Assign an Array a data type

[Guest]

Thanks for taking the time to read my question.

I have a string that I am parsing, and sticking into an array. The string
has text and numbers. I want only the numbers.

So I've tried setting my array to Double, but it is still allowing text.
What am I doing wrong?

Thanks for your help,

Brad

CODE:
====================================================

TheString = " Test Test 9 3 2"

Option Compare Database
Dim NamesArray(10) As String
Dim InventoryArray(10), SowsBredWeekArray(10), SowsFarrowWeekArray(10),
PigsWeanedWeekArray(10), AvgWeanAgeArray(10), BornAliveArray(10) As Double
----------------------------------------------------------------------------------------------

Sub GetSowsBredWeek(TheString As String)
Dim Result, X, Y, Z As Integer

On Error GoTo GetSowsBredWeek_Err

X = 1
Y = 0

Do Until X = Len(TheString)
Result = InStr(X, TheString, " ", vbBinaryCompare)
If Result > X + 1 Then
'The Next line is where it all happens for the first time.
'Here the Array should only accept numbers, but it will except "Test"
'"Test" should cause an error 13, Type Mismatch, go through error
handling
'skip the line and continue.
SowsBredWeekArray(Y) = Mid(TheString, X, Result - X)
Debug.Print SowsBredWeekArray(Y)
Y = Y + 1
X = Result
Else
If Result < X Then
SowsBredWeekArray(Y) = Right(TheString, Len(TheString) - (X - 1))
Debug.Print SowsBredWeekArray(Y)
X = Len(TheString)
Else
X = X + 1
End If
End If
Loop


GetSowsBredWeek_Exit:
Exit Sub

GetSowsBredWeek_Err:
If Err.Number = 13 Then
Resume Next
Else
MsgBox Err.Number
Resume GetSowsBredWeek_Exit
End If
End Sub

 

[Douglas J. Steele]

In actual fact, your declaration

Dim InventoryArray(10), SowsBredWeekArray(10), SowsFarrowWeekArray(10),
PigsWeanedWeekArray(10), AvgWeanAgeArray(10), BornAliveArray(10) As Double

is only making BornAliveArray an array of Doubles: the other 5 arrays being
declared are all arrays of Variants, which can, of course, accept both text
and numbers.

VBA doesn't let you "short circuit" your declarations like that. To have all
six arrays be Doubles, you must use:

Dim InventoryArray(10) As Double, SowsBredWeekArray(10) As Double,
SowsFarrowWeekArray(10) As Double, PigsWeanedWeekArray(10) As Double,
AvgWeanAgeArray(10) As Double, BornAliveArray(10) As Double

You might consider using the IsNumeric function:

If IsNumeric(Mid(TheString, X, Result - X)) Then
SowsBredWeekArray(Y) = Mid(TheString, X, Result - X)
End If

However, be aware that the IsNumeric function sometimes accepts things that
you might not consider to be numeric. While IsNumeric("Test") will
definitely return False (as desired), certainly strings like
IsNumeric("123D354") or IsNumeric("235E23") will return True, as VBA sees
the D and E in those cases as being an indication to use powers of 10 (the
first string would be viewed as 123 x 10^354, while the second is seen as
235 x 10^23)

 

[Guest]

OK, I just tried this and it worked, but I have NO idea way.

Option Compare Database
Dim NamesArray(10) As String
Dim InventoryArray(10) As Double
Dim SowsBredWeekArray(10) As Double
Dim SowsFarrowWeekArray(10) As Double
Dim PigsWeanedWeekArray(10) As Double
Dim AvgWeanAgeArray(10) As Double
Dim BornAliveArray(10) As Doubl
-----------------------------------------------------------------------------------------------

Instead of

Option Compare Database
Dim NamesArray(10) As String
Dim InventoryArray(10), SowsBredWeekArray(10), SowsFarrowWeekArray(10),
PigsWeanedWeekArray(10), AvgWeanAgeArray(10), BornAliveArray(10) As Doubl

 

[Douglas J. Steele]

Did you not read the explanation I gave you this morning?