nateh1026
08-09-2013, 07:58 AM
Sub Calculate()
Dim Lengths() As Variant
Dim a As Double
a = Range("B2")
Lengths = Ram(a)
MsgBox Lengths(1) 'Issue 1
Range("B21: D21") = Lengths() 'Issue 2
End Sub
Function Ram(a) As Variant()
Dim Tap() As Variant
Dim b As Integer
Dim c As Integer
b = a + 1
c = b + 1
Tap() = Array(a,b,c)
Ram = Tap()
End Function
The simplified code above is giving me problems and I'm not sure how to sort it out. I am trying to perform a calculation in "Ram" which is called in the Calculate Sub. I am pulling varaibles from an excel sheet to do so and returning variables to the excel sheet as an array. I believe the problems have something to do with how I'm structuring the sub or with the variant data type.
Issue 1: Can't Message Box values from my variant array, gives me a "Run-time Error '9': Subscript out of range"
Issue 2: If my array is holding values (which I can't tell if it is), it won't place them back into my Excel sheet; "Run-time Error '13': Type Mismatch"
Any help would be appreciated! I've been working on this for a while now and can't get a functioning solution.
Dim Lengths() As Variant
Dim a As Double
a = Range("B2")
Lengths = Ram(a)
MsgBox Lengths(1) 'Issue 1
Range("B21: D21") = Lengths() 'Issue 2
End Sub
Function Ram(a) As Variant()
Dim Tap() As Variant
Dim b As Integer
Dim c As Integer
b = a + 1
c = b + 1
Tap() = Array(a,b,c)
Ram = Tap()
End Function
The simplified code above is giving me problems and I'm not sure how to sort it out. I am trying to perform a calculation in "Ram" which is called in the Calculate Sub. I am pulling varaibles from an excel sheet to do so and returning variables to the excel sheet as an array. I believe the problems have something to do with how I'm structuring the sub or with the variant data type.
Issue 1: Can't Message Box values from my variant array, gives me a "Run-time Error '9': Subscript out of range"
Issue 2: If my array is holding values (which I can't tell if it is), it won't place them back into my Excel sheet; "Run-time Error '13': Type Mismatch"
Any help would be appreciated! I've been working on this for a while now and can't get a functioning solution.