×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

VBA Excel Array inside an Array

VBA Excel Array inside an Array

VBA Excel Array inside an Array

(OP)
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:

CODE

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) 

CODE

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:

CODE

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

RE: VBA Excel Array inside an Array

Is this something that you trying to achieve?

CODE

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:
This
is
my
test
of
parameters

---- Andy

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

RE: VBA Excel Array inside an Array

A Dictionary or Collection might work well here.

RE: VBA Excel Array inside an Array

> 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.

CODE

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:

CODE

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 



RE: VBA Excel Array inside an Array

(OP)
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:

CODE

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...

RE: VBA Excel Array inside an Array

A Dictionary or Collection might work well here.

Strongm's example is non-constraining.

RE: VBA Excel Array inside an Array

A long-winded "solution":

CODE

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

RE: VBA Excel Array inside an Array

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

CODE -->

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:
current_data_record = Array123(0) (0)

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

combo

RE: VBA Excel Array inside an Array

Quote (strongm)

maximum line length is 1023 characters

Looks like there is also a limit of how many line continuations you can have - 24
smile

---- Andy

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

RE: VBA Excel Array inside an Array

By way of another example:

CODE --> VBA

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 

CODE --> Window

load

call do_things(mycoll)
 1234 
a string
 123.456 
another string 

RE: VBA Excel Array inside an Array

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

Where are all the things coming from?

RE: VBA Excel Array inside an Array

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: VBA Excel Array inside an Array

Well said Skip.

RE: VBA Excel Array inside an Array

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."

soapbox
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.
soapbox

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: VBA Excel Array inside an Array

>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

RE: VBA Excel Array inside an Array

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

RE: VBA Excel Array inside an Array

>a Primary Key?

Or the recordset itself ... possibly disconnected.

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

RE: VBA Excel Array inside an Array

Quote (strongm)

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close