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 ...
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

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