Cannot use result of index in vba function for further computation

Question

I'm building a custom function in Excel VBA to receive the nth (here: 5th) element of an array with a Webull distribution. This works, but then I cannot do further computations with the result such as simple multiplication without getting an error.

Function weib(xr, shape, scaler)
    n = Application.Weibull_Dist(xr, shape, scaler * 100, 0)
    weib = n
End Function

Function good(xr, shape, scaler)
    n = Application.Index(Application.Weibull_Dist(xr, shape, scaler * 100, 0), 5, 0)
    good = n
End Function

Function nogood(xr, shape, scaler)
    n = Application.Index(Application.Weibull_Dist(xr, shape, scaler * 100, 0), 5, 0) * 1
    nogood = n
End Function

Why does the "nogood" function not work? It only adds * 1 at the end - how can I fix it?

BigBen · Accepted Answer · 2021-11-18 03:32:45Z

In testing, n is a Variant array with a lower bound of 1. That'll cause a Type Mismatch when attempting to multiply by 1. So one (inferior) solution is:

Function good(xr, shape, scaler)
    n = Application.Index(Application.Weibull_Dist(xr, shape, scaler * 100, 0), 5, 0)
    good = n(1)
End Function

Function nogood(xr, shape, scaler)
    n = Application.Index(Application.Weibull_Dist(xr, shape, scaler * 100, 0), 5, 0)
    nogood = n(1) * 1
End Function

The better solution is to use 1 instead of 0 in the Index call:

Function good(xr, shape, scaler)
    n = Application.Index(Application.Weibull_Dist(xr, shape, scaler * 100, 0), 5, 1)
    good = n
End Function

Function nogood(xr, shape, scaler)
    n = Application.Index(Application.Weibull_Dist(xr, shape, scaler * 100, 0), 5, 1) * 1
    nogood = n
End Function

You can even skip using Index entirely (maybe the best solution):

Function good(xr, shape, scaler)
    good = Application.Weibull_Dist(xr, shape, scaler * 100, 0)(5, 1)
End Function

Function nogood(xr, shape, scaler)
    nogood = Application.Weibull_Dist(xr, shape, scaler * 100, 0)(5, 1) * 1
End Function

Collectives™ on Stack Overflow

Cannot use result of index in vba function for further computation

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related