Log Function Conversion VBA

[SBF12345]

I would like to convert nominal data values into logarithmic values to understand rates of change. The data is in the form of an array and I would like to observe the log values given the first nominal value in the series compared to the current value. What formula can I use to do this if I want to incorporate a first value and a current value? The code I have thus far is below.

The code structure may be misleading. I anticipate using both variables Array1 and Array2 to define a currently undefined Log variable associated with the current value (array2).

'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


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


For yy = 1 To numberObs


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


Next yy


End Function

 

[shg]

Can you show an example of input and expected results as it would appear on a worksheet?

 

[SBF12345]

10/3/18	835.81		10/3/18	1.001396
10/2/18	833.60		10/2/18	1.001002
10/1/18	828.01		10/1/18	1

<tbody>
</tbody>

Sure,

The set of two columns on the left side are input type values and I would like to output values similar to the values in the set of two columns on the right.

The function I put together is below, but I'm getting an error on the LogArray(yy) line

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


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


LogArray = LogPriceData


End Function

 

[shg]

What are the inputs and what are the outputs? What is the calculation to arrive at the result?

 

[SBF12345]

I used the worksheetfunction LOG() to produce the values. The first argument is the current value. The second argument is the base (first cell) written as $A$5

The inputs are taken from the left two columns the output is the right two columns.