Here’s an example sub routine using a For-Next loop to calculate the sum of the first positive 100 numbers: Sub GetSum() Dim sum as Double Dim count As Long Sum = 0 For count = 1 to 100 sum = sum + count Next count MsgBox sum End Sub Do-While LoopĪ Do-While loop will perform a loop as long as the condition at the beginning of the loop is true. The statements between the For and Next statements are repeated in the loop. In a For-Next loop, looping is controlled by a counter which is incremented by the Next statement. The most straightforward kind of loop is the For-Next loop. The most popular types of loops in VBA are: For-Next Loop Clear deletes the content in a given range while Delete shift cells around to replace deleted cells. Other popular methods besides Select, include Clear and Delete. Now that you know the Range object, here’s an example of how you can use the Select method on the Range object to copy and paste data: Sub CopyRange() Range("A1:B10").Select Selection.Copy Range("Z1").PasteSpecial xlValues End Sub Use Cells property to refer to a range of cells from A1 to F20: The following example refers to the cell D3 on Sheet3 : You can also refer to a range using the Cells object, which takes two arguments (a row number and column number). Refer to a range in a different workbook: Refer to a range outside of the active sheet: Once you define a range, you can use the Select method to select the range of cells. A Range object can be as tiny as a single cell or be as large 10,293,289,228 cells in a worksheet. To reference data on your Excel spreadsheet, you can use the Range object, which represents a range of data. Once you declare variables, you can assign values to those variables: Sub Variable_Example() x = 6 y = 3 z = x + 2 * y MsgBox z 'Outputs 12 End Sub You can also declare several variables with a single Dim statement: Dim x as Integer, y as Integer, z as Integer Here are some examples of how you declare variables: Dim YourName as String Dim x as Integer Dim NetRevenue as Double Dim RowNumber as Long Dim Today as Date Dim SampleValueĭim is an old programming term that stands for dimension, which means that you are assigning a memory location for a specific variable. The most common way to declare variables is to use the Dim statement. Now that you know the advantages of declaring variables, let’s put it to practice.
#What is visual basic for excel code
When you use this statement, you will not be able to run your code if it contains undeclared variable types. To force yourself to declare all variables you use, add the following statement to the top of your VBA module (outside all sub routines): If you let VBA handle data types, you’ll be sacrificing speed and memory. If you don’t set variable types, VBA will use the default data type: Variant. It’s good practice to declare the types of your variables in Excel because your code will run faster and use memory more efficiently. ThemeFont = xlThemeFontMinor End WithĪnd here is what the entire macro looks like after deleting unrelated code: Sub recorder_example() Range("A1").Select ActiveCell.Formula = "John Doe" Range("A2").Select ActiveCell.Formula = "=NOW()" Range("A1:A2").Select = True = 22 Selection.HorizontalAlignment = xlCenter End Sub Here is the code generated to change the font size to point 22: With Selection.Font. The Macro Recorder can be excessive, and it would be a good idea to simplify this macro by deleting irrelevant code. For example, although you changed the font size to point 22, Excel created code that set many other font-related properties. You may be surprised by the amount of code that’s generated by simple commands from the Macro Recorder. Once you’ve recorded your macro, you can view the code that you generated: Choose Developer tab ->Code sub-tab -> Macros -> Select your macro name -> Step Into