none
2D Array From A Function RRS feed

  • Question

  • ** I am a TOTAL newbie at programming so please keep that in mind**

    I have created a program that passes arrays and single numbers (doubles) into a function. The function compiles all the numbers I need into a 2D array. I than want to access this 2D array so I can use these numbers in further calculations. I have simplified the concept of what I want to do by throwing some code together below. When I print the results to a Worksheet, none of the text appears in the first two columns (Name and Month). And all I get are 0's for the third and fourth column (Birthdate and Number). Why?!?

    In the actual code I am working on, when I try to create a variable (something like TopRight in my example below) to be equal to a single entry in the 2D Array, I keep getting "Error, type mismatch". Where is the mismatch??

    Sub Test()
    
    'Declare Input Arrays
    Dim Name(4) As String
    Dim Month(4) As String
    Dim Birthdate(4) As Double
    Dim Number(4) As Double
    
    'Fill Input Arrays
    a = Array(Tom, Emma, Jess, Bwee)
    b = Array(October, August, March, December)
    c = Array(29, 24, 25, 6)
    d = Array(1, 2, 3, 4)
    
    'Send Arrays into function
    Dim Results As Variant
    Results = DoubleArray(Name, Month, Birthdate, Number)
    
    'Print the 2-D Array from the function into a Worksheet in Excel
    For j = 1 To 4
        For i = 1 To 4
            Worksheets("Sheet1").Cells(i, j).Value = Results(i - 1, j - 1)
        Next i
    Next j
    
    'Create a variable named TopRight and make it equal to the top right cell in the Results 2D array
    Dim TopRight As Double
    TopRight = Results(0, 2)
    
    'Display the variable TopRight
    MsgBox (TopRight)
    
    End Sub
    
    Function DoubleArray(a() As String, b() As String, c() As Double, d() As Double) As Variant
    
    Dim SquareArray() As Variant    'Declare an array to use in the calculation
    ReDim SquareArray(4, 4)         'Redimension the array to hold the values
    
    'Fill the 2D array with the input arrays
    For i = 0 To 3
        SquareArray(i, 0) = a(i)
    Next i
    
    For i = 0 To 3
        SquareArray(i, 1) = b(i)
    Next i
    
    For i = 0 To 3
        SquareArray(i, 2) = c(i)
    Next i
    
    For i = 0 To 3
        SquareArray(i, 3) = d(i)
    Next i
    
    'Equate the function name to the array used in the calculation
    DoubleArray = SquareArray
    
    End Function
    
    

    Wednesday, August 12, 2015 3:11 PM

Answers

  • You declare Name, Month etc. but then populate arrays a, b etc. that you haven't declared.

    So Name, Month etc. aren't populated anywhere in the code. The default value for strings is the empty string  and the default value for numbers is 0 - hence nothing in columns A and B, and zeros in columns C and D.

    Here is a working version of your code:

    Sub Test()
        'Declare Input Arrays
        Dim a, b, c, d
        Dim i As Long, j As Long
    
        'Fill Input Arrays
        a = Array("Tom", "Emma", "Jess", "Bwee")
        b = Array("October", "August", "March", "December")
        c = Array(29, 24, 25, 6)
        d = Array(1, 2, 3, 4)
    
        'Send Arrays into function
        Dim Results As Variant
        Results = DoubleArray(a, b, c, d)
    
        'Print the 2-D Array from the function into a Worksheet in Excel
        For j = 1 To 4
            For i = 1 To 4
                Worksheets("Sheet1").Cells(i, j).Value = Results(i - 1, j - 1)
            Next i
        Next j
    
        'Create a variable named TopRight and make it equal to the top right cell in the Results 2D array
        Dim TopRight As Double
        TopRight = Results(0, 2)
    
        'Display the variable TopRight
        MsgBox (TopRight)
    End Sub
    
    Function DoubleArray(a, b, c, d) As Variant
        Dim SquareArray(3, 3) As Variant   'Declare an array to use in the calculation
        Dim i As Long
    
        'Fill the 2D array with the input arrays
        For i = 0 To 3
            SquareArray(i, 0) = a(i)
        Next i
    
        For i = 0 To 3
            SquareArray(i, 1) = b(i)
        Next i
    
        For i = 0 To 3
            SquareArray(i, 2) = c(i)
        Next i
    
        For i = 0 To 3
            SquareArray(i, 3) = d(i)
        Next i
    
        'Equate the function name to the array used in the calculation
        DoubleArray = SquareArray
    End Function
    


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Gradient127 Thursday, August 13, 2015 6:44 PM
    Wednesday, August 12, 2015 3:49 PM

All replies

  • You declare Name, Month etc. but then populate arrays a, b etc. that you haven't declared.

    So Name, Month etc. aren't populated anywhere in the code. The default value for strings is the empty string  and the default value for numbers is 0 - hence nothing in columns A and B, and zeros in columns C and D.

    Here is a working version of your code:

    Sub Test()
        'Declare Input Arrays
        Dim a, b, c, d
        Dim i As Long, j As Long
    
        'Fill Input Arrays
        a = Array("Tom", "Emma", "Jess", "Bwee")
        b = Array("October", "August", "March", "December")
        c = Array(29, 24, 25, 6)
        d = Array(1, 2, 3, 4)
    
        'Send Arrays into function
        Dim Results As Variant
        Results = DoubleArray(a, b, c, d)
    
        'Print the 2-D Array from the function into a Worksheet in Excel
        For j = 1 To 4
            For i = 1 To 4
                Worksheets("Sheet1").Cells(i, j).Value = Results(i - 1, j - 1)
            Next i
        Next j
    
        'Create a variable named TopRight and make it equal to the top right cell in the Results 2D array
        Dim TopRight As Double
        TopRight = Results(0, 2)
    
        'Display the variable TopRight
        MsgBox (TopRight)
    End Sub
    
    Function DoubleArray(a, b, c, d) As Variant
        Dim SquareArray(3, 3) As Variant   'Declare an array to use in the calculation
        Dim i As Long
    
        'Fill the 2D array with the input arrays
        For i = 0 To 3
            SquareArray(i, 0) = a(i)
        Next i
    
        For i = 0 To 3
            SquareArray(i, 1) = b(i)
        Next i
    
        For i = 0 To 3
            SquareArray(i, 2) = c(i)
        Next i
    
        For i = 0 To 3
            SquareArray(i, 3) = d(i)
        Next i
    
        'Equate the function name to the array used in the calculation
        DoubleArray = SquareArray
    End Function
    


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Gradient127 Thursday, August 13, 2015 6:44 PM
    Wednesday, August 12, 2015 3:49 PM
  • Thanks for the help!
    I copied your code, but am getting a Compile error: Syntax error for under the 'Fill Input Arrays; a and b. Any idea why?

    I don't know if it makes any difference, but I am using the Microsoft Visual Basic for Applications program from the Development tab in Excel.

    Wednesday, August 12, 2015 5:13 PM
  • If you copied the code as I posted it, it should run. This is the result I see when I run it in Excel 2013:


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, August 12, 2015 8:04 PM