Redim preserve multi dimensional array

[sn0rg]

Hi all

I'm trying to build a 2 dimensional array from values in a spreadsheet.

I am used to using the

redmin preserve array(x)

command to add values, but when I try this for a multi-dimentsional array I get an "out of range" error. Can anyone help?

			n = 0
			intColumn = 1
			dim m
			Dim arrCountries()


			Do while not objExcel.Cells(intRow, intColumn) = ""
				Redim preserve arrCountries(n,1)
					arrCountries(n,0) = objExcel.Cells(intRow, intColumn)
					arrCountries(n,1) = objExcel.Cells(intRow, intColumn + 1)

					intRow = intRow + 1
					n = n + 1
			Loop

 

[PHV]

If you use the Preserve keyword, you can resize only the last array dimension, and you can't change the number of dimensions at all.

Do while not objExcel.Cells(intRow, intColumn) = ""
  ReDim Preserve arrCountries(1,n)
  arrCountries(0,n) = objExcel.Cells(intRow, intColumn)
  arrCountries(1,n) = objExcel.Cells(intRow, intColumn + 1)
  intRow = intRow + 1
  n = n + 1
Loop

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sn0rg]

Thanks for the answer. In the end, I decided to loop through the rows of the spreadsheet, and construct the array size, then go back to the beginning, Dim the array, then Redim it with the variable, and add the items into the array for a For statement.

 

[markdmac]

An excellent solution to the limitation sn0rg. Thanks for sharing what you came up with. I am sure it will help others in the future.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.