SOLVED

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

Copper Contributor

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 :)

 

 

 

3 Replies

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

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

best response confirmed by louis1155 (Copper Contributor)
Solution

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

1 best response

Accepted Solutions
best response confirmed by louis1155 (Copper Contributor)
Solution

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

View solution in original post