Dim mySheet As New Worksheet

[Frans van Zelm]

Hi all,

I will be stupid, a moron, etc. but ... How to continue after
Sub NoGo()
Dim mySheet As New Worksheet
?
End Sub

The line
Dim X As New Worksheet
is stated in the VBA-help but that's all.

I can do with:
Sub WillGo()
Dim mySheet As Worksheet
Set mySheet = Worksheets.Add
...
End Sub

And also:
Sub Splendid()
Dim myApp As New Application
...
End Sub

Please give me any usefull, working code sample with
Dim mySheet As New Worksheet
Or explain why is doesn't work and still it is in the help.

Thanks, Frans van Zelm

 

[abcd]

Dim mySheet As New Worksheet
this mean mySheet is ready to be a sheet

Set mySheet = Worksheets.Add

add is a method wich mean you want to add a sheet to the book
then, you re-use this object and decide to store the added new sheet to
mySheet.

Please give me any usefull, working code sample with
Dim mySheet As New Worksheet
Or explain why is doesn't work and still it is in the help.

what does not exactly work ?
it works perfectly:
it does what you wrote, maybe not what you wish


the real question is: what to you want to do ?

an example:
mysheet.Range("A1").value = mysheet.name
this will add the name of the new created sheet in its A1 cell

 

[Frans van Zelm]

Hi abcd,

Thanks for your reply but I still don't get the point of New.
The help says that using New makes Set not neccessary.
Both samples below work but both need Set. So what is the point of New?

Sub Try1()
Dim mySheet As New Worksheet
Set mySheet = Worksheets.Add
mySheet.Range("A1").Value = mySheet.Name
End Sub

Sub Try2()
Dim mySheet As Worksheet
Set mySheet = Worksheets.Add
mySheet.Range("A1").Value = mySheet.Name
End Sub

Frans

 

[Jim Cone]

There is more information available here...

Including: "Don't Use The New Keyword In A Dim Statement"
http://www.cpearson.com/excel/variables.htm

Jim Cone
San Francisco, USA


Hi all,
I will be stupid, a moron, etc. but ... How to continue after
Sub NoGo()
Dim mySheet As New Worksheet
?
End Sub
The line
Dim X As New Worksheet
is stated in the VBA-help but that's all.
I can do with:
Sub WillGo()
Dim mySheet As Worksheet
Set mySheet = Worksheets.Add
...
End Sub
And also:
Sub Splendid()
Dim myApp As New Application
...
End Sub
Please give me any usefull, working code sample with
Dim mySheet As New Worksheet
Or explain why is doesn't work and still it is in the help.
Thanks, Frans van Zelm

 

[abcd]

a worksheet is not a type of data but is an object: that's all the
difference

you can type
dim A as string
but
dim S as worksheet
doesn't mean anything because a sheet is not a type of data but is an
object. So "new object" is the syntax.

It's just a syntax question: this makes you remember what your are
edaling with, but also makes differences inside the VBA motor:
creating a sheet is in memory creating many data (size, cells, colors,
etc.) so the "set" (and not = ) means "launch the procedure to
duplicate all the data of a sheet". It's not one data, not an array of
data but a whole structure of data.

It's usefull: the evidence is that it makes you think about the
difference beteween a sheet and a string : one is an object

 

[Vasant Nanavati]

Not sure what you mean but your statement:

dim S as worksheet
doesn't mean anything

is incorrect.

 

[abcd]

Vasant Nanavati a écrit :

Not sure what you mean but your statement:
is incorrect.

it's not mathematics it's human language
what i mean is explain after

 

[Vasant Nanavati]

OK, whatever you say! But I still don't get it. Perhaps a language
interpretation problem.

 

[abcd]

I try something else:
you can say
h will be in meters
c will be in seconds

but saying
h will be a house
doesn't mean anything (in this way of saying) because a house is not a unit.

But you can say "h" will be a short name for the house i'm building
and this "h" will have plenty of size, colors, timings (for building
it), etc. But a house is still not a unit.

the syntax dim ... as ... is supposed to mean
dim <variable> as <type of variable>

so , in this vba langage, saying s is a sheet does not mean anything:
a sheet is not a type of variable, it's an object carrying plenty of
variables (called properties) but also methods (to build things)

it's a "vba langage" choice:
we could decide we will write
dim s as sheet
because it's easier: but it's not the choosen way and the choosen way
permitt to remenber the difference between a unit and a house when just
seeing the shortname of it.

 

[Vasant Nanavati]

Again, I have to disagree. Of course, a worksheet is a composite object made
up of many components, but a string is also a composite data type made up of
many characters.

I guess I'm saying that:

Dim S As Worksheet

is a perfectly good VBA statement as well as perfectly good programming
practice.

 

[abcd]

Thank you for taking time to try and understand what i mean and force me
to take care about words.

Some people say the difference between Einstein and other people was:
He was not wondering about details and mathematics like others. He was
looking for other (interesting) questions.
I'm not saying i'm Einstein. Just saying sometimes it's better not to
read too much the details and choose the global point of view for the
opening it brings. ;-)
(the mathematics where written by his friend, and his end of life jobs
where not followed by people --they are today--)

Please, do try to allow me my other way of saying. My saying is wrong in
your (-sorry but- frozen) model. I understand what makes you sad. You
are right to say (and add after my writting) that vba is powerfull
enought to also permit a:
dim s as sheet
syntax and, ok, my writing can make think it's forbidden to write this.
(I must choose a "does not mean what we expect because we need more to
create the sheet")

I forgot words and look at ideas only: the syntax was juged "not enought
to do the job" because there's not the same level between sheets and
strings and we do not want people to forgot this difference.

the difference between "as sheet" and "as new sheet" is explained at the
end of the
http://www.cpearson.com/excel/variables.htm
link given by Jim Cone

and i assume Frans (the asker) will also read it.

Even after the article you still must admit we do _not_ have a
dim T as new string
syntax (this is forbiden: vba doesn't expect this kind of object after
new) here comes my answer.

Why not ? if "set / new" is so powerfull and save time, why not also
using it for strings ???

"do we really need a special 'new' syntax with sheets ?". That's the way
i see the abyss of the question. (you're right: going further and not
said a word to warn readers, so ? it's me; human beeing, hello you ... )
well, in fact you might say i'm an original guy; But that's the
difference between vba and human talking.

My idea was here:
house (=plenty units and workers) versus unit
so "dim as sheet" does not mean the construct of the house...
because the vba syntax wish to distinguish them

Am i such raving mad ? Whatever: i don't care it brings fun...
but if you read me more, you should be warn i'm an original one

 

[Frans van Zelm]

Hi Jim,

Thanks for the link. I promise never to use the Dim ... New any more.

But I am still puzzled by this Help-information (same text to be found in
MSDN). In short:
"After 'Dim X As New worksheet' you do not need a Set-command."
I tried many ways but ... niente.
Is this help a slip of the pen?

Frans

 

[abcd]

well, whatever is written, i tryed to test and chrono the both methods:
not such a difference (with worksheet in my test)
4% only if i loop a test: if S.name = ""
8% if the test is S is nothing

this ùean it depends on the action
but it's not 200 times faster
using dim as new is not such a bad way
(it's true one is better but... in real life the test will not be made
30000000 times and the few % is not enought to take real care of it all
day long, i think)



the test: lauch it and wait...
(on my computer win + excel 2000)

Private Declare Function GetTickCount Lib "kernel32" () As Long

Sub test()
Dim TM&, i&


Dim S As New Worksheet
Set S = Feuil1
Dim T As Worksheet
Set T = Feuil1

TM = GetTickCount ' chrono
For i = 1 To 30000000
If S.Name = "" Then: Beep
Next i
[A1] = GetTickCount - TM

TM = GetTickCount ' chrono
For i = 1 To 30000000
If T.Name = "" Then: Beep
Next i
[A2] = GetTickCount - TM

TM = GetTickCount ' chrono
For i = 1 To 30000000
If S Is Nothing Then: Beep
Next i
[B1] = GetTickCount - TM

TM = GetTickCount ' chrono
For i = 1 To 30000000
If T Is Nothing Then: Beep
Next i
[B2] = GetTickCount - TM

End Sub

 

[Jim Cone]

Frans,

I believe it is a slip of the pen
I could not find the specific help reference you refer to,
but I did find this reference...

"...The New keyword
can't be used to declare variables of any intrinsic data type,
can't be used to declare instances of dependent objects, and
can’t be used with WithEvents."

I have only found use for the 'New' word when creating a
Collection object (as Chip Pearson pointed out)
or when creating a new instance of an application, as in...
Dim appWord as Word.Application
Set appWord = New Word.Application

Regards,
Jim Cone
San Francisco, USA


Hi Jim,
Thanks for the link. I promise never to use the Dim ... New any more.
But I am still puzzled by this Help-information (same text to be found in
MSDN). In short:
"After 'Dim X As New worksheet' you do not need a Set-command."
I tried many ways but ... niente.
Is this help a slip of the pen?
Frans

 

[Frans van Zelm]

Dear Jim

So perhaps I am not that stupid after all. Thanks for your info.

Discussion closed, as far as I'm concearned.

Frans