Add a counter to a VBA function

Question

I am currently working on a sheet where I would like to know how many times does a cell change in value. I have the following code:

Function Bidcalcul(Bid As Variant, Intervmin As Variant, Intervmax As Variant, Ticksize As Variant, 
Percentage As Variant) As Variant

Dim rowcompteur As Integer
Dim valeurinitial As Variant

valeurinitial = ActiveCell.Value
rowcompteur = ActiveCell.Row

If IsError(Bid) Then
    Bidcalcul = WorksheetFunction.Floor(Bid * Percentage, Ticksize)
End If

If Intervmin <= (Bid - valeurinitial) And (Bid - valeurinitial) <= Intervmax Then
    Bidcalcul = valeurinitial
Else
    Bidcalcul = WorksheetFunction.Floor(Bid * Percentage, Ticksize)
    **Call Compteur(rowcompteur, 23)**
    
End If

End Function

Private Sub Compteur(rowcompteur As Integer, column As Integer)

Cells(rowcompteur, column).Value = Cells(rowcompteur, column).Value + 1

End Sub

But when calling the function Compteur it doesn't seems to work. Do you have any idea on how I could do it? (I've already tried with a simple formula on excel but since I retrieve my values from Bloomberg it doesn't work)

Thanks!

Is `Bidcalcul` a UDF (user defined function)? Means: Is it used like a formula in a cell? If yes, this is not possible. UDF are not allowed to change other cells values. — Pᴇʜ, May 31 '21 at 14:58
You can use a global variable to count in the function and a sub to output the value of the global variable. — Pᴇʜ, May 31 '21 at 15:19
Using `ActiveCell` in a UDF is likely to give you unexpected results - if you want to refer to the cell with the formula then use `Application.Caller` or `Application.ThisCell` — Tim Williams, May 31 '21 at 18:04

score 0 · Answer 1 · answered Jun 01 '21 at 06:21

Since UDF (user defined functions) do not allow changing other cell's values your attempt does not work.

As a workaround you can use a global/public variable Public Compteur As Long to count in your UDF Bidcalcul. And another volatile function Public Function GetCompteur() As Long to output that variable.

Option Explicit

Public Compteur(1 To 1048576) As Long

Public Function Bidcalcul(ByVal Bid As Variant, ByVal IntervMin As Variant, ByVal IntervMax As Variant, ByVal Ticksize As Variant, ByVal Percentage As Variant) As Variant
    Dim ValeurInitial As Variant
    ValeurInitial = Application.ThisCell.Value
    
    Dim RowCompteur As Long  ' row counting has to be long there are more rows than integer can handle!
    RowCompteur = Application.ThisCell.Row  ' don't use ActiveCell!
    
    If IsError(Bid) Then
        Bidcalcul = Application.WorksheetFunction.Floor(Bid * Percentage, Ticksize)
    End If
    
    If IntervMin <= (Bid - ValeurInitial) And (Bid - ValeurInitial) <= IntervMax Then
        Bidcalcul = ValeurInitial
    Else
        Bidcalcul = Application.WorksheetFunction.Floor(Bid * Percentage, Ticksize)
        Compteur(RowCompteur) = Compteur(RowCompteur) + 1
    End If
End Function


Public Function GetCompteur() As Long
    Application.Volatile
    GetCompteur = Compteur(Application.ThisCell.Row)
End Function

Note that you should not use ActiveCell in a UDF because the active cell is not the cell the formula is written in. This is Application.ThisCell but any other cell on the worksheet could be the ActiveCell, so you might get wrong results!

Also your row counting variables need to be of type Long because Excel has more rows than Integer can handle. Actually I recommend always to use Long instead of Integer as there is no benefit in using Integer in VBA.

But your entire approach has one big issue

With ValeurInitial = Application.ThisCell.Value you reference to the same cell your formula is written in. So this genereates a circular reference and that is where you run into a problem. Because now if the formula calculates it's result changes and this changes thi initial value which would the formula need to calculate again and again.

Yes, this did not happen when you used ActiveCell.Value but that definitely gave you a wrong result. So you might need to re-think your approach or how you deal with the circular reference.

My recommendation to get out of this issue

Don't use UDFs for calculating this. Use a command button to launch a calculation procedure Sub and read your raw data into an array (for fast calculation) let your procedure do all the work and output the result in an output array that you can easily write into your sheet then.

This way you don't have to deal with circular references and you can easily count your iteration steps of your calulation. It gives you much more control over the entire process.

score -1 · Answer 2 · answered May 31 '21 at 16:44

-1

Your code looks good and it works on my excel. You could also try to write these Debug.Print instructions to see the values.

Debug.Print "Before: " & Cells(rowcompteur, column).Value
Cells(rowcompteur, column).Value = Cells(rowcompteur, column).Value + 1
Debug.Print "After: " & Cells(rowcompteur, column).Value

it works!

answered May 31 '21 at 16:44

VB6

34
1

Yes, but that does not work in a UDF (user defined function) and that is the problem the OP has: He uses `Bidcalcul` as a UDF and from there he calls the procedure `Compteur` which then runs under the scope of the UDF and in UDFs changing cell values is not permited (does not work). – Pᴇʜ Jun 01 '21 at 05:57

Add a counter to a VBA function

2 Answers2

But your entire approach has one big issue

My recommendation to get out of this issue