Forum Discussion

louis1155's avatar
louis1155
Copper Contributor
Nov 18, 2023
Solved

Help with using a named column as argument in a VBA function

Dear community,

 

I have create a named column, "sales_column" with reference A:A (i.e. the first column of my workbook)

 

When I write "=sales_column" in a cell it correctly displays "40$", the sale value of the row corresponding to the cell i am writing in.

 

Now, I would like to use this named column in a VBA function which is just returning the input value, defined here bellow.

 

Function Echo(inputValue As Variant) As Variant
Echo = "Value: " & inputValue
End Function

 

However this would return a "#VALUE!" error when i try to run "=Echo(sales_column)" in a cell.

 

I have no clue how to fix this. Thanks for your help 🙂

 

 

 

Formulas and Functions
Macros and VBA
  • louis1155's avatar
    louis1155
    Dec 30, 2023

    Thanks guys!

     

    Here is my solution

     

    Function EchooRange(rangeName As Range) As Variant
       current_row = GetCurrentRow()

       EchooRange = "Value: " & rangeName.Cells(current_row, 1).Value
    End Function

3 Replies

  • louis1155's avatar
    louis1155
    Copper Contributor
    Dec 30, 2023

    Thanks guys!

     

    Here is my solution

     

    Function EchooRange(rangeName As Range) As Variant
       current_row = GetCurrentRow()

       EchooRange = "Value: " & rangeName.Cells(current_row, 1).Value
    End Function

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Nov 19, 2023

    louis1155 

    Function Echo(inputValur) As Variant
    Set r = Range(inputValue)
    For n = 1 To r.Rows.Count
    r.Cells(n, 1)="value:" & r.Cells(n,1)
    Next
    end function

    multi-cell range does not have a single value.
    So need to loop all values.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 19, 2023

    louis1155 

    Although the formula =sales_column refers to a multi-cell ranges, it returns the value of the cell in sales_column in the current row in Excel.

    VBA doesn't - cannot - work like that, however. In the line

     

    Echo = "Value: " & inputValue

     

    the interpreter sees a multi-cell range and throws an error because a multi-cell range does not have a single value.

    If you want to use Echo as a cell function, provide a single cell as argument.

Resources