Split Function

Hi there

One of the lines in my VBA code reads as follows

Dim oRange As Range
Dim oFindInRange As Range
Dim strSplitAddress As String
Dim blnNullRange As Boolean

.//lines of code//
.//lines of code//
.//lines of code//
.//lines of code//

strSplitAddress = Split(oFindInRange.Address, "$")(1)

what does the Split function do especially with does $ and (1) sign means in this scenario.
I have read up on split functions in excel help but i can't find specific reference to $ or (1) anywhere. Can you please advice?

The $ is used as the character by which to split the string ... eg assume for sake of demo that oFindInRange is A1 ... the .Address of which would be "$A$1" (based on code)

The "$A$1" is subsequently Split based on $ and a three value zero based array is created... namely:

0 - string before first $
1 - string after first $ and before second $
2 - string after second $

the above values can be accessed at the same time as the Split call (as per your example), eg:

Split(...)(0) is blank
Split(...)(1) is A
Split(...)(2) is 1

in much the same way that we can access cells etc

Debug.Print Range("A1:D10")(5)

or by passing the Split to a Variant and processing from that, eg:

Dim vData As Variant
vData = Split(oFindInRange.Address,"$")
Debug.Print vData(0) & ":" & vData(1) & ":" & vData(2)

If the intention is to process only one element then the first approach is logical.
If however the requirement is such that you will want to process multiple/all resulting values of the Split then storing the output in a Variant Array is the more logical approach given the Split is executed only once.

...it would seem that based on the code you provided you are extracting the column letter from a Range address.

Hi DonkeyOte

Thanks for the reply

Yes the code that i have is extracting information from a column header, the full function is below as follow

Private Function RangeFinder(ByVal strStrFindWord As String) As String
' search the header column only for the findword
    Dim oRange As Range
    Dim oFindInRange As Range
    Dim strSplitAddress As String
    Dim blnNullRange As Boolean

    Set oRange = Range("A1", strMaxRange + "1") ' the search range, the currentrow will always be 1 here since we only check the header
    On Error Resume Next
    Set oFindInRange = oRange.Find(strStrFindWord, LookIn:=xlValues, MatchCase:=False) ' in case someone modifies the header by case
    
    If oFindInRange Is Nothing Then blnNullRange = True
     
    If Not (blnNullRange) Then
        strSplitAddress = Split(oFindInRange.Address, "$")(1)
        RangeFinder = strSplitAddress
    Else
        MsgBox ("Header value has been removed / renamed. i cant find " + strStrFindWord + " in the header")
    End If
End Function

Sorry, i had a quick glance at the links you recommended for reading and none of them have any reference to my second question i.e. why (1) is used in the split function above. I may be wrong here so please correct me. Any chance you can explain the following questions please?

1. Set oRange = Range("A1", strMaxRange + "1") - what does this mean?

1. If oFindInRange Is Nothing Then blnNullRange = True - How does this statement gets compiled i.e. whats the logic behind this?

1. RangeFinder = strSplitAddress - how does strSplitAddress gets passed to RangeFinder when actually we are passing ByVal strStrFindWord As String?

Originally Posted by captedgar

Sorry, i had a quick glance at the links you recommended for reading and none of them have any reference to my second question why (1) is used in the split function above

The links are my general signature - ie general footnote to my posts - they are not meant as specific references for your question...

As to the question regards use of (1) - I thought I had covered that in the prior post - seemingly not (though I did edit)...

The Split of "$A$1" by $ results in three strings namely: [blank], A, 1 ... the "$" character acts as nothing more than a delimiter between values.
Those three values reside in a 0 based array ... 0 base meaning that the first value occupies position 0 in the Array, 2nd value position 1 and 3rd (& final) value in position 2.

Thus:

Split("$A$1","$")(1)

the use of (1) retrieves the 2nd value from the resulting array of values, in this case "A" - the column letter.


On to your other questions - a lot of these I can't answer because we have only a partial picture:

Originally Posted by captedgar

Set oRange = Range("A1", strMaxRange + "1") - what does this mean?

this is creating a Range for the headers where starting point is A1 and the end point is also on row 1 but defined by whatever the letter assigned to strMaxRange variable is - we don't know this based on your code (ie this is set elsewhere).

Originally Posted by captedgar

If oFindInRange Is Nothing Then blnNullRange = True - How does this statement gets compiled i.e. whats the logic behind this?

this simply states that if the search string (as passed to the Function) can not be found in the header range then set the Boolean variable to True to flag this fact...
this Boolean flag will be used latterly to determine appropriate action to take ie:

Found: split the found address and retrive the column letter.

Not Found: display MsgBox dialog notifying user as such

Originally Posted by captedgar

RangeFinder = strSplitAddress - how does strSplitAddress gets passed to RangeFinder when actually we are passing ByVal strStrFindWord As String?

The string output as retrieved from the split where the Address was found is assigned as the Functions output value - RangeFinder itself is declared as a String.

Private Function RangeFinder(ByVal strStrFindWord As String) As String

If you have other questions not related to the original Split question please ask them in a new (dedicated) thread...