Re indexing an array in vba

[NileshRam]

Hi,

I was wondering if I could please get some help on the following vba program.

For ease of the actual programme that I'm writing it's easier for me to use -2 as a base for my array.

Once I've populated the array I was wondering if it's possible to re-index the array to start from 1?

I thought it was something to do with the way i'm using redim preserve but any input would be greatly appreciated!

Thanks,

Nilesh
_______________________________________________________________________________________________
Sub test()


Dim wb As Workbook
Dim ws As Worksheet
Dim i As Integer
Dim j As Integer
Dim arr() As Variant


Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Sheet1")
ReDim arr(-2 To 2, -2 To 2)


For i = LBound(arr, 1) To UBound(arr, 1)
For j = LBound(arr, 2) To UBound(arr, 2)
arr(i, j) = "test"
Next j
Next i


ReDim Preserve arr(-2 To 2, -2 To 2) '<<<< Would like this to be arr(1 to 5, 1 to 5) with elements preserved


ws.Range(Cells(1, 1), Cells(5, 5)) = arr


End Sub

 

[Arjan77]

With ReDim you can only redim the last -2 to 2, so you can redim to arr(-2 to 2, 1 to 5). This is an annoying restriction in excel vba.
You could write to a new array using a loop, sorting the same effect.

 

[Arjan77]

For a manual redim you can use this code:

 For i = -2 To 2
 For j = -2 To 2
 arr2(i + 3, j + 3) = arr(i, j)
 Next
 Next

arr2 will hold the content of arr

 

[NileshRam]

Much appreciated Arjan, thanks

 

[mikerickson]

There is no quick way to do what you want.
Redim Preserve will only redemantion the UBound of the last dimension of an array.

The good news is that you can put an array into cells without its indexing starting at 1.

Range("A1:e5").Value = arr