Problems with VBA Arrays using Split

[bbook]

I created some code to grab serial port data and import it into excel. The data that comes in is in the following format;

D 4.7618 uS/cm 98.627 %Rej 346.73 uS/cm 70.573 DegF 0130

This string has a CR on the end. I am trying to split this into an array and then extract the data that I want into cells in excel. The code that I am using is this;

Private Sub XMCommCRC1_OnComm()

Static sInput As String
Dim sTerminator As String
Dim Buffer As Variant
Dim dLim() As String

' Branch according to the CommEvent property.
Select Case XMCommCRC1.CommEvent
Case XMCOMM_EV_RECEIVE
Buffer = XMCommCRC1.InputData
sInput = sInput & Buffer
' Puts the data from the com port into this variable
If Worksheets("Settings").Range("Terminator") = "CR/LF" Then
sTerminator = vbCrLf
' Determines the terminator from cell on settings sheet
Else
sTerminator = vbCr
End If
If Right$(sInput, Len(sTerminator)) = sTerminator Then
' Parses string for terminator and puts the string
' in the active cell and indexes one row
dLim = Split(sInput)

ActiveCell.Value = dLim(1)
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = dLim(3)
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = dLim(5)
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = dLim(7)
ActiveCell.Offset(1, -3).Select

Erase dLim

' Clears the data from the variable
sInput = ""
End If
End Select

End Sub

The problem is that only array elements 0-2 get populated, all the elements after 2 are empty. I'm not a VB programmer and am amazed that I got this far with my code, so please keep that in mind with any help that is offered.

 

[Norie]

Do you not need to specify a delimiter for Split?

 

[Andrew Poulsom]

This worked for me:

Sub test()
    Const sInput As String = "D 4.7618 uS/cm 98.627 %Rej 346.73 uS/cm 70.573 DegF 0130"
    Dim dLim() As String
    dLim = Split(sInput)
    ActiveCell.Value = dLim(1)
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = dLim(3)
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = dLim(5)
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = dLim(7)
End Sub

The UBound of dLim was 9.

What do you get with:

MsgBox dLim

immediately before calling Split?

 

[bbook]

After a closer look at my data string:

D 4.7618 uS/cm 98.627 %Rej 346.73 uS/cm 70.573 DegF 0130

What I receive on the comm port has 2 spaces after uS/cm, after 98.627, after %Rej, after 346.73, after the second uS/cm, and after 70.573.

It looks like the Split command only splits up to the second space after the first uS/cm and stops. So I only store "D", "4.7618" and "uS/cm" into the dLim array.

Is there another way to get this data into the array?

 

[Norie]

I've just tried this code.

Dim varArray As Variant
Dim I As Integer

varArray = Split("D 4.7618 uS/cm  98.627  %Rej  346.73  
uS/cm  70.573  DegF 0130", " ") ' one space as delimiter

For I = LBound(varArray) To UBound(varArray)
    Debug.Print varArray(I)
Next I

It returned a 16 element array including spaces and the following output.

D
4.7618
uS/cm

98.627

%Rej

346.73

uS/cm

70.573

DegF
0130

Then I tried this

Dim varArray As Variant
Dim I As Integer

varArray = Split("D 4.7618 uS/cm  98.627  %Rej  346.73  
uS/cm  70.573  DegF 0130", "  ") ' two spaces as delimiter

For I = LBound(varArray) To UBound(varArray)
    Debug.Print varArray(I)
Next I

And it returned a 7 element array and the following output.

D 4.7618 uS/cm
98.627
%Rej
346.73
uS/cm
70.573
DegF 0130

 

[bbook]

I added the following line that replaced the double spaces with a single space and now it seems to work like it was supposed to.

sInput = Replace(sInput, " ", " ")

Also, this site removes the extra spaces when I post the message. That is why my first post of the string worked for you because the extra spaces were removed.

Thanks for the help!!