VBA proper use of set

[TopLearner]

Good afternoon there,



I am pretty new to VBA and I am struggling to understand some certain concepts even I have been watching a few videos.



According to the definition of set: “Set”, is a keyword used in VBA programming to assign a reference to an object or cell range which is going to remain fix throughout the program or code in Excel. VBA Set basically helps us in avoiding repetitive input of range we need to select while executing the code. In excel, “Set” is mostly applied on worksheets/cell range in a particular sheet”.



For instance,

Sub SetRnge


Dim Rnge As Range

Set Rnge = Range (“A2:A11”)

Rnge.Select

End Sub

However, I do not understand why other times we do not have to use “set”

Sub AddMultipleSheet2()



Dim sheets_count As Integer

Dim sheet_name As String

Dim i As Integer


sheet_count = Range("A1:A7").Rows.Count


For i = 1 To sheet_count


sheet_name = Sheets("mySheet").Range("A1:A10").Cells(i, 1).Value


If SheetCheck(sheet_name) = False And sheet_name <> "" Then

Worksheets.Add().Name = sheet_name

End If



Next i


End Sub

Why in this case I do not write?



Set sheet_count = Range("A1:A7").Rows.Count





From my first Sub ( SetRnge), could I also write:



Set Rnge = Range (“A2:A11”). Select?



Thanks

Sanchez

 

[Joe4]

You use "Set" when setting an "Object" (Workbooks, Sheets, Ranges, etc are all "Objects").
Here is a link defining what an "Object" is in Excel: Understanding objects, methods, properties, and events (VBA)

You do not use "Set" when setting a value (i.e. a String, Number, etc).

 

[mikerickson]

I think that part of your confusion might be that

Range("A1:A10").Rows.Count

references a work sheet, but since the .Count returns a numeric value, that is what triggers the need (or not need in this case) of Set.

 

[My Aswer Is This]

There are lots of ways to do everything in Excel.

I hardly ever use SET

Here are several ways do this small script.
Now which way is faster I do not know.

Sub Using_Set()
'Modified 10/31/2022  11:34:01 AM  EDT
Dim sn As Worksheet
Set sn = Worksheets("Apple")
sn.Range("A1").Value = "Alpha"
sn.Range("A2").Value = "Bravo"


With Sheets("Apple")
    .[B1].Value = "Charlie"
    .Range("B2").Value = "Delta"
End With

End Sub

 

[TopLearner]

I think that part of your confusion might be that

Range("A1:A10").Rows.Count

references a work sheet, but since the .Count returns a numeric value, that is what triggers the need (or not need in this case) of Set.

Hi Mikerickson,

Thanks for your help.


Does it mean that only when I want to assign an object to a variable I have to use set, and when I am expecting a value I do not have to use set?

Thanks
Sanchez

 

[mikerickson]

Correct.

 

[TopLearner]

Correct.

Thanks