alternative way for triggering event procedures from EXCEL VBA User Defined Functions

[testor2897]

Hi, sorry for using issues as I found no other direct contact possibility. I read the article with a lot of interest (https://rubberduckvba.wordpress.com/2021/03/19/globals-and-ambient-context/), Thanks a lot. There is an alternative way for triggering event procedures from Excel VBA User Defined Functions (relating to your article).

1. You start a seldom used key event procedure in Sub Auto_Open and declare a global variable (for triggering) like:

' variable to store the Application.Caller.Address (inkl. Workbook and Worksheet)
Public actKeyAddress$

Sub Auto_Open()
  actKeyAddress = ""
  Application.OnKey "^{F15}", "ColorCell"
End Sub

2. You define a procedure to do something asynchron (in this case reformat the Application.Caller cell):

  Sub ColorCell()
  If actKeyAddress = "" Then Exit Sub
  
  On Error Goto FinalEnd
  With Range(actKeyAddress)
    .ClearFormats
    .HorizontalAlignment = xlCenter
  With .Interior
    .Pattern = xlPatternRectangularGradient
    .Gradient.RectangleLeft = 0.5
    .Gradient.RectangleRight = 0.5
    .Gradient.RectangleTop = 0.5
    .Gradient.RectangleBottom = 0.5
    .Gradient.ColorStops.Clear
  End With
  With .Interior.Gradient.ColorStops.Add(0)
    .Color = 6750207
  End With
  With .Interior.Gradient.ColorStops.Add(1)
    .ThemeColor = xlThemeColorDark1
  End With
  End With
  With Range(actKeyAddress).Font
    .Name = "Webdings"
    .Size = 12
    .Bold = True
    .Color = -65536
  End With

FinalEnd:
  ' important: reset triggering variable!
  actKeyAddress = ""
End Sub

3. You fire the event from within an UDF:

 Function myFunction( ... ) 
  Dim actWorkbook$
  Dim actWorksheet$
  Dim actCell$
...
  With Application.Caller
    actCell = .Address
    actWorksheet = .Parent.Name
    actWorkbook = .Parent.Parent.Name
  End With

  actKeyAddress = "[" & actWorkbook & "]" & actWorksheet & "!" & actCell
  SendKeys "^{F15}"
  ...
End Function

Greetings,

Sebastian