VBA: subscript out of range (runtime error 9)

[Aberdham]

Hi guys, I would like to use the following code to produce the result of an array using the power of 3. i.e 0, 1.7320, 3, 5.1961 with the power interval of (1/2)

I have come up with the following code but I have come across with runtime error 9 pointing me to the line a(i) = 3^I

Could someone please advise how I can modify the code to get the desired result without changing the original code?

Thank you in advance!!

 

[Trebor76]

Try this:

Option Explicit
Sub Test()
    
    Dim RES As String
    
    RES = powerThree(8)
    Debug.Print RES

End Sub
Function powerThree(n As Long) As String
    
    Dim i As Currency
    Dim j As Long
    
    For i = 0 To n Step 0.5
        j = j + 1
        powerThree = IIf(Len(powerThree) = 0, Addth(CStr(j)) & ": " & Round(3 ^ i, 4) & " (3^" & i & ")", powerThree & vbNewLine & Addth(CStr(j)) & ": " & Round(3 ^ i, 4) & " (3^" & i & ")")
    Next i

End Function
Function Addth(pNumber As String) As String

    'https://www.extendoffice.com/documents/excel/3757-excel-format-number-1st-2nd-3rd.html
    'UpdatebyExtendoffice20160628
    Select Case CLng(VBA.Right(pNumber, 1))
        Case 1
            Addth = pNumber & "st"
        Case 2
            Addth = pNumber & "nd"
        Case 3
            Addth = pNumber & "rd"
        Case Else
            Addth = pNumber & "th"
    End Select
    
    Select Case VBA.CLng(VBA.Right(pNumber, 2))
        Case 11, 12, 13
            Addth = pNumber & "th"
    End Select
    
End Function

 

[bobsan42]

Function PowerThree(n)

    Dim a(0 to n)
 
    For i = 0 To n
        a(i) = 3 ^ (i/2)
    Next i
 
    PowerThree = a

End Function

Or

Function PowerThree(n)

    Dim a(0 to n)
 
    For i = 0 To n step 0.5
        a(i) = 3 ^ i
    Next i
 
    PowerThree = a

End Function

 

[Marcelo Branco]

Maybe...

Sub aTest()
    Dim Res As Variant
    
    Res = powerThree(8)
    arrayPrint Res
End Sub

Function powerThree(n As Long)
    Dim a() As Variant, i As Long, j As Double
    
    ReDim a(n)
    For i = 0 To n
        a(i) = 3 ^ j
        j = j + 0.5
    Next i
    powerThree = a
End Function

Sub arrayPrint(Res)
    Dim i As Long
    
    'Header in A1
    Cells(1, 1) = "Power Three"
    'Results in column A beginning in A2
    For i = LBound(Res) To UBound(Res)
        Cells(i + 2, 1) = Res(i)
    Next i
End Sub

Hope this helps

M.

 

[Aberdham]

oh great thank you all for your help !