How to use the SET statement
The SET statement allows you to save an object reference to a variable, the image above demonstrates a macro that assigns a range reference to a range object.
For example, the DIM statement (among others) only declare a variable but it will be empty, nothing is referred until you assign an object using the SET statement.
What is an Excel object? It can be a chart, worksheet, a cell range or a shape among other things basically. Each object has usually a number of properties that you may change, for example, a worksheet may be hidden by changing the visible property to False.
The following macro is used in the above example.
'Name macro Sub Macro1() 'Declare variable Dim rng As Range 'Assign object Set rng = Worksheets("Sheet1").Range("B2:C9") 'Show object address MsgBox rng.Address End Sub
Excel Statement Syntax
Set objectvar = {[ New ] objectexpression | Nothing }
Syntax Explained
Excel Name | Description |
objectvar | The name of the object variable. |
New | Optional. Is used to create an object. |
objectexpression | A name of an object or another object variable with the same object type. |
Nothing | Deletes object. Recommended if an object holds a lot of memory that you don't need anymore. |
Comments
How can I create a new worksheet and assign it to an object variable?
The following macro creates a new worksheet and then shows the worksheet name using the assigned object.
Sub Macro2() Set obj = Sheets.Add MsgBox obj.Name End SubMore than 1300 Excel formulas
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form