Tuesday, May 15, 2012

EXCEL VBA - Using SET in the VBA Program

For a long time, when I wrote a VBA program, I used the syntax such as Sheets(“Sheet1”), . It made my program long, not easy to read and hard to make changes.

Although “Find and Replace” function is a good way to make change. Still, it gave me the headache when I reopen my VBA program and try to modify it to use for a new task.

 Recently, I find SET statement is a very handy One. I wrote:

Dim SH1 As WorkSheet

Set SH1=Sheets(“Sheet1”)

at the beginning part of my program. It shortened my program, make it easy to read, find the mistake and modify for other purpose.

SET can also used to define other objects (Workbook, Range) in the VBA.

For example,

Dim myRange as Range
Set myRange = Range("A1:A10")

© Copyright Exceltipsandkeys All Rights Reserved.

No comments:

Post a Comment