# 2D Array From A Function

• ### 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

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