Excel VBA variable variable names

Question

I create a lot of tables in my macro. So I am trying to create a module , table_mod , that generates tables and places it three rows below the existing last row and I plan to call the table_mod whenever I need to create tables. But I am running into the fact that I need my variables to be dependent on the table name that the user gives. For example:

Sub Main()


' Below are all the data that my macro will generate in its number crunching

var_12_count = "Jordan"
var_13_count = "Kobe"

var_21_count ="Rings"

var_22_count = 6
var_23_count = 5

var_title_count = "This table lists the number of rings won by Jordan and Kobe"


var_12_transpose = "Rings"

var_21_transpose = "Jordan"
var_31_transpose = "Kobe"

var_22_transpose = 6
var_32_transpose = 5

var_title_transpose = "This is the transpose of the previous table"


' My intention is to call table commands like this to spit out the earlier generated data in nicely formatted tables.    

table_mod(2,3,"count")
table_mod(3,2,"transpose")



End Sub

Sub table_mod(row As Long, column As Long, name As String)


 Set wb As ActiveWorkbook
 Set ws As wb.ActiveSheet

With ws

lr = .Range("A" & .Rows.Count).End(xlUp).Row


.Cells(lr+3,1).Value =  var_title_&name


For i = 1 To row
        For j = 1 To column

               .Cells(lr+6+i,j).Value = var_&CStr(i)&CStr(j)&_&name

        Next j 
Next i

End With

End Sub

The output should look like

 This table lists the number of rings won by Jordan and Kobe


        Jordan  Kobe
 Rings    6      5    




This is the transpose of the previous table


         Rings
  Jordan   6
  Kobe     5

But I don't know how to combine the variable values with strings so VBA would know to read the values from the correct variables.

I am open to any suggestions of creating formatted/standardized tables in some alternate fashion. I intend for all my tables to have identical formatting in terms of lines, fonts, colors etc but I have not included all those aspects of the code. Right now I am getting red colored lines and unexpected end messages on the lines. Thanks

If I get this to work, I hope to do with with every repetitive procedure, indeed every procedure in my macro. Which I am guessing is how everybody does things anyway.

@SiddharthRout No, I intend for them to be of any size. As of today, my largest table as 10 rows and 4 columns but that might change going forward. — Amatya
– Amatya, Commented Dec 13, 2013 at 21:55
How are you getting the input for the tabe? Have you considered using arrays and then passing them to the funciton? — Siddharth Rout
– Siddharth Rout, Commented Dec 13, 2013 at 22:00
@SiddharthRout I haven't considered doing that. The numbers are computed along the way and I could just as well be re Dim-ing an array and store the numbers in some multidimensional martrix. That would be much easier. Thanks. Let me try and implement that. — Amatya
– Amatya, Commented Dec 13, 2013 at 22:03

Amatya · Accepted Answer · 2013-12-13 23:18:42Z

Follwing Siddharth Rout's suggestion, I did this and it works!!!!

   Dim tabArray As Variant

  'I defined the array of tables values outside everything because otherwise the other Sub was not able to access it.

Sub Main()




    ReDim tabArray(0 To 10, 0 To 10, 0 To 0)

    ' Apparently, you can only ReDim the last dimension, so I picked some large number for the first two dimensions, which are my rows and columns and then the last dimension will be my table title. I will keep incrementing it as I add more tables.

    tabArray(0, 0, 0) = "This table lists the number of rings won by Jordan and Kobe"

    tabArray(1, 2, 0) = "Jordan"
    tabArray(1, 3, 0) = "Kobe"

     tabArray(2, 1, 0) = "Rings"

    tabArray(2, 2, 0) = 6
    tabArray(2, 3, 0) = 5







      ReDim Preserve tabArray(0 To 10, 0 To 10, 0 To 1)


      tabArray(0, 0, 1) = "This is the transpose of the previous table"

    tabArray(2, 1, 1) = "Jordan"
    tabArray(3, 1, 1) = "Kobe"

     tabArray(1, 2, 1) = "Rings"

    tabArray(2, 2, 1) = 6
    tabArray(3, 2, 1) = 5





   Call table_mod(2, 3, 0)
    Call table_mod(3, 2, 1)



    End Sub

    Sub table_mod(row As Long, column As Long, number As Integer)





    lr = Range("A" & Rows.Count).End(xlUp).row




    Cells(lr + 3, 1).Value = tabArray(0, 0, number)


    For i = 1 To row
            For j = 1 To column

                   Cells(lr + 6 + i, j).Value = tabArray(i, j, number)

            Next j
    Next i



    End Sub

This works perfectly but it seems like a waste of space to start up from with a gigantic array. If there is some way to keep the rows and the variables bit also flexible, like maybe having an "array of arrays" where one array is a 2D array of table data and the main array contains it and contains the table name. Is this possible?

Thanks again to Siddharth Rout for suggesting Arrays.

Collectives™ on Stack Overflow

Excel VBA variable variable names

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related