CODEWITHSUNDEEP
excelvbaworksheet-function
Victor Lalonde
Victor Lalonde

Reputation: 27

Excel VBA, error 438 "object doesn't support this property or method on WorksheetFunction

So I am getting the error in the title on the AT_cellnum line where I use the address function. 

Sub AllTransURL()

Dim AT_rownum As Integer, pathrange As Range, AT_cellnum As String, sheetname As String

sheetname = "All Transmissions"
Set pathrange = ThisWorkbook.Worksheets("All Transmissions").Range("H1:H53")

AT_rownum = Application.WorksheetFunction.Match("infra/remwip/Public/0_00_Rapports", pathrange, 0)

AT_cellnum = Application.WorksheetFunction.Address(AT_rownum, 1, 1, 1, sheetname)

End Sub

Thanks you

Upvotes: 1

Views: 752

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

There's no need for an Address function in the object model, hence it's not on the WorksheetFunction interface.

Rule of thumb, if you're maknig an early-bound member call and the member you want to invoke isn't showing up in the autocompletion name list, it's not a member of the object you're working with; if the code compiles anyway, there's a good chance it will throw error 438 at run-time.

There's no need for an Address function, because when you work with the object model, you have a Range object, and Range has an Address property that's readily available: a WorksheetFunction for it would be entirely redundant.

You know what worksheet the range is from (sheetname); you know what row you want (AT_rownum), and you know what column you want (1) - thus:

Dim AT_range As Range
Set AT_range = ThisWorkbook.Worksheets(sheetName).Cells(AT_rownum, 1)

AT_cellnum = AT_range.Address

But as Scott hinted at, a cell's address is rarely something you need in VBA: if you have a Range object, you likely already have everything you need.

Upvotes: 2

Related Questions