How to Change Another Cell with a VBA Function

Introduction
Change a Different Cell using an Excel VBA Function
Update Another Cell using VBA UDF
Change Cell Next to the Formula Cell
Prevent User from Typing into Any Cell
Final Thoughts

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:

1. 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.
2. 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.
3. 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

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

This example copies the content of cell A1 to whatever cell you select in your function via the CopyTo variable. By not passing the source you want to copy to the function, you’re preserving on old copy of cell A1. In other words, because volatility is False by default, you can change A1 in the future and the value in cell CopyTo will not change.

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 CopyCellContents2(CopyFrom As Range, copyTo As Range)
CopyFrom.Parent.Evaluate "CopyOver2(" & CopyFrom.Address(False, False) _
                        & "," & copyTo.Address(False, False) & ")"
CopyCellContents2 = ""
End Function

Private Sub CopyOver2(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: CopyFrom and CopyTo.

One thing I like about this UDF is you don’t even see that there’s a formula entered into cell B1. It’s just quietly doing it’s thing.

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 CopyFrom, the value in cell CopyTo will automatically update.

You know what else the Excel Volatility setting does for this function? It prevents you from overwriting the value in cell CopyTo.

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!

Before

After

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.

Final Thoughts

Be honest. You thought it was impossible to change another cell using a custom function, didn’t you? Nothing is impossible with VBA!

When you’re ready to take your VBA to the next level, subscribe using the form below.