Offset in VBA

What is Offset?

The Offset property in Excel VBA takes the range which is a particular number of rows and columns away from a certain range (border below for illustration only). Place a command button on your worksheet and add the following code lines: Dim example As Range.

What is the OFFSET Function?

The OFFSET function is categorized under Lookup and Reference functions. OFFSET will return a range of cells. That is, it will return a specified number of rows and columns from an initial range that was specified.

In financial analysis, we often use Pivots Tables and Charts. The OFFSET function can be used to build a dynamic named range for pivot tables or charts to make sure that the source data is always up to date.

 

Formula

OFFSET(reference, rows, cols, [height], [width])

 

The OFFSET function uses the following arguments:

  1. Reference (required argument) – It is the cell range that is to be offset. It can be either single cell or multiple cells
  2. Rows (required argument) – It is the number of rows from the start (upper left) of the supplied reference, to the start of the returned range.
  3. Cols (required argument) – It is the number of columns from the start (upper left) of the supplied reference, to the start of the returned range.
  4. Height (optional argument) – It specifies the height of the returned range. If omitted, the returned range is the same height as the supplied refe
  5. Width (optional argument) – It specifies the width of the returned range. If omitted, the returned range is the same width as the supplied reference.

In computer science, an offset within an array or other data structure object is an integer indicating the distance (displacement) between the beginning of the object and a given element or point, presumably within the same object. The concept of a distance is valid only if all elements of the object are of the same size (typically given in bytes or words).

For example, in A as an array of characters containing "abcdef", the fourth element containing the character 'd' has an offset of three from the start of A.

In computer engineering and low-level programming (such as assembly language), an offset usually denotes the number of address locations added to a base address in order to get to a specific absolute address. In this (original) meaning of offset, only the basic address unit, usually the 8-bit byte, is used to specify the offset’s size. In this context an offset is sometimes called a relative address.

In computer science, offset describes the location of a piece of data in relation to another location. For example, when a program is accessing an array of bytes, the fifth byte is offset from the beginning of the array by four bytes. If the array contained data that is meant to be read 32 bits at a time, element n would be offset from the start of the array by 4 * (n – 1) bytes.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s