CODEWITHSUNDEEP
vbaexcel
user5670600
user5670600

Reputation: 1

excel vba Ubound/Lbound error

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

Upvotes: 0

Views: 427

Answers (1)

Vityata
Vityata

Reputation: 43585

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:

enter image description here

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

enter image description here

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

Upvotes: 0

Related Questions