How (if possible) do I define and populate a global array variable from a worksheet in VBA Excel 2010?

Question

*******Background******* For reasons not up for debate I am using Excel 2010 and VBA to program a scientific model that involves a user inputting data to a worksheet from an online data bank for each species involved. The number of species may change from simulation to simulation. As the program iterates, it calls many functions multiple times (some in the tens of thousands of times). The execution speed of the program is becoming too long so I would like to try and speed it up. In my mind two easy things to increase the execution speed are to decrease the number of worksheet calls and to minimize the number of variants I have to use.

Also of note that several of the functions share the same constants.

I've looked elsewhere on stackexchange and other sites but I still haven't found what I'm looking for ♪. Maybe I just don't have the right search terms.

---

The question(s) Because the number of species is not constant and because I would like to make a few arrays of constants available to multiple functions is there away that I can define say a global variable that is an double (or single) precision array that is dimensionalized when a sub is run, reads the constants from the excel sheet once, and then is destroyed when my "main" sub is finished executing?

I could create the array(s) in the main sub and pass it as an argument but several of my functions call other functions as arguments already and several of my lines are extremely long and hard to read. By making these constants arguments to pass to the functions only increases the length of these lines.

Second question if I can't create a global array variable is there away to call the worksheet once (like I have done) but to make the variable a double instead of a variant? I couldn't get the following to work because of the type mismatch error. Dim C() As Double redim c(1 to 7, 1 to n) C = Application.Transpose(Worksheets("Viscosity2").Range("J10:p19"))

Function example: I have a function that I am running in VBA. This function is called tens of thousands of times in the course of my program. I would like to make the C(1 to 7, 1 to n) array and the mw(1 to n) array double precision arrays that call the worksheet once and then are available to multiple functions.

Below is that example function:

Function mumx(y, T)
    'this function calculates the mixture viscosity using the Chapman Enskog Wilke method 
    'using the mol fraction vector, y, and the temperature T

    n = UBound(y, 1) - LBound(y, 1) + 1  'number of species


'***********Get Equation Parameters from Worksheet**************
Dim C() As Variant
C = Application.Transpose(Worksheets("Viscosity2").Range("J10:p19"))
Dim mw As Variant
mw = Application.Transpose(Worksheets("Viscosity2").Range("g10:g19"))
'***************************************************************

Dim mu() As Double
ReDim mu(1 To n)


For i = 1 To n Step 1
    mu(i) = (C(1, i) * (T ^ C(2, i))) / (1 + C(3, i) / T + (C(4, i) / (T ^ 2)))
Next i


Dim phi() As Double
ReDim phi(1 To n, 1 To n)

For i = 1 To n
    For j = 1 To n

        phi(i, j) = 1 / 8 ^ 0.5 * (1 + mw(i) / mw(j)) ^ -0.5 * (1 + (mu(i) / mu(j)) ^ 0.5 * (mw(j) / mw(i)) ^ 0.25) ^ 2

        test = 1
    Next j
  Next i

Dim denom As Double
Dim mumix As Double
denom = 0
mumix = 0
For i = 1 To n
    For j = 1 To n

        denom = denom + y(j) * phi(i, j)
    Next j


    mumix = mumix + y(i) * mu(i) / denom
    denom = 0
Next i



mumx = mumix
'where the units on mumx are in units of cP (which are 1 gm/(m*s))

End Function


    '************Example constants are as follows********
    'PS should someone stumble on this looking for say viscosity data 
    'the following constants just example constants
    '
    '
    'C(1, 1) = 0.00018
    'C(1, 2) = 0.000017
    'C(1, 3) = 0.001113
    'C(1, 4) = 0.00215
    'C(1, 5) = 0.0005255
    'C(1, 6) = 0.0011
    'C(1, 7) = 0.0006559
    'C(1, 8) = 0.00005
    'C(1, 9) = 0.00026
    'C(1, 10) = 0.002079
    '
    'C(2, 1) = 0.69
    'C(2, 2) = 1.115
    'C(2, 3) = 0.534
    'C(2, 4) = 0.46
    'C(2, 5) = 0.59
    'C(2, 6) = 0.563
    'C(2, 7) = 0.608
    'C(2, 8) = 0.90
    'C(2, 9) = 0.68
    'C(2, 10) = 0.4163
    '
    'C(3, 1) = -0.59
    'C(3, 2) = 0
    'C(3, 3) = 94.7
    'C(3, 4) = 290.
    'C(3, 5) = 106.
    'C(3, 6) = 96.3
    'C(3, 7) = 54.7
    'C(3, 8) = 0
    'C(3, 9) = 98.9
    'C(3, 10) = 353.
    '
    'C(4, 1) = 140.
    'C(4, 2) = 0
    'C(4, 3) = 0
    'C(4, 4) = 0
    'C(4, 5) = 0
    'C(4, 6) = 0
    'C(4, 7) = 0
    'C(4, 8) = 0
    'C(4, 9) = 0
    'C(4, 10) = 0
    '
    '
    'C(5, 1) = 0
    'C(5, 2) = 0
    'C(5, 3) = 0
    'C(5, 4) = 0
    'C(5, 5) = 0
    'C(5, 6) = 0
    'C(5, 7) = 0
    'C(5, 8) = 0
    'C(5, 9) = 0
    'C(5, 10) = 0
    '
    'C(6, 1) = 300
    'C(6, 2) = 300
    'C(6, 3) = 300
    'C(6, 4) = 300
    'C(6, 5) = 300
    'C(6, 6) = 300
    'C(6, 7) = 300
    'C(6, 8) = 300
    'C(6, 9) = 300
    'C(6, 10) = 300
    '
    'C(7, 1) = 1000
    'C(7, 2) = 1000
    'C(7, 3) = 1000
    'C(7, 4) = 1000
    'C(7, 5) = 1000
    'C(7, 6) = 1000
    'C(7, 7) = 1000
    'C(7, 8) = 1000
    'C(7, 9) = 1000
    'C(7, 10) = 1000
    '
    '
    '
    'mw(1) = 2.0158
    'mw(2) = 18.0148
    'mw(3) = 28.01
    'mw(4) = 44.009
    'mw(5) = 16.0426
    'mw(6) = 31.998
    'mw(7) = 28.014
    'mw(8) = 44.0962
    'mw(9) = 30.0694
    'mw(10) = 28.0536
    '
    ''******************************

Answer 1

Yes, you can and should* use an array to store the constants entered by the user and yes, you can make it global so that it doesn't have to be passed to the other functions.

Here's an example; note that the data is read into a Variant first, then transferred to the array--this was your missing step that lead to the Type Mismatch error. While this may look like too much code for the effort, transferring the data into the Double array will be many times faster than reading the cells one-by-one.

Public C() As Double

Public Sub PopulateArrayC(n As Integer)

    ReDim C(1 To 7, 1 To n)

    Dim v As Variant
    v = Application.Transpose(Worksheets("Viscosity2").Range("J10:P" & n + 10 - 1))

    Dim i As Integer, j As Integer
    For i = 1 To 7
        For j = 1 To n
            C(i, j) = v(i, j)
        Next j
    Next i

End Sub

*Reading from and writing to cells is very time consuming. Whenever possible, limit the number of reads and writes by*

1. storing frequently used values in variables, and
2. reading/writing whole ranges at a time.