VBA Variant Array issues with Excel

[nateh1026]

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.

[Kenneth Hobs]

Welcome to the forum! Please use code tags for code. Click the # icon and paste between tags.

It is not a good idea to use key words for you object or variable names that may collide with reserved key word commands and such. Do not mix variable types.

I added a value to B2 so delete or comment that line out. The results of Debug.Print will show in the Immediate window of the Visual Basic Editor (VBE).

Sub Calculatex()
  Dim Lengths() As Variant
  Dim a As Double
  Range("B2").Value = 4
  a = Range("B2").Value
  Lengths() = Ram(a)
  Debug.Print Lengths(1)
  Range("B21:D21").Value = Lengths()
End Sub


Function Ram(a As Double) As Variant
  Dim Tap() As Variant
  Dim b As Double
  Dim c As Double
  b = a + 1
  c = b + 1
  Tap() = Array(a, b, c)
  Ram = Tap()
End Function

[snb]

or

Sub M_snb()
  sn = Ram_snb(Range("B2").Value)
  Range("B21").Resize(, UBound(sn) + 1) = sn
End Sub

Function Ram_snb(x)
  Ram_snb = Array(x, x + 1, x + 2)
End Function