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

Excel User Defined Function (UDF) Calls A Macro To Change A Value In Another Cell

Excel User Defined Function (UDF) Calls A Macro To Change A Value In Another Cell

According to Excel’s ‘rules’ a UDF should not be allowed to change values in other cells.

If you are like me, you might hate it when someone says “It can’t be done”. I tend to follow a contrarian viewpoint, and I am not a big fan of ‘rules’. I would like to believe that with enough effort challenges can be overcome if you refuse to accept “no” as an answer.

Let me preface this blog post by mentioning that I have plenty of respect for Chip Pearson (http://cpearson.com), and Ozgrid (https://ozgrid.com) – experts who may have said it can’t be done*. It is likely they read this (outdated) link on Microsoft’s website: https://support.microsoft.com/en-us/help/170787/description-of-limitations-of-custom-functions-in-excel. It is also likely many will read the ‘rules’ and fail to question them.

This blog post demonstrates an Excel user defined function that will call any macro. The macro we call will change a value in a cell outside the cell called by the function as well as change the cell’s fill color (for good measure, bending more rules). For those who wonder why a UDF should call a macro, there are applications with both scenarios and data tables. I was happy to see the CallMacro UDF work correctly as an argument nested within an If function.

What I show in this example may not be considered best-practice, but it does work. However at times this recursive function will cause Excel to crash. Maybe it’s a new glitch in Excel that has been exploited.


DOWNLOAD A MACRO ENABLED WORKBOOK

Since my website does not allow non-US visitors, I list my steps for reference for those who cannot download my macro-enabled demonstration workbook.

Step 1: Create a new workbook and save it as macro enabled.

Step 2: In the VB Editor, select the Sheet1 object and change the General object to the Worksheet object.

Step 3: Locate the Worksheet object’s SelectionChange event.

Step 4: Add the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range(“C1”)) Is Nothing Then
CallMacro NewName
End If
End Sub

Step 5: Insert a module

Step 6: Below the Option Explicit in your global declarations, add the following code:

Public Name As String
Public NewName As String
Dim r As Range

Step 7: Create the following UDF:

Public Function CallMacro(ByVal Name As String) As String

‘Ignore errors, in my code you will likely find a few
On Error Resume Next

‘The cells change event uses NewName
NewName = Name

Set r = Range(“C1”)
If Not IsError(Range(“C1”)) Then
If NewName = “ChangeA1” Then
Call ChangeA1
Else
Call ChangeB1
End If
End If
End Function

Step 8: Create the following procedure:

Public Sub ChangeA1()
Set r = Range(“C1”)
r.Offset(0, -2) = r.Offset(0, -2) + 1
r.Offset(0, -2).Interior.Color = 255
End Sub

Step 9: Copy the Step A code and modify bolded items

Public Sub ChangeB1()
Set r = Range(“C1”)
r.Offset(0, –1) = r.Offset(0, –1) + 1
r.Offset(0, –1).Interior.Color = 5287936
End Sub

Step 10: Change Excel’s Calculation Options. Select the File tab > Options command. In the Formulas category:
Enable Iterative Calculations
Increase the Maximum Iterations: 32,767
Maximum Change: 1
Close the options dialog.

Step 11: Save your workbook as macro enabled.

Step 12: Run/Test the macro
Choose the Debug menu > Compile command to run a syntax check.
In cell C1 create a formula that calls either ChangeA1 or ChangeB1. The syntax to the UDF is
=CallMacro(“ChangeA1”)
In cell D1 I type the number 2
In cell C1 the formula could read:
=IF(D1>1,CallMacro(“ChangeA1”),CallMacro(“ChangeB1”))

I encourage others to test my results and email me feedback.

* Footnotes:

Chip Pearson on UDFs: http://www.cpearson.com/Excel/WritingFunctionsInVBA.aspx

“A UDF can only return a value to the cell(s) whence it was called — it must not modify the contents or formatting of any cell and must not modify the operating environment of Excel. If you attempt to change anything, the function will terminate immediately and return a #VALUE error to the calling cell”

Ozgrid on UDFs: https://www.ozgrid.com/VBA/Functions.htm#UDFIntro

“They cannot be used to try and change any part of another cell in any way at all. This means a UDF cannot place a value into any other cell except the cell housing the UDF”