Re: Handling ubound on an uninitialised array

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Rob van Gelder (newsgroups_at_nojunkmail-vangelder.co.nz)
Date: 01/24/05 

Date: Mon, 24 Jan 2005 21:19:49 +1300

I gave a bad example but I think I'll just let it go.

I'd forgotten about the array dump to range - that's a nice feature. 

-- 
Rob van Gelder - http://www.vangelder.co.nz/excel
"keepITcool" <xrrcvgpbby@puryyb.ay> wrote in message 
news:xn0dxmo6j9y2u7i000keepitcoolnl@msnews.microsoft.com...
> Rob,
>
> if you want to dump a simple array afaik excel doesnt care about upper
> and lower bounds.
>
>
> if you do want to change the upper and lower boundaries
> (while keeping the same number of elements AND preserving the data..
> the variable must a declared as a variant not a variant array,
> and you could do a simple redim preserve.
>
> I'll try to illustrate:
>
>
> Sub Redimming()
> Dim i, arr(), var, cpy
>
> ReDim var(10 To 19)
> ReDim arr(10 To 19)
> For i = LBound(arr) To UBound(arr)
> var(i) = i
> arr(i) = i
> Next
>
> [a1:a5].Clear
> 'dump the 10-based VARIANT
> [a1].Resize(1, UBound(var) - LBound(var) + 1) = var
> 'dump the 10-based VARIANT ARRAY
> [a2].Resize(1, UBound(arr) - LBound(arr) + 1) = arr
>
> 'NOTE REDIM PRESERVE BOTH BOUNDS WORKS FOR A 'PLAIN' VARIANT ONLY
> 'redim, dump the 1-based
> ReDim Preserve var(1 To UBound(var) - LBound(var) + 1)
> [a3].Resize(1, UBound(var) - LBound(var) + 1) = var
> 'redim, dump the 0-based
> ReDim Preserve var(0 To UBound(var) - LBound(var))
> [a4].Resize(1, UBound(var) - LBound(var) + 1) = var
>
> 'Copying ARR to a variant... and THEN resizing works
> cpy = arr
> ReDim Preserve cpy(0 To UBound(cpy) - LBound(cpy))
> [a5].Resize(1, UBound(cpy) - LBound(cpy) + 1) = cpy
>
> 'resizing ARR will not work
> ReDim Preserve arr(0 To UBound(cpy) - LBound(cpy))
>
> End Sub
>
>
>
>
>
>
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Rob van Gelder wrote :
>
>> It's tempting to argue this further, but I don't think I could
>> provide a strong case to using zero-based indexing.
>> I've only ever used zero-based - I've read no studies on bugcount
>> for/against zero-based. I'm just not the guy to push the point :)
>>
>> The only thing I could think of is reusing the index variable.. eg.
>> Copying your x array to a range might require two indexes where
>> zero-based requires just one.

Relevant Pages

  • Dim MyArray vs. Dim MyArray()
    ... MyArray is not a Variant() array but is a Variant variable containing an ... array, just as though the snippet were ... to 1-based or vice versa with the ReDim Preserve construct rather than ... The argument a couple of years ago that Dim MyArray1() led to a Variant ...
    (microsoft.public.excel.programming)
  • Re: Handling ubound on an uninitialised array
    ... if you want to dump a simple array afaik excel doesnt care about upper ... the variable must a declared as a variant not a variant array, ... 'dump the 10-based VARIANT ... 'NOTE REDIM PRESERVE BOTH BOUNDS WORKS FOR A 'PLAIN' VARIANT ONLY ...
    (microsoft.public.excel.programming)
  • Re: Which is better?
    ... Since your code will be doing the adding and modifying, ... enforce any conditions you wanted without declaring the array as a specific ... Not sure what you are suggesting with redim preserve, ... contained in a Variant, the type of the elements can be changed using an As ...
    (microsoft.public.excel.programming)
  • Re: Function to resize an array
    ... > Integer to Dim i as Integer, j as integer, the question ... Redim alone will wipe out and reinitialize all your array elements; ... ReDim Preserve retains the elements of the array. ... Function CleanArr(OrArr As Variant) As Variant ...
    (microsoft.public.excel.programming)
  • Re: User Define Array Data Type - Subscript out of range
    ... ReDim Preserve xArray.IntInputOrder ... ' Load Array ... strDataTypeAs String ... Dim rs As ADODB.Recordset ...
    (microsoft.public.access.modulesdaovba)