Rick, Thanks -- This Split function (VBA only) is working a miricle.
Data() is obvioulsy an string array that is catching the Split results
Correct. The parentheses (brackets to some of you out there) are optional,
just like they are when assigning one array to the other. The reason is
Split returns an array. So, just like you can do this...
Dim Array1() As <Whatever>
Dim Array2() As <Whatever>
Array1(0) = <something>
Array1(1) = <something else>
etc.
Array2 = Array1
That last line could also be written Array2() = Array1() if you want. Now,
since Split returns an array (and it does all the behind the scenes
assignments to the various elements, you can do either of the following....
SomeDynamicArray = Split(<<arguments>>)
or
SomeDynamicArray() = Split(<<arguments>>)
It's your choice.
The only part that I can not get my head around is:
the replace portion..
Is replace llooking for "-" (a minus character) and replacing all with
a "+-" set of characters then the comma? and then the "+"
Can you verbalize what's going on here?
Maybe it will help if we separate the arguments into individual statements.
The relevant statements to your question are these...
Dim Data() As String
Data = Split(Replace(Mid$(Range("A1").Formula, 2), "-", "+-"), "+")
But the last line is just a function call within a function call. Split
apart, code-wise, it would look like this...
Dim DataLine As String
Dim ModifiedDataLine As String
Dim Data() As String
DataLine = Mid$(Range("A1").Formula, 2)
ModifiedDataLine = Replace(DataLine , "-", "+-")
Data = Split(ModifiedDataLine, "+")
What we are doing is assigning everything from the formula in A1, except for
the leading equal sign, to the String variable DataLine. We then replace all
occurrences of a minus sign with a plus sign followed by a minus sign
(remember, the contents of DataLine is a string of text, nothing more) and
assign it to the variable ModifiedDataLine. Finally, we take the modified
data line and Split it using the plus sign as the delimiter. For the
negative values... they became +-number and, when the delimiting plus sign
is removed in the Split process, the minus sign remains. In order to handle
the negative terms, which just consisted of a minus sign, we had to modify
it so there was a plus sign coupled with it to give the term something to be
Split on.
I hope that was all clear... it is usually easier to visualize these things
in one's head then it is to verbalize the thought process behind it (at
least for me it is).
Rick