Quick array question

[MrTeeny]

Can anyone give e the syntax of how I'd create a variant array without setting the dimensions beforehand or using a range on a sheet

At the moment I basically

[B]Dim unclosed_liabilities_array As Variant[/B]

then set the array by finding the last row and selecting an empty space on the sheet

[B]unclosed_liabilities_array = sht_runners.Range("BB14:BA" & LrowRunners).Value[/B]

I'm guessing I need to use ReDim somewhere along the line but just need the syntax for setting the empty arrays dimensions which I assume are simply number of element and number of columns/

Thanks

 

[Rick Rothstein]

I am still confused about the data structure though as to why [/COLOR]

[/COLOR][COLOR=#574123]ReDim unclosed_liabilities_array(1 To LrowRunners - 13,[/COLOR][COLOR=#574123]1 To LrowRunners - 13[/COLOR][COLOR=#574123]) As Variant [/COLOR][COLOR=#574123]

works with

[/COLOR]unclosed_liabilities_array(i, 1)[COLOR=#574123]

but

[/COLOR][COLOR=#574123]ReDim unclosed_liabilities_array(1 To LrowRunners - 13,[/COLOR][COLOR=#574123]1[/COLOR][COLOR=#574123]) As Variant [/COLOR][COLOR=#574123]

does not

Unless you specify Option Base 1 (the default is Option Base 0), VB arrays are zero-based (meaning the first element is 0) unless you provide a range for the element index values for a given dimension. So, that 1 for the second dimension actually declares 2 element, the first with index 0 and the second with index 1. To force it to one element, you would use 1 To 1 instead of using 1. As as side note, VB differs from other languages in that you can declare the indexes for each element independently to start and end at any value (within the range of a Long I believe), so this would create an array with 3 elements whose indexes are 100, 101 and 102...

ReDim MyArray(100 To 102)

 

[MrTeeny]

Unless you specify Option Base 1 (the default is Option Base 0), VB arrays are zero-based (meaning the first element is 0) unless you provide a range for the element index values for a given dimension. So, that 1 for the second dimension actually declares 2 element, the first with index 0 and the second with index 1. To force it to one element, you would use 1 To 1 instead of using 1. As as side note, VB differs from other languages in that you can declare the indexes for each element independently to start and end at any value (within the range of a Long I believe), so this would create an array with 3 elements whose indexes are 100, 101 and 102...

ReDim MyArray(100 To 102)

Thanks Rick, makes it clear now why the data wasn't being echoed back to my screen , or the reason I wasn't seeing it, when I was using

[COLOR=#574123][I]ReDim unclosed_liabilities_array(1 To LrowRunners - 13,[/I][/COLOR][COLOR=#574123][I]1[/I][/COLOR][COLOR=#574123][I]) As Variant [/I][/COLOR]

I was assuming the array only had one index and when dumping it to the screen in one column (A: ) it was dumping index 0 and not index 1 where I was actually adding the data , when I tried dumping the data to the sheet over two columns (A:B) the data was in the second column (i.e index 1)


At the moment I'm dumping the data back to the sheet using

sht_runners.Range("AL14:AL" & LrowRunners).Value = unclosed_liabilities_array

Is there a way I can dump the data back without specifying the end of the range, i.e just specify the starting cell and the array would fill as far as the elements within it. I'm sure I've seen you use transpose in some of your code dumping the data back to the screen.

 

[Rick Rothstein]

At the moment I'm dumping the data back to the sheet using

sht_runners.Range("AL14:AL" & LrowRunners).Value = unclosed_liabilities_array

Is there a way I can dump the data back without specifying the end of the range, i.e just specify the starting cell and the array would fill as far as the elements within it. I'm sure I've seen you use transpose in some of your code dumping the data back to the screen.

As far as I know, you have to size the range that the array will be dumped into, but you do not have to go back to original values that were used to create the array... the array already knows it size, so you can use that to set the receiving range's size. For the one dimensional array above..

sht_runners.Range("AL14").Resize(UBound(unclosed_liabilities_array)) = unclosed_liabilities_array

where I am assuming the lower bound of the array is 1, otherwise you would have to add 1 to the UBound to account for element index 0). There is an optional argument for the UBound property (defaults to 1) which is used to specify the dimension for it to find the upper bound to. Using simpler array names for space consideration (MyArray, which is assumed to be a two dimensional array) and assuming a lower bound of 1...

Range("AL14").Resize(UBound(MyArray, 1), UBound(MyArray, 2)) = MyArray

 

[Rick Rothstein]

At the moment I'm dumping the data back to the sheet using

sht_runners.Range("AL14:AL" & LrowRunners).Value = unclosed_liabilities_array

Is there a way I can dump the data back without specifying the end of the range, i.e just specify the starting cell and the array would fill as far as the elements within it. I'm sure I've seen you use transpose in some of your code dumping the data back to the screen.

In order for that code line to work, I am assuming that unclosed_liabilities_array is a two-dimensional array where the second dimension was declared as 1 To 1 or, if the array was populated from a range, that range was a column of values. The Transpose function is used to take an array that was populated from a row of values and make it into a column of values... that does not apply to the question you are asking here. For your question, as I understand it... as far as I know, you have to size the range that the array will be dumped into, but you do not have to go back to original values that were used to create the array to do so... the array already knows it size, so you can use that to set the receiving range's size. For the array above...

[table="width: 500"]
[tr]
	[td]sht_runners.Range("AL14").Resize(UBound(unclosed_liabilities_array)) = unclosed_liabilities_array[/td]
[/tr]
[/table]

where I am assuming the lower bound of the array's first element is 1, otherwise you would have had to add 1 to the UBound to account for element index 0. Note that I did not specify the columns argument for the Resize property because it defaults to 1 when omitted and, as I stated above, I am assuming your array is two-dimension with the second element dimensioned by using 1 To 1 which means the second element is width is 1, the same as the default for Resize when omitted. There is an optional argument for the UBound property (defaults to 1) which is used to specify the dimension for it to find the upper bound of. Using simpler array names for space consideration (MyArray, which is assumed to be a two dimensional array) and assuming a lower bound of 1...

[table="width: 500"]
[tr]
	[td]Range("AL14").Resize(UBound(MyArray, 1), UBound(MyArray, 2)) = MyArray[/td]
[/tr]
[/table]

 

[MrTeeny]

Thanks Rick, the reason for using something other than

[COLOR=#333333][I].Range("AL14:AL" & LrowRunners).Value[/I][/COLOR]

was mainly so I wouldn't have to add or or subtract to LrowRunners if I changed the starting cell from AL14 to say AL30.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]sht_runners.Range("AL14").Resize(UBound(unclosed_liabilities_array)) = unclosed_liabilities_array[/TD]
[/TR]
</tbody>[/TABLE]

works exactly as I wanted, thanks