Introduction - Change Another Cell with a VBA UDF
Despite what others say, it is possible to change another cell with a VBA user-defined function. Microsoft even says it’s impossible for a custom function to change another cell’s value, but I’m going to show you a couple UDFs that prove all these people wrong.
There aren’t very many practical applications where you would want to enter a formula in one cell and change the value of a different cell, but people regularly want to do it.
One common example of why people want to do this is to copy a static snapshot of what a cell used to be at a given point in time. In other words, they don’t want their copy of the cell to update when the target cell changes. Yes, this can be done by copying and pasting as values, but some people prefer a custom formula.
Another example of where this can be useful is to copy a value to another cell such that that cell cannot be overwritten. This is actually a pretty cool method. Scroll down to my second example to see how it works!
Another common example is to use a user-defined function to change a color of a cell. I’ll show you how to do this in a future tutorial.
There are also other, umm, less scrupulous applications of this feature. One not-so-nice application is to enter a function hidden somewhere on a spreadsheet that prevents anyone from typing what they want into any other cell. I’ll show you how to do that, too, but don’t blame me when you get beat up for being mean!
Time for three warnings:
- These functions can be quite unstable. They may cause Excel to crash. I tested them in Excel 2010, but your mileage may vary. This approach should work for Excel 2010 and earlier versions.
- Some of the UDF examples I’m about to show you are not practical. They can be accomplished with different functions. I’m just here to show you that it’s possible to change other cells with VBA functions.
- If you thought you knew VBA, what you’re about to see may blow your mind…
Change a Different Cell using an Excel VBA Function
Function CopyCellContents(copyTo As Range) Evaluate "CopyOver(" & Range("A1").Address(False, False) _ & "," & copyTo.Address(False, False) & ")" CopyCellContents = "Copied at " & Now() End Function Private Sub CopyOver(copyFrom As Range, copyTo As Range) copyTo.Value = copyFrom.Value End Sub
This example copies the content of cell
Here’s what I mean. I entered “3.14” in cell A1 and typed
=CopyCellContents(E1) into cell B1. The expectation is that cell E1 will now change to 3.14.
Success. That’s exactly what happened. Now, I can change cell A1 and the value stored in cell E1 will not change.
The timestamp proves it doesn’t change. What we’ve done is recorded a snapshot of what cell “A1” was when we first executed the CopyCellContents function.
Update Another Cell using VBA UDF
Function CopyCellContents(CopyFrom As Range, copyTo As Range) CopyFrom.Parent.Evaluate "CopyOver(" & CopyFrom.Address(False, False) _ & "," & copyTo.Address(False, False) & ")" CopyCellContents = "" End Function Private Sub CopyOver(CopyFrom As Range, copyTo As Range) copyTo.Value = CopyFrom.Value End Sub
This user-defined function is quite similar to the last function, except it accepts 2 arguments:
One thing I like about this UDF is you don’t even see that there’s a formula entered into cell
With Excel Volatility set to false, the default, the formula will recalculate anytime one of the arguments is changed. What this means is that when you change range
You know what else the Excel Volatility setting does for this function? It prevents you from overwriting the value in cell
That’s right! You can use a VBA UDF to prevent overwriting a cell. Try to type anything you want into cell E1 in the above example and the moment you press enter to get out of the cell, the value in cell A1 will automatically pop back into the cell. It’s a self-protected cell!
Change Cell Next to the Formula Cell
Function ChangeAdjacentCell() Evaluate "Adjacent(" & Application.Caller.Offset(0, -1).Address(False, False) & ")" ChangeAdjacentCell = "" End Function Private Sub Adjacent(CellToChange As Range) CellToChange = "Hello!" End Sub
This function changes the value of the cell to the left of wherever your formula is entered. It doesn’t matter where you typed your formula. As long as there’s a cell to the left, that cell will change.
All it does is change the text to “Hello!” You can adapt the macro to your own needs. If you’d rather change multiple cells, you certainly can.
Since we didn’t pass our formula any arguments, it will never be recalculated unless we enter the formula again. If you want your user-defined function to update anytime a cell is changed, add
Application.Volatile to the top of the function, like we do in the next example. If you don’t want your formula to show up on every single sheet, you’ll need to pass your Sub the sheet name, as well.
Prevent User from Typing into Any Cell
Function MeanFunction() Application.Volatile If Application.Caller.Address <> Selection.Address Then Evaluate "NotNice(" & Selection.Address(False, False) & ")" End If MeanFunction = "" End Function Private Sub NotNice(rng1 As Range) rand1 = CInt(Application.WorksheetFunction.RandBetween(0, 4)) Select Case rand1 Case 0 rng1.Value = "It looks like you're struggling..." Case 1 rng1.Value = "You can't do that!" Case 2 rng1.Value = "Not happening." Case 3 rng1.Value = "What are you trying to do?" Case 4 rng1.Value = "Why won't it work?" End Select End Sub
Alright, this one isn’t very nice, but it sure is funny. Copy and paste the above example into a module in your VBA Editor and enter
=MeanFunction() into any cell in an Excel workbook - preferably someone else’s workbook! Because the function returns an empty string and has no arguments, it’s really difficult to find where you put it. That’s what makes it such an effective function. For maximum impact, hide the command way over on the right side of the workbook so it’s even harder to find.
Whenever someone tries typing in another cell, they’ll be greated with one of 5 random taunts. This is what it looks like:
Because we set
Application.Volatile equal to true, the function executes anytime someone tries changing a cell. Whatever cell the person has selected will automatically be overwritten with one of the pre-defined messages.
What makes it even funnier is if your user tries to select all the cells to delete them, look at what happens:
All the cells get filled! This makes a pretty good april fools jokes for school or work, if you’re brave enough to do it. I’m not!
Okay, okay, that’s enough fooling around.
On a serious note, you can modify this example to prevent people from entering data into a certain range. This is good protection if you don’t want to lock your sheet.
Be honest. You thought it was impossible to change another cell using a custom function, didn’t you? Nothing is impossible with VBA, even if Microsoft says it is! In the coming days, I’m going to show you how to use similar methods to change cell colors from a VBA user-defined function. This is another feat often thought to be impossible with a UDF.
Coming Fall 2017
Auto-Import Macros Directly from wellsr.com
Absolutely FREE when you join the waitlist
About Ryan Wells
Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.Follow