How to get VBA Function to store cell address values instead of values at these cell address

user_15339649 0
Viewed 78

I know this is a very unusual question, it will probably not make sense to you why I want to do this but...

This happens if the value in cell A1 is 5:

Myfunction(A1): outputs 5

In vba module:

Function Test(array As String)
   Test=array
End function

Examples of what I want:

Myfunction(A1): outputs string "A1"

Or

Myfunction(A1:B3): outputs string "A1:B3"

Or

Myfunction(A1:B3 & B5:B9): outputs string "A1:B3 & B5:B9"

How do I get the vba function to do this. Basically I want the function to store the cell address as a string and not the values located at the cell address locations. I know you can use "A1:A4" when you enter the values into the function or something along those lines but that's not what I'm looking for. Also this is for a function not a sub.

user-defined-functions vba excel
2 Answers

ParamArray

Understanding parameter arrays

  • In Excel, to select a multiarea range, select the first range, then hold down CTRL while selecting the next ranges.
  • In Excel use e.g. =RangeAddress(A1:A5,C3:C7,Z11:Z75).

The Code

Option Explicit

Function RangeAddress( _
    ParamArray rg() As Variant) _
As String
    Dim arg As Variant
    If UBound(rg) = 0 Then
        RangeAddress = rg(0).Address(0, 0)
    Else
        Dim n As Long
        For Each arg In rg
            RangeAddress = RangeAddress & arg.Address(0, 0) & " & "
        Next arg
        RangeAddress = Left(RangeAddress, Len(RangeAddress) - 3)
    End If
End Function
user_98140690

Not sure if this is what you're looking for, but you can try something like this:

Function MyFunction(rng as range) as string
   MyFunction = cstr(rng.address)
end function

Go to your sheet, select any cell or cells.

In your immediate window, type: ?MyFunction(selection)

user_153295440
Related