Unable to get the log property of the worksheet function class

[SBF12345]

I am receiving an "unable to get the log property of the worksheet function class" error on the line immediately below. I've traced the error back to the construction of "TempSample". When I hover my mouse pointer over counter variables "oo" and "rr" I read "36" which is outside of the range used in constructing the array variables. NumObs reads "35". I am not sure how the counter variable came to read "36" when the loop range is "1 to NumObs (35)". Any ideas? Thanks!

 ArrTemp(nn) = Application.WorksheetFunction.Log(Application.WorksheetFunction.Index(TempSample, nn, 2), Application.WorksheetFunction.Index(TempSample, NumObs, 2))

Dim ii As Integer:              ii = 1  'total sample lengthDim oo As Integer:              oo = 1
Dim nn As Integer:              nn = 1
Dim rr As Integer:              rr = 1
Dim jj As Integer:              jj = 5
Dim gg As Integer:              gg = 1


'XXXXXXXXXXXXXXXXXXXXXXXXXXXX
'X  Log transform X array   X
'XXXXXXXXXXXXXXXXXXXXXXXXXXXX


For oo = 1 To NumObs


ArrX(oo) = Application.WorksheetFunction.Log(Application.WorksheetFunction.Index(X, oo, 2), Application.WorksheetFunction.Index(X, NumObs, 2))


Next oo

'XXXXXXXXXXXXXXXX
'X  Main Script X
'XXXXXXXXXXXXXXXX


For ii = NumObs To A - NumObs
    
        For rr = 1 To NumObs
        
            TempSample(rr, 1) = Application.WorksheetFunction.Index(PriceData, ii + rr, 1) 'Dimension 1 TempSample - Date of Temp Sample
            TempSample(rr, 2) = Application.WorksheetFunction.Index(PriceData, ii + rr, 2) 'Dimension 2 TempSample - price of Temp Sample
            TempSample(rr, 3) = Application.WorksheetFunction.Index(PriceData, ii + rr, 3) 'Dimension 3 TempSample - Volume
    
        Next rr
    
    For nn = 1 To NumObs
    
    ArrTemp(nn) = Application.WorksheetFunction.Log(Application.WorksheetFunction.Index(TempSample, nn, 2), Application.WorksheetFunction.Index(TempSample, NumObs, 2))

 

[NdNoviceHlp]

Add...

Option Base 1

to the top of your code.
HTH. Dave

 

[SBF12345]

The module is set to option base 1 but still gets hung up on this line. It is worth noting that "oo" "rr" and "gg" are all reading "36" when NumObs is set to "35". When I look at ArrX(oo) in the watch window the values are shown to be "subscript out of range". Also, worth noting, the "ii" value is large at the moment the error is produced, and so the loop that "oo" "rr" and "gg" are running in have looped many times successfully. I looked at the data around the row "ii" and am not seeing any irregularities in data. what else could be causing this error?

 

[Fluff]

Whilst I've no idea what the problem is, when looping like you are the counter (ie oo) will always end-up 1 higher than the limit (ie NumObs).
When oo is 35 the loop will run until you get to the Next oo, at which point oo will become 36 and the loop will quit.

 

[Anthony47]

I guess the Log parametres are outside the "legal" value for the Log function (for example a negative number). Add these two trace lines before the failing instruction:

Debug.Print "First: ", Application.WorksheetFunction.Index(TempSample, nn, 2)
Debug.Print "Second: ", Application.WorksheetFunction.Index(TempSample, NumObs, 2)
ArrTemp(nn) = Application.WorksheetFunction.Log(Application.WorksheetFunction.Index(TempSample, nn, 2), Application.WorksheetFunction.Index(TempSample, NumObs, 2))

Then, when you get the run-time error, activate the debugger, type Contr-g to open the vba "Immediate window" and post the last First /Second message you can read.

If you cannot guarantee that the Log parametres be in the allowed ranges we will overcome the error using the following syntax:

ArrTemp(nn) = Application.Log(Application.WorksheetFunction.Index(TempSample, nn, 2), Application.WorksheetFunction.Index(TempSample, NumObs, 2))

In this way, if the Log function fails the instruction will return an Error Value rather than a runtime error (I got "Error 2036" if trying to calculate Log(0,10)); then your code will test if ArrTemp(nn) IsError and decide wich action need to be taken.

Bye

 

[SBF12345]

I stepped through it for a few iterations and I will always receive 36 values for the counters...not a problem.

I added the debug.print lines with an if statement at the iteration giving the error and the code ran through to completion, so...problem solved?

 

[shg]

Those loops would be a lot faster if constructed as

  dDenom = X(NumObs, 2)
  For oo = 1 To NumObs
    ArrX(oo) = Log(X(oo, 2)) / dDenom
  Next oo

 

[SBF12345]

Thanks! I'm always interested in other structures to save time and work

 

[shg]

Oops:

  dDenom = [COLOR="#FF0000"]Log([/COLOR]X(NumObs, 2)[COLOR="#FF0000"])[/COLOR]
  For oo = 1 To NumObs
    ArrX(oo) = Log(X(oo, 2)) / dDenom
  Next oo