How to Set the Cell Value in VBA
number background image by kuhar from Fotolia.com
Visual Basic for Applications, or VBA for short, is the scripting language used in Microsoft Office to automate tasks in applications such as Microsoft Excel.
Since Excel is ultimately used for displaying related data in spreadsheet cells, one of the core features of VBA in Excel is to assign values to those cells after calculations. Not only can you assign values to single cells, but also to ranges, using VBA.
Copy and paste the following code into your VBA module:
Replace the "Sheet1" text with the cell to which you want to assign a value. For example, if you want to assign a value to the cell in column B, row 24, then you'd change the code from "Sheet1" to "B24."
- Visual Basic for Applications, or VBA for short, is the scripting language used in Microsoft Office to automate tasks in applications such as Microsoft Excel.
- Not only can you assign values to single cells, but also to ranges, using VBA.
Replace the 35 with the value you want to assign to your cell. For example you could make it a number, such as 25, or a string of text, such as "Hello."
Call the SetValue subroutine from your module by typing its name:
Press "F5" to run the code and see the cell you typed in the code change to the value you specified.
- The name "SetValue" for the function can be changed to one of your choosing, such as "MyFunction" or "AuntMelinda."
L.P. Klages is an entrepreneur and software developer, concentrating on information theory, software user experience, and mathematical modeling. He has been writing about technology and the business of technology since 1999. His articles have appeared on many sites, including GameDev.net, KenSharpe.net, and eHow. Klages attended Jacksonville University in Jacksonville, Fla.