Excel VBA offers many different ways to refer to ranges in a worksheet. One very popular way is to use the Offset property of the Range object.

The Offset property allows you to move around a worksheet with ease using ranges relative to your current position, or to a specific range.

The Offset property takes two arguments. These relate to the upper left-hand corner of the specified Range object. The first argument is the number of rows to offset, and the second is the number of columns.

The arguments can be positive to move down and to the right, or negative to move up and to the left. For example, the following selects cell C3.

Range(“C2”).Offset(1,0).Select

The Offset property is probably more commonly used with the ActiveCell to move relative to the currently selected cell. The following example is a little more complex. It selects the cell one row above and two columns to the right of the current cell.

ActiveCell.Offset(-1,2).Select

If the currently selected cell was C2, then cell E1 would be selected. If cell C1 was the selected cell then an error message would be returned as it cannot find a Range object that does not exist (there are no rows above row 1).

The example below shows the Offset property being used to select a range of cells 4 rows high and 4 columns wide from the current cell.

Range(ActiveCell,ActiveCell.Offset(4,4)).Select

The Offset property is extremely useful when used in loops and when working with variables. The example below demonstrates a Do While loop being used to enter the numbers 1 to 20 in a column starting from the active cell.

Dim x As Integer
x = 1
Do While x <= 20
   ActiveCell.Value = x
   ActiveCell.Offset(1, 0).Select
   x = x + 1
Loop

As you can see the Offset property can be used in a variety of scenarios. It is the most popular method of moving relatively around a worksheet. In fact if you ever record a macro in Excel with the relative references command turned on, the Offset property is used.

This post serves as an introduction to its syntax and use. Go forth and experiment with some macros.

The OnTime method of the Application object in Excel VBA occurs at a specific time of the day.

It can be used to schedule procedures to be run at specified times of the day, or at specific time intervals.

[Read more…] about Use the OnTime Method in Excel VBA

I was asked a few days ago if it is possible to lookup a picture in Excel. The scenario was that they wanted to be able to type in a code, and for Excel to return the picture of that product.

I created a simple picture lookup spreadsheet with a small amount of VBA. A button is used to run the macro that performs the picture lookup.

Download the picture lookup spreadsheet.

[Read more…] about Lookup a Picture in Excel

This post contains the download of a stock control spreadsheet. The spreadsheet is used to manage the level of sweets in stock. As more sweets are ordered, and sweets are sold the spreadsheet keeps a record of the stock remaining.

The spreadsheet is unprotected making the formulas and VBA used available to see. The spreadsheet includes the following Excel techniques;

• VBA code to perform much of the functionality
• A command buttons to initiate the macro to deduct the number of sweets sold, or add the number of sweets ordered
• A table to create a dynamic range for future orders and sales. A Total Row has also been used to sum values
• Vlookup functions to retrieve the sweet names from the stock sheet after the product code has been entered

Download the Excel Stock Control Sheet.