Type Mismatch

[SBF12345]

I am working with a User Defined Function to transform an array of nominal integer values to logarithms. I have the code below.

LogArray is a variant and I suspect this data type does not work with the integer values being fed into the worksheetfunction.

How can I build the array to hold log values?

'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'X  This UDF converts an array of nominal values into an array of logarithmic values    X
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


Function LogPriceData(Arr As Variant, numberObs As Integer) As Variant


Dim yy As Integer
Dim Array1 As Integer
Dim Array2 As Integer
Dim LogArray As Variant


ActiveWorkbook.Sheets("ActiveSheet").Activate


Array1 = Application.WorksheetFunction.Index(Arr, 1, 2)


For yy = 1 To numberObs


    Array2 = Application.WorksheetFunction.Index(Arr, yy, 2)
        
    LogArray(yy) = Application.WorksheetFunction.Log(Array2, Array1)


Next yy


LogPriceData = logarray()


End Function

 

[NdNoviceHlp]

Dim Array1() as Variant, Array2() as Variant, LogArray() as Variant

HTH. Dave
ps. It also seems that numberobs should be a double?

 

[SBF12345]

Thanks!

I have one more error also a mismatch on the line

Array1() = Application.WorksheetFunction.Index(Arr, 1, 2)

Array1 is now a variant, Arr is a variant as well, however the Index worksheet function is going to return a single value at column 2 row 1. My intention is to define array1 as a series of values defined by the index value for each observation in the space yy. Do you know how I might be able to create array1 with these characteristic?

 

[NdNoviceHlp]

I really don't understand your use of index or what "space yy" is? Arrays have positions to fill. Your Arr should also indicate a position ie. Arr(0). Here's a general outline...

For cnt = 0 to totalobs - 1
Array1(cnt) = Application.WorksheetFunction.Index(Arr, 1, 2)
Next cnt

Maybe a bit more info. Dave

 

[RoryA]

Try adding this to your original code:

Redim LogArray(1 to numberObs)