Excel 2003 - VBA - "with...end with

  • Thread starter Thread starter Craig Brandt
  • Start date Start date
C

Craig Brandt

Hi.

This is a curiosity question.

What is the advantage of using the With/EndWith statement. I see it in many
of the examples, but it seems to add lines of code and a level of complexity
to in many cases, a simple problem. Example:

With Sheets("Control")
Testdata = .Range("D4")
End With

Seems overly complex when the following will do.

Testdata = Sheets("Control").Range("D4")

In fear of miss-out on something really cool, I am awaiting the words of
wisdom.

Thanks,
Craig
 
Advantageous and saves time in looking at the sheet a/o range each time for
MULTIPLE instances
Testdata = Sheets("Control").Range("D4")
Testdata1 = Sheets("Control").Range("D5")
Testdata2 = Sheets("Control").Range("D6")
 
Besides saving time/typing when you use a parent object several times (never
once as in your example) I personally think it enhances the readability of
the code.

--
Jim
| Hi.
|
| This is a curiosity question.
|
| What is the advantage of using the With/EndWith statement. I see it in
many
| of the examples, but it seems to add lines of code and a level of
complexity
| to in many cases, a simple problem. Example:
|
| With Sheets("Control")
| Testdata = .Range("D4")
| End With
|
| Seems overly complex when the following will do.
|
| Testdata = Sheets("Control").Range("D4")
|
| In fear of miss-out on something really cool, I am awaiting the words of
| wisdom.
|
| Thanks,
| Craig
|
|
 
and it is usually more efficient, the object is resolved just once.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
and it is usually more efficient

That would certainly seem to be the case. I didn't cite that as an
advantage however because some learned person, maybe Ken Getz, years ago did
some testing and said it didn't work out as you would expect. Or so I
remember. I'm not going to test it myself since I'm not changing my coding
style no matter what<g>.

--
Jim
| and it is usually more efficient, the object is resolved just once.
|
| --
| HTH
|
| Bob
|
| (there's no email, no snail mail, but somewhere should be gmail in my
addy)
|
| | > Besides saving time/typing when you use a parent object several times
| > (never
| > once as in your example) I personally think it enhances the readability
of
| > the code.
| >
| > --
| > Jim
| > | > | Hi.
| > |
| > | This is a curiosity question.
| > |
| > | What is the advantage of using the With/EndWith statement. I see it in
| > many
| > | of the examples, but it seems to add lines of code and a level of
| > complexity
| > | to in many cases, a simple problem. Example:
| > |
| > | With Sheets("Control")
| > | Testdata = .Range("D4")
| > | End With
| > |
| > | Seems overly complex when the following will do.
| > |
| > | Testdata = Sheets("Control").Range("D4")
| > |
| > | In fear of miss-out on something really cool, I am awaiting the words
of
| > | wisdom.
| > |
| > | Thanks,
| > | Craig
| > |
| > |
| >
| >
|
|
 
.... nor me

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Thanks for your input. I can understand it when there are many operations on
a single object but I still don't use it because I think it is much clearer
without it. That was a statement by a non-trained person dabbling in
programming.

Craig
 
Jim,
It was Ken Getz in a magazine article, maybe 8 or 10 years ago.
And the bottom line was how many "dots" were eliminated.
No dots eliminated no speed improvements.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)





That would certainly seem to be the case. I didn't cite that as an
advantage however because some learned person, maybe Ken Getz, years ago did
some testing and said it didn't work out as you would expect. Or so I
remember. I'm not going to test it myself since I'm not changing my coding
style no matter what<g>.
 
Thanks, Jim. Good memory too.

--
Jim
Jim Cone said:
Jim,
It was Ken Getz in a magazine article, maybe 8 or 10 years ago.
And the bottom line was how many "dots" were eliminated.
No dots eliminated no speed improvements.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)






That would certainly seem to be the case. I didn't cite that as an
advantage however because some learned person, maybe Ken Getz, years ago
did
some testing and said it didn't work out as you would expect. Or so I
remember. I'm not going to test it myself since I'm not changing my
coding
style no matter what<g>.
Click to expand...
 
Back
Top