Text: Mr. Excel's VBA and MACROS for Microsoft EXCEL 2007

[xavierhussenet]

Hello all!

In Ch 4, User-Defined Functions, I can't find the definitions or help files explaining the red highlighted code in the following short function:

Function ColName(Rng As Range) As String
'Page 98
' Rng —The cell for which you want the column letter.
ColName = Left(Rng.Range("A1").Address(True, False), _
InStr(1, Rng.Range("A1").Address(True, False), "$", 1) - 1)
End Function

I tried placing the cursor in each and doing F1, but that does not help. Basically I need to know what .Address(True, False) actually does, and where other dot functions and arguments with a cell inside a range are defined and explained in the Excel VBA help files. Also, is "$" a marker for something like integers, or ar we actually looking for the first $ char?

I know this has to be simplistic, but I need to find out what is actually going on here and comments are sparse in the text.

Thanks,

Xavier

 

[tweedle]

This Rng.Range().Address(True, False)
is morphing the address of the provided range to this format: A$1
The True says force the Row to Absolute (preluding it with a $)
The False indicates that the Column should be Relative (No $)

This
InStr(1, Rng.Range("A1").Address(True, False), "$", 1) - 1
Is Repeating the formula to determine what position the 1 known $ is, and use 1 fewer characters for the left function. Ulimately returning the "A".

FYI:
ALT F11 followed by F2 willl open the object browser which will tell you virtually everything you need to know about use and syntax as well as better object-specific help-linking, especially when you may not know exactly what you're looking for, which is often the case.

 

[xavierhussenet]

Thanks, that was really very helpful! I also appreciate the tip on finding info!