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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 

Forum statistics

Threads
1,211,744
Messages
6,103,677
Members
447,876
Latest member
rodqntr

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
Back
Top