VBA Excel Array inside an Array 1

[remeng]

Hi,

Is there a way to create an array inside an array? Apparently there is a limit to the number of values that can be passed from one sub to another sub. Here is what I am trying to do.

final result - seems to have a character limit:

call test(
current_data_record,  current_narrative_split_value,  current_veneer_material_grade_row,  current_veneer_part_path_series_row,  current_veneer_mach_dest_row, current_veneer_non_p_plan_length_table_row,  current_veneer_p_grade_suite_plan_length_table_row, current_veneer_p_grade_non_suite_plan_length_table_row, data_material_index, data_narrative_index, data_part_path_index, data_quantity_each_index, data_Model_Number_index, data_Model_Number_index, veneer_rule_material_grade_exception_grade_index, veneer_rule_part_path_series_index, veneer_rule_part_planning_req_index, veneer_rule_grade_index, veneer_rule_grade_ID_index, veneer_rule_species_index, veneer_rule_species_ID_index, veneer_rule_machine_species_index, veneer_rule_machine_ID_index, veneer_rule_non_p_min_index, veneer_rule_non_p_max_index, veneer_rule_non_p_dim_index, veneer_rule_p_suite_grade_index, veneer_rule_p_suite_mach_dest_index, veneer_rule_p_suite_mach_dest_index, veneer_rule_p_non_suite_grade_index, veneer_rule_p_non_suite_min_index, veneer_rule_p_non_suite_max_index, veneer_rule_p_non_suite_dim_index, total_data_record_count, veneer_rule_material_grade_count, veneer_rule_part_path_count, veneer_material_grade_path_count, veneer_material_species_count, veneer_machine_count, veneer_non_p_count, veneer_p_suite_count, veneer_p_non_suite_count)

sub test(
current_data_record,  current_narrative_split_value,  current_veneer_material_grade_row,  current_veneer_part_path_series_row,  current_veneer_mach_dest_row, current_veneer_non_p_plan_length_table_row,  current_veneer_p_grade_suite_plan_length_table_row, current_veneer_p_grade_non_suite_plan_length_table_row, data_material_index, data_narrative_index, data_part_path_index, data_quantity_each_index, data_Model_Number_index, data_Model_Number_index, veneer_rule_material_grade_exception_grade_index, veneer_rule_part_path_series_index, veneer_rule_part_planning_req_index, veneer_rule_grade_index, veneer_rule_grade_ID_index, veneer_rule_species_index, veneer_rule_species_ID_index, veneer_rule_machine_species_index, veneer_rule_machine_ID_index, veneer_rule_non_p_min_index, veneer_rule_non_p_max_index, veneer_rule_non_p_dim_index, veneer_rule_p_suite_grade_index, veneer_rule_p_suite_mach_dest_index, veneer_rule_p_suite_mach_dest_index, veneer_rule_p_non_suite_grade_index, veneer_rule_p_non_suite_min_index, veneer_rule_p_non_suite_max_index, veneer_rule_p_non_suite_dim_index, total_data_record_count, veneer_rule_material_grade_count, veneer_rule_part_path_count, veneer_material_grade_path_count, veneer_material_species_count, veneer_machine_count, veneer_non_p_count, veneer_p_suite_count, veneer_p_non_suite_count)

'do a lot of stuff with these variables

end sub

possible work around using nested arrays // array inside array:

one = array_one(current_data_record,  current_narrative_split_value,  current_veneer_material_grade_row,  current_veneer_part_path_series_row,  current_veneer_mach_dest_row, current_veneer_non_p_plan_length_table_row)

two = array_two(current_veneer_p_grade_suite_plan_length_table_row, current_veneer_p_grade_non_suite_plan_length_table_row, data_material_index, data_narrative_index, data_part_path_index, data_quantity_each_index)

three = array_three(data_Model_Number_index, data_Model_Number_index, veneer_rule_material_grade_exception_grade_index, veneer_rule_part_path_series_index, veneer_rule_part_planning_req_index, veneer_rule_grade_index)

etc....

call test(array(one),array(two),array(three), etc..)

any ideas?

Mike

 

[Andrzejek]

Is this something that you trying to achieve?

Option Explicit

Sub StartHere()
Dim ary() As String

ary = Split("This,is,my,test,of,parameters", ",")
Call test(ary)

End Sub

Sub test(ByRef MyParameters() As String)
Dim i As Integer

For i = 0 To UBound(MyParameters)
    Debug.Print MyParameters(i)
Next i

End Sub

In the Immediate Window you will get:[tt]
This
is
my
test
of
parameters
[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mintjulep]

A Dictionary or Collection might work well here.

 

[strongm]

> seems to have a character limit


Yep. VBA limitation - maximum line length is 1023 characters. Yuu can get around this by using VBA's line continuation capability, e.g.

Call test(current_data_record, current_narrative_split_value, current_veneer_material_grade_row, current_veneer_part_path_series_row, current_veneer_mach_dest_row, current_veneer_non_p_plan_length_table_row, current_veneer_p_grade_suite_plan_length_table_row, current_veneer_p_grade_non_suite_plan_length_table_row, data_material_index, data_narrative_index, data_part_path_index, data_quantity_each_index, data_Model_Number_index, veneer_rule_material_grade_exception_grade_index, veneer_rule_part_path_series_index, veneer_rule_part_planning_req_index, veneer_rule_grade_index, veneer_rule_grade_ID_index, veneer_rule_species_index, veneer_rule_species_ID_index, veneer_rule_machine_species_index, veneer_rule_machine_ID_index, veneer_rule_non_p_min_index, veneer_rule_non_p_max_index, veneer_rule_non_p_dim_index, veneer_rule_p_suite_grade_index, veneer_rule_p_suite_mach_dest_index, veneer_rule_p_suite_mach_dest_index, veneer_rule_p_non_suite_grade_index, _
 veneer_rule_p_non_suite_min_index, veneer_rule_p_non_suite_max_index, veneer_rule_p_non_suite_dim_index, total_data_record_count, veneer_rule_material_grade_count, veneer_rule_part_path_count, veneer_material_grade_path_count, veneer_material_species_count, veneer_machine_count, veneer_non_p_count, veneer_p_suite_count, veneer_p_non_suite_count)

and yes, this works in the procedure heading as well (which means I learned something new about VB today!)

But ... messy. So, as you ask, an array would be one way around this - but might be better off using a Collection. The you can (almos directlyt) refer to your passed variables by name within the called sub, e.g:

[blue]Public Sub SimpleExample()
    Dim whatever As New Collection
    current_narrative_split_value = 1897.24
    
    whatever.Add current_data_record, "current_data_record"
    whatever.Add current_narrative_split_value, "current_narrative_split_value"
    
    Call test(whatever)
End Sub

Public Sub test(MyParams As Collection)
    MsgBox "current_narrative_split_value is " & MyParams("current_narrative_split_value")
End Sub[/blue]

 

[remeng]

Hi strong,

It took me a while to get your method working. Very cool by the way. I don't think that is going to help me. I would be better off keeping all of the formulas in the same macro instead. I'd have to create a list for the collection names in the sub macro.

Hi Andrzejek,

The split operation is good, but then I need to get the "index" value. I'm back in the same situation as it being better off to keep all of the formulas in the same sub macro.

----

Maybe a little more info would help.

I have a ton of nested IF statements with a huge mess of DO / LOOPS. I was hoping to avoid the mass amount of confusion that occurs when trying to see what the code is doing. I've tried using Debug.Print and the Immediate screen, but if I can break it into subs based on the result of the IF statements, that would be a lot cleaner in my opinion.

Idea:

Do Until a > b

If something happens then

call macro_two(array of all variables)

'Bringing all the variables would allow for everything to be transferred and pulled from as needed without getting into a mess since there are table index, row counts, and then other variables from tables. 

else: end if

a=a+1

Loop

Any solution ideas?


maybe VBA just can't handle the awesomeness that this code requires...

 

[mintjulep]

A Dictionary or Collection might work well here.

Strongm's example is non-constraining.

 

[Andrzejek]

A long-winded "solution":

Option Explicit

Dim current_data_record As Long
Dim current_narrative_split_value As Long
Dim current_veneer_material_grade_row As Long
Dim current_veneer_part_path_series_row As Long
Dim current_veneer_mach_dest_row As Long
Dim current_veneer_non_p_plan_length_table_row As Long

Sub StartHere()

current_data_record = 123
current_narrative_split_value = 321
current_veneer_material_grade_row = 76
current_veneer_part_path_series_row = 54
current_veneer_mach_dest_row = 74
current_veneer_non_p_plan_length_table_row = 69

Call test

End Sub

Sub test()

Debug.Print current_data_record
Debug.Print current_narrative_split_value
Debug.Print current_veneer_material_grade_row
Debug.Print current_veneer_part_path_series_row
Debug.Print current_veneer_mach_dest_row
Debug.Print current_veneer_non_p_plan_length_table_row

End Sub

I would avoid it if a better solution be available...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

What you ask is possible for arrays stored in variant using Array function:

one = Array(current_data_record,  current_narrative_split_value,  current_veneer_material_grade_row, ... etc)
two = Array(current_veneer_p_grade_suite_plan_length_table_row, ...)
three = Array(data_Model_Number_index, data_Model_Number_index, ...)
Array123 = Array(one, two, three)

with nested access to variables:
[tt]current_data_record = Array123(0) (0)[/tt]

For such variable it is possible to test boundaries with LBound and UBound, resize with Redim Preserve.

combo

 

[Andrzejek]

maximum line length is 1023 characters

Looks like there is also a limit of how many [tt]line continuations[/tt] you can have - 24


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mintjulep]

By way of another example:

Global Mycoll As New Collection

Public Sub Load()


Mycoll.Add 1234, "Item_1"
Mycoll.Add "a string", "Item_2"
Mycoll.Add 123.456, "Item_3"
Mycoll.Add "another string", "Item_4_"

End Sub

Public Sub Do_Things(param As Collection)
Dim item As Variant

For Each item In param
    Debug.Print (item)
Next item

End Sub

load

call do_things(mycoll)
 1234 
a string
 123.456 
another string

 

[mintjulep]

Do you really need to concurrently manipulate so many things in a single procedure?

Where are all the things coming from?

 

[SkipVought]

Ya know, sometimes a user has in mind a process or method, when they ought to be thinking about here's the data I have and this is the result I need at the end. That's the specification. There may be several ways that that could be accomplished based on the capabilities of various tools or the capability of the developer.

The OP wants to manipulate arrays of arrays. But maybe, if we knew the underlying requirements, there could possibly be other options???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[mintjulep]

Well said Skip.

 

[SkipVought]

I don't know how many times a user started out telling me HOW they thought a task could be accomplished. I think the leading "helpful suggestion" I heard was, "I need some sort of VLOOKUP"

"Just the facts, ma'am."


Edit: Been thinking about this (since I've got LOTS of retirement time on my hands). Sometimes it's "the boss" who tells the employee HOW to do their job, too. Then it gets complicated. "She/he wants me to use a VLOOKUP to do this project." Well, ya could use a VLOOKUP, but you'd have to change the table that she/he told you not to touch.

There's probably a simpler approach.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[strongm]

>I'd have to create a list for the collection names in the sub macro

With the array solutions you'd have to have a list of which indexes map to each variable. So magic numbers ...

As Skip says above, you probably need to tell us what it is you are trying to achieve. At the moment I'm guessing you are trying to break a very large procedure down into a number of smaller units

 

[Andrzejek]

All the names of the arguments OP wants to pass to the Sub suggest to me that they may be just names of the fields in a table (tables?) in the data base. The solution may be as simple as passing just one or two arguments (a Primary Key?) and the rest could be retrieved in the Sub.

After all, those arguments get their values from somewhere. So, get them in the Sub instead of passing them.

Just a guess...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

>a Primary Key?

Or the recordset itself ... possibly disconnected.

All sorts of options. We need the OP to provide some more detail ...

 

[mintjulep]

At the moment I'm guessing you are trying to break a very large procedure down into a number of smaller units

My guess is exactly the opposite.

That they are trying to take what should be small units and mash them all together into a single (unnecessarily complicated) procedure.

But we are guessing, because remeng hasn't shared what needs to be done.

 

[remeng]

Hi Folks,

Sorry for the massive delay in response. Basically it was Management driven. I had to show them how it kept crashing so they would stop trying to get that direction to work.

In the end I created calls to macros in a sequence so that only specific values were passed to specific code. Step into the first, step out of the first, step into the second...

I also used Constance for anything that I knew had a low probability of changing and if it was an exception, just set rules in place to adjust accordingly.

In the end instead of one master macro to capture all of the data in one shot and pass all of the data to another macro, it was broken into about 10 separate macros to get the same result.

Thanks for the learning lesson.

Mike

 

[strongm]

>only specific values were passed to specific code
Yes, that would be more traditional ...