9

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 :-(

WGS
  • 13,969
  • 4
  • 48
  • 51
Codo
  • 271
  • 3
  • 10
  • 24
  • possible duplicate of [What is the reason for not instantiating an object at the time of declaration?](http://stackoverflow.com/questions/8114684/what-is-the-reason-for-not-instantiating-an-object-at-the-time-of-declaration) –  Feb 10 '14 at 08:42

2 Answers2

18

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!

nagyben
  • 938
  • 1
  • 10
  • 19
  • Sir i know that when i reference an object variable it is nothing and when i instantiate it becomes something. We can see that when we hover the mouse over in VBE but really what do you mean when you say the New keyword implies (which means suggest but not specifically) that you're creating a new object. When you are saying i am initializing the variable do you mean the Bk so it is in this situation that the New keyword isn't needed? Could you please amplify a bit? – Codo Feb 08 '14 at 23:06
  • Alright i thing i am getting it...well then why do we use 2 lines instead of 1 and instantiate right away? To save memory right? Could you confirm this as a yes or no? – Codo Feb 08 '14 at 23:14
  • 1
    So if it is a Worksheet since it is always instantiated we dont use a New keyword, and to be ALREADY instantiated we need to declared it with a specific instance like Dim wks as Application.Worksheet("Sheet1") Right? Please confirm so the question is solved and i can give you the question – Codo Feb 08 '14 at 23:17
  • However if we create an object that didn't existed either with 2 lines or with one we need the New keyword right? – Codo Feb 08 '14 at 23:18
  • Ok let me answer your further questions one by one: 2 lines instead of 1: It depends on what you're trying to accomplish with the code. It is considered good practice to define all of your variables at the top of the code so if someone else is looking at it they can see all the variables you are using. Initializing them can happen as and when you need it e.g. if I don't use the variable until line 200, then I don't need to initialize until then - but again this depends on what you are trying to do! – nagyben Feb 08 '14 at 23:24
  • I also did a small edit asking one last question that popped out on this issue. – Codo Feb 08 '14 at 23:27
  • Exactly as you say, since Application.Worksheet("Sheet1") is already a variable in memory, you don't create a variable by using `Dim wks As New Application.Worksheet("Sheet1")` since you're not creating a new object, you are using an existing one – nagyben Feb 08 '14 at 23:29
  • Take a look at this MSDN article, it might help to understand a bit: http://msdn.microsoft.com/en-us/library/77s47661.aspx – nagyben Feb 08 '14 at 23:31
  • It says it does not exist i put the pic on edit up on the question – Codo Feb 08 '14 at 23:33
  • Just a small nit: In VBA the value of a declared but unset variable depends on type. Not all types are initialized to `Nothing`. – andy holaday Feb 08 '14 at 23:37
  • To create a variable and initialize it straight away, you need to have `Option Explicit Off` and then you can do `wks = Application.Worksheets("Sheet1")` – nagyben Feb 08 '14 at 23:38
  • @andy-holaday: That's interesting, I never knew that – nagyben Feb 08 '14 at 23:40
  • 1
    Yep. Objects will be `Nothing`, but numerics will be 0, string will be "" (a zero-length string), boolean will be false. That said, it's good form to code as though you don't know this and explicitly initialize variables. – andy holaday Feb 08 '14 at 23:47
  • That is interesting indeed i just tested it on VBE on hovering the mouse on VBE while running. – Codo Feb 09 '14 at 00:53
  • 1
    See here also for discussion on this: http://stackoverflow.com/questions/8489507/is-the-poor-performance-of-excel-vba-auto-instancing-a-myth – Tim Williams Feb 09 '14 at 04:13
5

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.

Thomas Weller
  • 55,411
  • 20
  • 125
  • 222
  • 1
    Wow jesus! You mean that instead of Dim...As... PLUS Set...=New ... WE can just use Dim...As New...? Then why most procedures have it in 2 statements the code? – Codo Feb 08 '14 at 23:09
  • Also you are absolutely correct on the first paragraph! It didn't existed we brought it to exist so my bad...:P – Codo Feb 08 '14 at 23:09
  • 1
    One reason to split it into two lines might be coding guidelines. Sometimes, the company wants you to declare all variables at the beginning and then work with it. – Thomas Weller Feb 08 '14 at 23:19
  • 3
    Another reason to split is because `As New` declares an *auto-instantiated* object, and that changes how nulling that reference behaves. Can lead to surprising bugs if you don't know about it. FWIW declaring all variables at the top is stupid; in any other language the recommendation is to declare variables as close as possible to their usage. Keep declaring them at the top, and then don't be surprised when half of them aren't used anywhere. – Mathieu Guindon Dec 21 '17 at 18:16
  • 2
    @Mat's Mug Putting all the declarations at the top can improve readability in a short procedure, but I agree for long procedures. To further elaborate on _italic_auto-instantiation_italic_, the single line syntax essentially creates an indestructible object (for more infromation, see: [link](https://stackoverflow.com/questions/8114684/what-is-the-reason-for-not-instantiating-an-object-at-the-time-of-declaration)) – nateAtwork Jan 10 '18 at 19:44
  • 1
    @nateAtwork I firmly disagree on "stick it to the top" improving readability in any context, but indeed, an auto-instantiated object is *essentially* indestructible. – Mathieu Guindon Jan 10 '18 at 19:46