What does the keyword 'New' do in VBA?

Question

In VBA procedures we are constantly meeting the keyword New usually on instantiation of an object reference to an existing object instance. But in some instantiations we use the keyword New while in others we don't for example:

Dim T As Excel.Workbook

Set T = Application.Workbooks(Bk)

In the upper example No.1 the "New" keyword has not been used

Dim fso As FileSystemObject

Set fso = New FileSystemObject

In the upper example No.2 the New keyword is being used

Why that? Keep in mind i'm fresh of the boat in VBA but i will do my best to understand!

In addition to that i also get confused when is used/not-used in declaring an object reference for example:

Dim WS As Worksheet

In the upper example No.1 the "New" keyword has not been used

Dim myClassModule As New cl_ChartEvents

In the upper example No.2 the New keyword is being used

The Microsoft Help just tells me nothing...

Keyword that enables implicit creation of an object. If you use New when declaring the object variable, a new instance of the object is created on first reference to it, so you don't have to use the Set statement to assign the object reference.

Gratz2u

Dear people just a last dust-off for deep understanding

Dim WS as Worksheet

Set WS = Worksheets("Sheet1")

Right here we are creating an object that already existed in order to open MS Excel (lets say for examples sake in "default mode") of course which is Sheet1. Since it exists and the New keyword is out of the question how could we instantiate this object in one line right away?

4 @exantas

Sorry says not enough rep to post pic :-(

Answer 1

When you Dim a variable you are specifying what data type it will hold. At the time of creation, its value is always Nothing until you initialize it. Set T = Application.Workbook(Bk) initializes the T variable to the specific instance of Application.Workbook, in this case 'Bk'.

When you Dim fso as FileSystemObject, you are basically saying that fso will hold, at some point, a FileSystemObject; however its initial value is Nothing until you initialize it using Set fso = New FileSystemObject. The New keyword implies you're creating a new object instead of initializing the variable with an existing object as you do with Set T = Application.Workbook(Bk)

Also note that Set fso = FileSystemObject would be invalid because it doesn't know what instance of FileSystemObject you wish to assign to it. This is why you use the New keyword to create a new instance of FileSystemObject.

As stated before, Dim WS As Worksheet merely tells the compiler that you want variable WS to hold a Worksheet object. Since nothing else is specified, at the point of Dim, WS is set to Nothing

Dim myClassModule As New cl_ChartEvents is equivalent to doing:

Dim myClassModule as cl_ChartEvents
Set myClassModule = New cl_ChartEvents

... except on one line of code instead of two. This differs from Dim WS As Worksheet in that the variable is initialized straight away, i.e. myClassModule is set to a new instance of cl_ChartEvents instead of Nothing.

Hope this helps!

Answer 2

You said: "[We are] meeting the keyword New usually on instantiation of an object reference to an existing object instance". Exactly the opposite is true: New is used when something does not exist yet and you want to create it, well "new".

You can omit the New keyword if something already exists, e.g. the Application object, ActiveWorkbook object and others in VBA, that is everything which was already opened by you when starting Excel.

Dim ... As New ... is a shortcut for

Dim ... As ...
Set ... = New ...

For your last question to create a new worksheet, it's done by

Dim WS As Worksheet
Set WS = Sheets.Add
WS.Name = "My new worksheet"

You cannot use Dim WS as New Worksheet, because Microsoft prevents you from doing so. Even if you specify New in that instruction, the object is still Nothing.