VBA Variant Array issues with Excel

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.

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).

Code:

---

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

---

or

Code:

---

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

---