pass an address to a function in vba function-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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.