Introduction | Example | Tutorial | Applications | Comments
Introduction - Offset VBA
This tutorial shows you how the Offset VBA property is used to navigate Excel. In VBA, offset allows you to access cells relative to your target cell. For example, you can select a cell and change or grab the value of a cell that’s a couple rows or columns away from the cell you have selected.
The Offset property in VBA is similar to the
=OFFSET() function in Excel, except the VBA property is more powerful! I say that because, unlike the OFFSET() function, the offset VBA property doesn’t limit you to just grabbing the value of a nearby cell. You can also change the contents of that nearby cell! I’ll demonstrate why I think this is so important later in this tutorial.
Example - Offset VBA
Sub OffsetDemo() Range("A1").Select Selection = "This is cell " & Selection.Address Selection.Offset(1, 0).Select Selection = "This is cell " & Selection.Address End Sub
Tutorial - Offset VBA
This is a basic example. All it does is select cell
Kind of boring, eh? Let’s dive deeper into the Offset property to add a little excitement.
The Offset VBA property is often referred to as the Range.Offset Property. It accepts 2 arguments:
RowOffset is the number of rows you want to move up or down. It can be a positive integer (down), negative integer (up), or 0.
ColumnOffset is the number of columns you want to move left or right. It can also be a positive integer (right), negative integer (left), or 0.
Before we go any further, I want to point out a good practice to make your macros run quicker.
If you’re using the offset property to change the cell/range you have selected in Excel, like we did in our example, get in the habit of placing Application.ScreenUpdating = False at the top of your macro. If you don’t use this command, each time the selected cell changes, your screen will update. Eventually, your screen may freeze on you, but even if it doesn’t freeze, your macro run-time will be much longer than if you added the command to the top of your macro.
Bottom line, add the command to the top of your macros to increase speed.
Offset in a Loop
My favorite thing to do with the Offset VBA property is to stick it in a For Loop.
Sub OffsetDemoLoop() Application.ScreenUpdating = False Range("A1").Select For i = 0 To 5 Selection = "Row " & Selection.Row Selection.Offset(2, 1).Select Next i Application.ScreenUpdating = True End Sub
Each time the macro iterates through the loop, the selected cell is moved two cells down and one cell to the right.
Offset Active Cell
You don’t have to use offset to select the cell. The same macro works if you just want to refer to the active cell. This may make your macros run faster.
Sub OffsetDemoLoop() Application.ScreenUpdating = False Range("A1").Activate For i = 0 To 5 ActiveCell = "Row " & ActiveCell.Row ActiveCell.Offset(2, 1).Activate Next i Application.ScreenUpdating = True End Sub
Offset a Range
So far we’ve only used the Offset in VBA to move a single cell. This example shows you how to use Offset for an entire range:
Sub OffsetRange() Range("A1:B2").Select Selection.Offset(2, 2).Select End Sub
In this example, the values in range
Read Contents of Offset Cell
In all the examples above, we used Offset to navigate the Excel spreadsheet. This example will by slightly different because we’ll never actually change the selected cell.
Sub OffsetRangeValue() Range("A1").Select str1 = Selection.Offset(0, 1) MsgBox str1 End Sub
Change Contents of Offset Cell
This time, we’ll change the value of an offset cell without selecting that cell.
Sub OffsetVBAmap() Range("A1").Select Selection.Offset(2, 1) = "Hey!" End Sub
Like magic, cell
VBA Offset Map
If you’re wondering how I made the VBA offset map in my header image, here’s a bonus macro for you:
Sub OffsetVBAmap() Range("A1").Select For i = 0 To 8 For j = 0 To 8 If Len(CStr(Selection.Row - 5)) = 1 Then str1 = " " & CStr(Selection.Row - 5) Else str1 = CStr(Selection.Row - 5) End If If Len(CStr(Selection.Column - 5)) = 1 Then str2 = " " & CStr(Selection.Column - 5) Else str2 = CStr(Selection.Column - 5) End If Selection = "(" & str1 & "," & str2 & ")" Selection.Offset(1, 0).Select Next j Selection.Offset(-9, 1).Select Next i End Sub
The resulting map tells you what arguments to enter into the offset command to access cells relative to cell
Offset VBA Map
The power of the Offset VBA command doesn’t come when you select a cell in your macro, like I did in these examples. The power comes when you search through your data for a number or string, then offset relative to that cell once you find it.
I primarily use the Offset VBA property to write scripts in other programming languages. For example, I’m not a Bash or Awk expert. It would take me a while to write a functional Bash or Awk script that loops through a bunch of files.
Instead of wasting time trying to write a short, clean script in these languages, I’ll use VBA to write hundreds of simple Bash or Awk command lines and copy and paste the commands from Excel into Unix or Linux. It’s certainly not practical, but it gets the job done!
Most people try to deter VBA programmers from using the offset command, because it’s slow, clunky, and often unnecessary. As I’ve been saying since my first post over one year ago, I’m not “most people.” Form follows function. As long as your code is functional and meets your intended purpose, who am I to deter you? Most VBA projects just aren’t resource-intensive enough to warrant optimizing. Do what you’ve gotta do!
Coming Fall 2017
Auto-Import Macros Directly from wellsr.com
Absolutely FREE when you join the waitlist
About Ryan Wells
Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.Follow