Pass function result to cell, not back to sub

[GCW esq]

So I'm startling to think this is impossible.
My question carries on from http://www.excelforum.com/excel-prog...ml#post3870988 - some useful suggestions have been made there, but I'm trying to get the thing to work as follows:
I'm trying to toggle cell values by only using functions and VBA.
I have a function called Tog (a,b,acv) where acv is basically the ActiveCell.Value
I have a sub with a selection change event.
I put =Tog("y","n",acv) into a cell, and when I click on that cell, I want the current function result in the cell to change from "y" to "n", or vice versa the next time I select that cell.
I have come to the point where I can just about get it to work, but I think the value I want passed back to the cell will only go back to the sub which called the function, and not to the cell.
Is there a way to get the function in the cell to update to the new result -- without losing the formula in the cell???

The following Code is in a standard module:

Dim X As New Class1

Function Tog(a, b, acv)
    If acv = "n" Then Tog = "y" Else Tog = "n"
End Function


Sub Auto_Open()
    Set X.App = Application
End Sub

I have the following in a Class Module called Class1:

Public WithEvents App As Application
Private Sub App_SheetSelectionChange()
If Left(ActiveCell.Formula, 4) = "=Tog" Then
   a = Split(ActiveCell.Formula, """")(1)
   b = Split(ActiveCell.Formula, """")(3)
   acv = ActiveCell.Value
   Call Tog(a, b, acv)
End If
End Sub

Pretty simple, and it assigns the needed value to Tog, but the cell doesn't update with the result. It would be a great boon to mankind (and especially to this specimen) if it could be made to work.

[mikerickson]

You could use something like this

Function Toggle(Trigger As Variant) as Boolean
    Toggle = Not (Application.Caller.Text = "TRUE")
End Function

Trigger is just a value to indicate when the value should flop

put =Toggle(A1) in a cell and it will toggle between TRUE and FALSE every time that A1 is changed.

But since you want Y/N not TRUE/FALSE and TOGGLE reads the text in the cell with the formula, IF(TOGGLE(A1), "Y", "N") will always return Y.

So:

Function Toggle2(trigger As Variant, Optional oneValue As Variant = True, Optional twoValue As Variant = False) As Variant
    Dim CurrentValue As String
    CurrentValue = Application.Caller.Cells(1, 1).Text
    If LCase(CurrentValue) = LCase(CStr(oneValue)) Then
        Toggle = twoValue
    Else
        Toggle = oneValue
    End If
End Function

Where =Toggle2(A:A, "Y", "N") will switch between "Y" and "N" every time a cell in column A changes.

Then I got ambitious and thought of more that two values.
So =CYCLE(A:A, "X", "Y", "Z", "W") will cycle through X, Y, Z and W when column A is changed.

Function Cycle(Trigger As Variant, ParamArray CycleValues() As Variant)
    Dim cValues As Variant, i As Long
    Dim CurrentValue As String
    If TypeName(Application.Caller) = "Range" Then
        CurrentValue = Application.Caller.Cells(1, 1).Text
    Else
        CurrentValue = Trigger
    End If
    
    If UBound(CycleValues) < 1 Then
        cValues = Array(True, False)
    Else
        cValues = CycleValues
    End If
    
    Cycle = cValues(0)
    For i = 0 To UBound(cValues) - 1
        If LCase(CurrentValue) = LCase(CStr(cValues(i))) Then
            Cycle = cValues(i + 1)
        End If
    Next i
End Function

If no CycleValues are specified, CYCLE acts like TOGGLE.

How to force it to a value?
=IF(B1=1, "cat", CYCLE(A:A, "X", "Y", "Z"))

if B1 =1 it will return "cat"
otherwise it will cycle through X,Y and Z whenever a cell in A1:A10 is changed.

I hope this helps.

Note that all these functions, except Cycle, must be called by a worksheet formula, otherwise they will error.
The Application.Caller handling in Cycle can be put in the others.

Great care should be taken with UDFs like this to avoid the pitfalls of circular referencing.

[mikerickson]

Re reading the OP, I see that you don't want a function that will toggle based on cell values, but will respond the the user clicking on a cell.
Try this.
Put this UDF in a normal module

Function CycleOnClick(ParamArray CycleValues() As Variant)
    Application.Volatile
    Dim CycleIndex As Long, cValues As Variant
    If UBound(CycleValues) < 1 Then cValues = Array(True, False) Else cValues = CycleValues
    CycleIndex = 0
    On Error Resume Next
    With Application.Caller
        CycleIndex = Evaluate(.Parent.Names(Replace(.Address, "$", "_")).RefersTo)
    End With
    CycleOnClick = cValues(CycleIndex Mod (UBound(cValues) + 1))
End Function

Then put this in the ThisWorkbook code module

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Dim CycleIndex As Long, formStr As String
    With Target
        If LCase(.Formula) Like "=cycleonclick*" Then
            Cancel = True
            On Error Resume Next
            CycleIndex = Evaluate(.Parent.Names(Replace(.Address, "$", "_")).RefersTo)
            On Error GoTo 0
            .Parent.Names.Add(Name:=Replace(.Address, "$", "_"), RefersTo:="=" & CycleIndex + 1).Visible = False
            .Calculate
        End If
    End With
End Sub

Put =CycleOnClick() in a cell, double clicking on that cell will toggle between TRUE and FALSE

=CycleOnClick("a", "b", "c", "d") will cycle through those letters.

One can use formulas as arguments.
=CycleOnClick("Sin=" & Sin(A1), "Cos=" & Cos(A1))

One can put multiple cycles (of differing lengths) in the same cell
=CycleOnClick("A", "B", "C") & CycleOnClick(1, 2, 3, 4)
each double click will advance both of the cycles i.e. A1 > B2 > C3 > A4 > B1 > ... > B3 > C4 > A1 > ...

[GCW esq]

Thanks very much, mikerickson. That's a fun bit of programming. Some clever arithmetic in there, too.
I've learnt quite a bit by going through it, and it's quite close to what I was after.
I just spent most of the day wondering why my code doesn't work. I can see that your code updates the function value when the function ends, but mine doesn't. How can this be??? What has yours got that mine hasn't got???? I've tried everything I can think of to get mine to work, and then some!
P.S. I tried Application.Volatile in the Function, but that didn't do it.

[mikerickson]

A function does some calculation and assigns the result of that calculation to a variable. Somewhere in the OP Tog function, the code needs to assign a value to Tog.

[GCW esq]

It does it here:

If acv = "n" Then Tog = "y" Else Tog = "n"

Will that not do?