excel vba Ubound/Lbound error

Question

UBound seems to not be returning anything. I am using the Pricer function in excel and passing it a column. Did I mismatch my data types? I UBound on a dummy array that accessed arrP.Value and that didnt work either. Thoughts?

Function Pricer(arrP As Variant) As Double
        sd = Application.WorksheetFunction.StDevP(arrP)
        avg = Application.WorksheetFunction.Average(arrP)
        PriceUB = avg + sd
        PriceLB = avg - sd
        MsgBox UBound(aarP)
        Pricer = Application.WorksheetFunction.Average(arrP)
End Function

in MsgBox UBound(aarP) the aarP should be arrP. Otherwise, it should work fine. — Gene
you can avoid errors that @Gene brought up by including the line Option Explicit at the very top of your vba code — Marcucciboy2

Vityata Vityata · Accepted Answer · 2018-07-30T20:01:31

Option Explicit is exactly what would save you the next time you are suffering from "fat fingers". Just do not forget to declare the other variables:

Option Explicit

Public Sub TestMe()    
    MsgBox Pricer(Array(1, 2, 3, 4, 5, 100))    
End Sub

Function Pricer(arrP As Variant) As Double

    Dim sd, avg, PriceUB, PriceLB
    sd = Application.WorksheetFunction.StDevP(arrP)
    avg = Application.WorksheetFunction.Average(arrP)
    PriceUB = avg + sd
    PriceLB = avg - sd
    MsgBox UBound(arrP)
    Pricer = Application.WorksheetFunction.Average(arrP)

End Function

To use the formula in Excel worksheet, pass it like this:

The semicolon could be a comma, depending on your language settings:

To make the Pricer() work with Range() as an Excel formula this is what to do:

Function Pricer(arrP As Range) As Double

    MsgBox arrP.Cells.Count
    Pricer = Application.WorksheetFunction.Average(arrP)

End Function

excel vba Ubound/Lbound error

1 Answers