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 need an empty array but the dimensions aren't set , the number of elements will be set using the value in LrowRunners, I was looking for the syntax to set an array with LrowRunners elements in it I had tried

Dim unclosed_liabilities_array(1 To LrowRunners) As Variant

but it gives constant errors

As long as LrowRunners has been given a value beforehand, just ReDim it...

[table="width: 500"]
[tr]
	[td]ReDim unclosed_liabilities_array(1 To LrowRunners) As Variant[/td]
[/tr]
[/table]

While it is not necessary, the convention is to declare unclosed_liabilities_array as a Variant (with no parentheses) at the beginning of the procedure and then later on ReDim it to size without declaring its data type. For a two-dimensional array (like you asked for originally)...

[table="width: 500"]
[tr]
	[td]Dim LrowRunners As Long, LcolRunners As Long, unclosed_liabilities_array As Variant
LrowRunners = 10
LrowRunners = 20
ReDim unclosed_liabilities_array(LrowRunners, LcolRunners)[/td]
[/tr]
[/table]

 

[JackDanIce]

That's because you can't assign a variable like LrowRunners as a dimension, you must use whole positive numbers.*
(* with respect to @rickrothstein's post I didn't consider using ReDim afterwards to enable use of LrowRunners as dimension)

Which then isn't clear, why have a variable to determine a dimension, to pre dimension an array vs

arr = .Cells(1,1)Resize(LrowRunners,10).Value

Does your query become, why can't you do:

Dim unclosed_liabilities_array(1 to LrowRunners, 1 to 10) as variant

Instead of:

Dim unclosed_liabilities_array() as variant
unclosed_Liabilities = Range("A1:C" & LrowRunners).Value

That's only an extra line of code for something that doesn't need to be pre-dimmed with a variable, which as you've discovered cant be set as it's not a constant.

 

[Norie]

I'm totally confused.

You want an empty array with the dimensions not set but you want to set the no of elements (one of the dimensions) of the array.

 

[JackDanIce]

Don't think you're alone Norie!

There may be confusion with what you're trying to do and use of dimensionalised arrays

@MrTeeny perhaps you can explain what you are trying to achieve or give a bigger picture explanation? It seems like this is something more complicated than needs to be, based on your initial post and the various responses so far...

 

[MrTeeny]

I come from a very basic php background where you don't need to continually set dimensions of arrays so that's why learning arrays with VBA confuses me I guess.

It's basically a betting program whereby I want to find my overall liabilities per runner where the individual bets are listed on a separate sheet so they get looped thru and matched against a separate array of runners. I want to set the number of elements in the array against the number of runners so I can use the unmatched array elsewhere within my sheet and know unclosed_liabilities_array(1,1) refers to runner 1 etc.

I've managed to sort it using ReDim from the bottom part of Ricks post and tweaking the row after a bit of debugging to check the upper and lower boundaries of both arrays. But it'd be good if someone could give me a quick explanation of the boundary dimensions as I'm still not sure why it works and another doesn't.

It's working fine with (I hadn't taken into account I was going from BB14 and starting at 1 in some testing so the runner data wasn't matching)

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

I'm not sure why the above works and the following doesn't though

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

I'm unclear what the numbers declaring the array are actually setting as with a single one column array I could simply refer to data within the array like unclosed_liabilities_array(2,1) whereas when I'mm ReDim'ing the array it only seems to work if I'm declaring two values as 1 To LrowRunners - 13

 

[Norie]

What type of arrays are you used to using in PHP?

 

[Tetra201]

Looks like everybody is totally confused. Here are my 5¢:

If you do this

Dim unclosed_liabilities_array As Variant
unclosed_liabilities_array = sht_runners.Range("BB14:BA" & LrowRunners).Value

you will get your unclosed_liabilities_array dimensioned as (1 To LrowRunners - 13, 1 To 2). Do you still need to ReDim it?

 

[JackDanIce]

Reading data to an array from the sheet requires a 2D array, hence you tend to identify the row and column number of your top-left cell together with the bottom-right cell for the data table then read that straight into the array without setting the dimension size.

ReDim unclosed_Liabilities_array(1 to LrowRunners - 13) as variant doesn't read a single column from the sheet as there is no column dimension provided, a cell must have a row index AND a column index to exist on a spreadsheet. You can try:

ReDim unclosed_Liabilities_array(1 to LrowRunners - 13, 1 to 1)

for a single column.

Also, with:

Sub Macro1()


    Dim LR   As Long
    Dim LC   As Long
    Dim arr()   As Variant
        
    With Sheets("Sheet1")
        'LastRow in column B
        LR = .Cells(.Rows.count, 2).End(xlUp).row
        'Assume header is in row 14
        LC = .Cells(14, .Columns.count).End(xlUp).row
        
        arr = .Range("B14").Resize(LR - 13, LC - 1).Value
    End With
    
End Sub

If LR (last used row in specified column) = 24 and because first row is row 14, we need to adjust LR for the resize component, i.e. end_row - start_row + 1. If LR = 24, then B14.Resize(24-14+1) is same as B14:B24
Similarly if LC (last used column in specified row) = 10 and first column is 2, we need to adjust LC for the resize component, i.e. end_column - start_column + 1. i.e. B14.resize(11, 10-2+1) is same as range B14:J24

(column A = 1, B = 2 etc)

Then it leaves arr = Range("B14").Resize(LR-14+1, LC-2+1).Value which is arr = Range("B14:J24").Value

Reading data from a sheet doesn't need the array to be pre-dimmed

 

[MrTeeny]

Three dimensional arrays and json to stdclass arrays etc, I'm so used to being able to dump the array to screen (print_r())so I can see the structure and contents when debugging which doesn't seem available with VBA.

Been reading up on the VBA arrays and a bit clearer now as I've tweaked my code so ReDim unclosed_liabilities_array(1 To LrowRunners - 13) As Variant works. The other parts of my code were referring to the elements like so

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

which worked fine when the array was set by selecting a range.

[/COLOR]unclosed_liabilities_array = sht_runners.Range("BB14:BB" & LrowRunners).Value[COLOR=#574123]

Works fine now simply using

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

I am still confused about the data structure though as to why

[/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

 

[MrTeeny]

I was slow typing looks like I wasn't using the correct syntax and should have been using 1 to 1 to set the 'column' boundary.

Thanks for all your help guys, it's made the structure of the arrays much clearer to me now and will be a great help with the other programming bits I wanted to do as I can now visualise how the data is laid out and how to add/amend arrays