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

xavierhussenet

New Member
Joined
Mar 25, 2011
Messages
2
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,856
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top