Address ( False, False ) _ & "," & copyTo.
If you thought you knew VBA, what you’re about to see may blow your mind…įunction Cop圜ellContents2 ( CopyFrom As Range, copyTo As Range ) CopyFrom.I’m just here to show you that it’s possible to change other cells with VBA functions. They can be accomplished with different functions. Some of the UDF examples I’m about to show you are not practical.This approach should work for Excel 2010 and earlier versions. I tested them in Excel 2010, but your mileage may vary. These functions can be quite unstable.I’ll show you how to do that, too, but don’t blame me when you get beat up for being mean! 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. There are also other, umm, less scrupulous applications of this feature.
I’ll show you how to do this in a future tutorial. Scroll down to my second example to see how it works!Īnother common example is to use a user-defined function to change a color of a cell. Yes, this can be done by copying and pasting as values, but some people prefer a custom formula.Īnother example of where this can be useful is to copy a value to another cell such that that cell cannot be overwritten. In other words, they don’t want their copy of the cell to update when the target cell changes.
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. 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. 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. Introduction - Change Another Cell with a VBA UDFĭespite what others say, it is possible to change another cell with a VBA user-defined function. Change a Different Cell using an Excel VBA Function
0 kommentar(er)
