Fresh Excel Tips

In this article, we will be discussing how to create a new Class Module in VBA and add our object properties and methods. First, for those of you not familiar with object-oriented lingo, an object is a code construct comprised of properties and methods. Properties are variables that describe the object in some way; [...]

Excel exposes its features and functionality through objects. Excel’s object model, as it’s called, includes nearly 200 objects that represent everything from cell ranges to charts to worksheets, workbooks, and the Excel application. Each object has properties that allow access and control over its attributes, as well as methods that expose the object’s functionality. When [...]

Below is a summary of the most commonly used VBA Built-n Functions for your general reference. A full list of these functions can be found at the Visual Basic Language Reference in the VBA help guide. There’s a section in the language reference entitled “Functions,” which includes an alphabetically organized list of built-in functions. [...]

Today, we will be discussing how to use and define loops in Excel VBA. Below is an example of a basic For loop. For i = 1 To n ' Code goes here Next i For loops are sometimes called For Next loops because you use the Next statement to bound the code over [...]

Below, we will illustrate a few examples on how to use IF/then/Else statements: If (time = 32000) then ' Statements go here End If If (MyCondition = True) Then ' Statements Else ' More statements End If If (count < 10) Then ' Statements ElseIf (count < 20) Then ' Statements ElseIf [...]

For this article, we will be looking at how to use and define arrays in Excel VBA. You declare arrays just as you do variables, except you specify the size of the array in the declaration as shown in the example below. Dim y(1 To 4) As Double Dim x(4) As Double Dim M(1 To 8, 1 [...]

In this article, we will be discussing how to define and use constants in Excel VBA. VBA constants are declared using the syntax Const Name As DataType = Value, as shown in the example below: Const ThisConstant As Integer = 7 Public ThisConstant2 As Double = 3.189 Just as with [...]

For this article, we are going to discuss how to use the proper syntax for declaring variables in Excel VBA. To do this, we will need to use the Dim statement. as shown in the example below: Dim apple As Double Dim basket As Integer Dim statements are of the general form Dim VariableName As DataType, where [...]

Today, we will be looking at various VBA Data types and how to use them in your VBA Code. Visual Basic supports the usual data types you’d expect to see in any programming language. The table below shows some of the VBA data types that are used most often when programming. There are other data types, [...]

Excel’s macro recorder is a useful tool that “records” user actions for “playback at a later time”. The main advantage of using a macro recorder in Excel is that it allows a user to easily perform complex operations over and over that may not be feasible without custom computer programming / scripting. It’s usage can [...]