pass an address to a function in vba function

user_19067853 0
Viewed 90

see the basic function structure

function test (first as range)

whatever I do inside

end function

calling the function and first is the address of the first cell, for example, B2, like this:

first=ActiveCell.address (0,0)
test (first)

calling the function results with a run time error 424 "object required" no idea what I'm doing wrong

arguments vba excel function
2 Answers

You just want the first cell's address? There are lots of ways to do this. Here is one.

Sub test(first As Range)
  Dim r As Range
  
  For Each r In first
    Debug.Print r.Address
    Exit Sub
  Next
End Sub
OR as a function that returns the address: 

Function GetFirstAddress(first As Range) As String
  Dim r As Range
  
  For Each r In first
    GetFirstAddress = r.Address
    Exit Function
  Next
End Function
user_84229530

Another approach allowing optional external references might be:

Function GetFirstAddress(rng As Range, Optional IsQualified As Boolean = True) As String
    GetFirstAddress = rng.Parent.cells(rng.Row, rng.Column).Address(external:=IsQualified)
End Function

where rng.Parent qualifies the needed worksheet reference.

user_64602970
Related