CODEWITHSUNDEEP
excelvbafunctionarguments
yossi even-zur
yossi even-zur

Reputation: 11

pass an address to a function in vba function

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

Upvotes: 1

Views: 921

Answers (2)

T.M.
T.M.

Reputation: 9938

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.

Upvotes: 0

braX
braX

Reputation: 11735

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

Upvotes: 1

Related Questions