Excel: To create a Boolean Counter function that refreshes itself like the Rand() function-CodePudding

I lack the knowledge of VBA so need help to create a function counter that works as follows:

int ctr = 1;

ctr = ctr   (-1)^ctr;

print ctr;

Here I want this function [written in C style above] to run in excel and whenever the sheet is editted or F9 is pressed, it gets refreshed/recalculated automatically just like Rand() function refreshes itself. If such a task can be done without VBA then please enlighten me on that part too. Thank you.

CodePudding user response：

There are two main points in writing a UDF like this

Use Application.Volatile to force the UDF to recalc every sheet recalc
Declarectr as Static so it retains its value between calls

Something like

Function My_Func() 
    Static ctr As Integer
    Application.Volatile 
    ctr = ctr   (-1)^ctr
    My_Func = ctr
End Function

CodePudding user response：

Have you tried to insert a formula in a cell like this: =E38 (-1)^E38 where E38 is a cell selected such as your ctr