Re: fixed or dynamic array

"Peter T" <peter_t@discussions> wrote in message news:%23EwD3ZkkIHA.6092@xxxxxxxxxxxxxxxxxxxxxxx

Only curiosty but are you able to explain why assigning
Split to a pre dimensioned variant array fails, in contrast
to a similarly dimensioned string array.
Redim arrStr(0 to e) As String
ReDim v(0 to e) as Variant
' where e is the know no. of elements in advance
arrStr = Split(s, deLim) ' works
v = Split(s, deLim) ' type mismatch

The first one works [arrStr = Split etc] only because arrStr is a dynamic array (an array for which it is possible to change the number of elements at runtime). It is a dynamic array because you use Redim (as opposed to Dim) to create it. The fact that you gave it elements (0 to e) is irrelevant. For example, if you used Redim arrStr (0 to 99) and the Split function found 6 substrings then arrStr would have only 6 elements after the Split, arrStr(0) to arrStr(5). The fact that you initially gave it 100 elements (0 to 99) is totally irrelevant. If you had instead used Dim arrStr (0 to e) then the Split function would have failed, because arrStr would be a static array and the Split function needs a dynamic array.

The second one fails [v = Split(s, deLim)] because v is not simply a Variant. It is an *array of Variants*. The Split function needs either a dynamic array (as in the first case) into which it will place its output or a normal Variant into which it will place a string array containing its output. For that code to work you would need to have declared v as a normal Variant, rather than an array of them, as in Dim v as Variant [rather than Dim v (0 to e) as Variant]. In simple terms, you can get the output of Split to go into a dynamic String array or into a standard Variant or into one element of array of Variants. Have a look at this:

Private Sub Command1_Click()
ReDim arrstr(0 To 12) As String ' the 0 to 12
Dim j As Variant ' left in here just to show
Dim v(0 To 12) As Variant ' what happens
Dim s As String, deLim As String
deLim = ":"
s = "12 : 34 : 56 : 78"
'
arrstr = Split(s, deLim)
Print LBound(arrstr), UBound(arrstr)
'
j = Split(s, deLim)
Print LBound(j), UBound(j)
'
v(12) = Split(s, deLim)
Print LBound(v(12)), UBound(v(12))
End Sub

Mike




.

Relevant Pages

  • Re: Max/Min Functions
    ... You said the Array function won't work here. ... Dim myArray() As Variant ... > Dim vMax As Variant ...
    (microsoft.public.word.vba.general)
  • Re: Max/Min Functions
    ... You can stuff a value of any other data type into a Variant ... > Dim myArrayAs Variant ... > a whole array into a single Variant variable, ... > Dim vMax As Variant ...
    (microsoft.public.word.vba.general)
  • Re: Same Contents, Regardless of Order
    ... Dim Arr1As Variant ... ' Second Array ... Dim blnFound As Boolean ...
    (microsoft.public.excel.programming)
  • Re: transferring array to range
    ... Dim arrValues(NumRows, NumColumns) As Variant ... declared an array of variants instead of a variant that contains an array. ...
    (microsoft.public.excel.programming)
  • Re: Re:workdays
    ... Optional Holidays As Variant = Nothing, ... Dim arrayH As Variant ... ' or not an array or cell range with at least one numeric value between ... ReDim arrayH) As Variant ...
    (microsoft.public.excel)