Calling type variables from another sub

Question

Hi I have a series of subroutines as follows:

1. DataCollection() : Collects data from the spreadsheet and writes it to custom type variables.

2. NewSub() : Does something else, but not relevant to the question.

I would like to keep the same variables previously declared, and having values assigned in the second sub. I think I have to make them global variables somehow, but could not work it out so far, whatever I do I get the variable not defined error. My code is as follows:

Option Explicit

Public Type Trucks
    NumberOfAxles As Integer
    AxleWeights(15) As Double
End Type

Public Sub DataCollection()

Dim NumberOfTrucks As Integer
Truck(10) As Trucks
Dim i, j, k As Integer

'Determine Number of Trucks
NumberOfTrucks = Cells(6, 8)

'Populate Truck Arrays (Trucks 1 to 5)

k = 0
For i = 1 To 5
    Truck(i).NumberOfAxles = Cells(9, 4 + 4 * k)
    k = k + 1
Next i

k = 0
For i = 1 To 5
    For j = 1 To Truck(i).NumberOfAxles
        Truck(i).AxleWeights(j) = Cells(31 + j, 3 + 4 * k)
    Next j
    k = k + 1
Next i

End Sub

Public Sub NewSub()

For i = 1 To Truck(10).NumberOfAxles
    Cells(27 + i, 22) = Truck(10).AxleWeights(i)
Next i

End Sub

Any ideas would be most welcome! Thanks!

Solution 1

Keep your variables in as limited a scope as possible.

If you call NewSub from DataCollection, then make Trucks() local to DataCollection and pass it as an argument to NewSub.

If you don't call one from the other but they are in the same module declare Trucks() as a module-level variable. To do that use the Private keyword and make the declaration at the top of the module outside of any procedures.

Finally, if NewSub is in a different module, you need to declare a global variable. Use the Public keyword and declare it in it's own module called MGlobals. Why it's own module? It's good practice to limit your use of global variables and declare them all in the same place so you can manage them more effectively. (That means move your public Type to MGlobals too.)

OK, having said all that, stop using Types now. At some point in your project, you're going to want some function that is beyond what Type can do for you. I know you don't think so, but it will happen. So you'll create a function that does it and it will become an unmanageable mess. So make a Truck class and a Trucks class. The Truck class will contain the two properties. The Trucks class will contain a private collection object that holds all the Truck instances. The only global variable you'll need is gclsTrucks. As long as that is in scope, all of your Truck instances. All of your heavy lifting should be on in the Truck class. A little extra work right now will save you big.

Solution 2

You can use global variables like follows.

Dim global_var As Integer
'

Sub doA()
global_var = global_var + 1
Debug.Print global_var

End Sub

Sub doB()
global_var = global_var + 10
Debug.Print global_var
End Sub

Sub main()
doA
doB
doA
End Sub

You declare your variable in

Truck(10) As Trucks

and not on

Public Type Trucks
    NumberOfAxles As Integer
    AxleWeights(15) As Double
End Type

In other words, just move the "Dim" to outside the routine.

Comments

[Dick Kusleika]

Here's somewhat of a jumping off point for class creation dailydoseofexcel.com/archives/2010/07/09/…