Split Function

Hi there

One of the lines in my VBA code reads as follows

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

in much the same way that we can access cells etc

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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