topic Re: Quick Array Question in VBA Forum

Quick Array Question

Anonymous — Tue, 05 Dec 2006 15:29:18 GMT

I have this function which splits a string and converts it to an array. It works fine with numbers, but when the entry becomes text it doesn't seem to work. What is wrong with my code?

IF varpoints = "Double,Array"

Function stringToDblArray(VarPoints)
Dim StrAr() As Variant
VarPoints = VBA.Split(VarPoints, ",")
If IsNumeric(VarPoints) Then
ReDim ptaRr(UBound(VarPoints)) As Double

Dim i As Integer
For i = 0 To UBound(VarPoints)
ptaRr(i) = CDbl(VarPoints(i))
Next i
Else
ReDim StrAr(UBound(VarPoints)) As Variant
For i = 0 To UBound(VarPoints)
StrAr(i) = CStr(VarPoints(i))
Next i
End If
stringToDblArray = ptaRr

End Function

Re: Quick Array Question

Ed__Jobe — Tue, 05 Dec 2006 16:26:55 GMT

Because of IsNumeric, your logic will never enter the If structure when the argument is text.

Re: Quick Array Question

Anonymous — Tue, 05 Dec 2006 16:27:20 GMT

Change this line
ReDim StrAr(UBound(VarPoints)) As Variant
on
ReDim StrAr(UBound(VarPoints)) As String

Will this helps?

~'J'~

Re: Quick Array Question

Anonymous — Tue, 05 Dec 2006 17:08:24 GMT

I've tried changing from Variant to String and it still gives me an error subscript out of range.

Could i possibly check each index of varpoints before it redim's to see if it is numeric since isnumeric will prevent it from ever entering the if statement?

Re: Quick Array Question

Anonymous — Tue, 05 Dec 2006 19:05:24 GMT

First, you pass wrong variable into Split function
See help. This would be a string expression not a variant
Try this one, seems to be working for me

~'J'~

'~~~~~~~~~~~~~~~~~~~~~~'
Option Explicit

Function stringToDblArray(expStr As String)
Dim i As Integer
Dim VarPoints
VarPoints = VBA.Split(expStr, ",")

Dim check As Boolean
check = False
For i = 0 To UBound(VarPoints)
If IsNumeric(VarPoints(i)) Then
check = True
End If
Next

If check = True Then
ReDim fstAr(UBound(VarPoints)) As Double

For i = 0 To UBound(VarPoints)
fstAr(i) = CDbl(VarPoints(i))
Next i
stringToDblArray = fstAr
Else
ReDim SndAr(UBound(VarPoints)) As String
For i = 0 To UBound(VarPoints)
SndAr(i) = CStr(VarPoints(i))
Next i
stringToDblArray = SndAr
End If

End Function
Sub test()
Dim a As String, b As String, c, d

a = "1,2,3,4,5"

b = "a,b,c,d,e"

c = stringToDblArray(a)

d = stringToDblArray(b)

If VarType(c) = vbArray + vbDouble Then
MsgBox "First Returned Array Is An Array of Doubles"
End If
If VarType(d) = vbArray + vbString Then
MsgBox "Second Returned Array Is An Array of Strings"
End If
End Sub