212-828-7089
Excel on YouTubeExcel Class NYC Faceook PageExcel Class on LinkedInExcel Class on TwitterExcel Class on YelpExcel on BloggerExcel on WordpressGoogle Places

Excel UDF Calls A Macro To Change A Value In Another Cell

Excel UDF Calls A Macro To Change A Value In Another Cell

Wishful Thinking: Update The Website With Accurate Information

Update: November 23rd, 2020: Instead of acknowledging the problem and fixing the incorrect information, in typical Microsoft fashion, the link below on Microsoft’s website now produces a 404 error page, which is why I created a PDF as proof the page existed. It’d be nice if they would present accurate information on their website instead of hiding their mistakes!
It’s pretty bad when an ‘authoritative’ source publishes bad information, yet it happens. Worse yet, when they won’t admit their mistake. Of course, I have shown proof of this error yet engineers will debate that milk is white if given the chance. Today I would like to correct the fallacy posited by Microsoft at this URL: https://support.microsoft.com/en-gb/help/170787/description-of-limitations-of-custom-functions-in-excel

Original URL: https://support.microsoft.com/en-gb/help/170787/description-of-limitations-of-custom-functions-in-excel

Since someone will likely remove the offending content, I have captured it to this PDF for your reference: https://excelclasstraining.com/Description_of_limitations_of_custom_functions_in_Excel.pdf

In an VBA module, add this code:

Option Explicit
Public Sub ADD1()
ActiveCell.Offset(0, 1) = ActiveCell.Offset(0, 1) + 1
End SubPublic Sub ADD2()
ActiveCell.Offset(0, 1) = ActiveCell.Offset(0, 1) + 2
End Sub
Public Function CallMacro(MacroName)
Application.Run MacroName
End Function

In ThisWorkbook, add this code:

Option Explicit
Option Compare Text
Dim MacroName As StringPrivate Sub Workbook_SheetCalculate(ByVal Sh As Object)
MacroName = ActiveCell.Formula
Select Case MacroName
Case “=CallMacro(“”ADD1″”)”
Call ADD1
Case “=CallMacro(“”ADD2″”)”
Call ADD2
End Select
End Sub

In an empty cell, the formula is =CallMacro(“ADD1”)
Hence, a UDF can call a macro, and a UDF can change a value in a different cell. Where the author’s website says “CANNOT” perhaps the correct words are “SHOULD NOT”