Address function Sub or Function not defined

[fathafluff]

I put this formula in a cell: =Address(5, 6, 2) and the cell displays F$5 (row 5, 6th col, absolute row, relative column) as I expect. But when I create the following Sub:

Please Login or Register  to view this content.

and then double click any cell, I get this message displayed: "Compile Error: Sub or Function not defined" with the word Address highlighted.
In searching the web, I found reference to needing to add the Solver reference under Tools/References. When I open Tools/References, I see a list with "Visual Basic For Applications", "Microsoft Excel 16.0 Object Library", "OLE Automation", and "Microsoft Office 16.0 Object Library" checked, and 200 or more items unchecked. But I cannot find "Solver."
All the references I find about using Address show it as ADDRESS. When I enter it in all caps or all lower case, it changes it to initial cap only, so it seems to recognize the word. Am I doing something wrong, or am I missing a library?

[Fluff13]

You cannot use that worksheet function directly in VBA, try

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[fathafluff]

Thanks. I never would have figured it out. I see that the Address property (1,0) is RowAbsolute true and ColumnAbsolute false. The second version is more intuitive to me, but I've never heard of using square brackets.

[Fluff13]

You're welcome & thanks for the feedback.

The square brackets are a shorthand version of the Evaluate function

[Marc L]

As a VBA beginner starter :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    MsgBox Target.Address(True, False)
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[fathafluff]

Thanks for all your help. I'm still developing this. I've been programming for 20 years, and took a class in VB a while ago, but I have trouble wrapping my head around referencing objects/properties. I have the following module:

Please Login or Register  to view this content.

When I double click on W9 I get a Message with "W$9" for the first and second messages. The third gives me a Type mismatch error. When I "mouse over" the words Target.Row and Target.Column I see "9" and "23" respectively. The first and third lines seem to be the same to me. What "type" is expected and what "type" was found, causing the mismatch?

I was going to mark this SOLVED, but (1) I had this additional question, and (2), I can't find the place in the forum to mark it SOLVED. Thanks.

[Fluff13]

When using the [] notation, you cannot use variables, ie Target.row.

To mark a thread as solved click on the "Thread tools" button at the top of the thread.

[fathafluff]

Thanks, and I hope I don't need to bug you again.