Excel - Best Practices Question - Usage of 'With' statement

[ralphjmedia]

I have received conflicting advice re: usage of the 'With' statement.

A review of my coding by an experienced coder was critical w/ regard to usage like this (fabricated example below):

Please Login or Register  to view this content.

The issue: only one object following the 'With' statement - proper form would be 3 distinct lines of code, each starting with 'With wksSample' like this:

Please Login or Register  to view this content.

I was told a common 'dot operator' was required to follow the initial object (example below). If that common operator does not occur, 'With' should not be employed.

Please Login or Register  to view this content.

1) Opinions re: this commonly-occurring coding scenario?

2) Q: It was my belief, when a number of 'things' are done to an object, that employing the 'With' operator speeds up the code. True/False?

3) Moreover, what about UserForm coding? Example: I presently have many sets of lines of code like this:

Please Login or Register  to view this content.

Can... rather, should this be done?

Please Login or Register  to view this content.

My thanks in advance.

RC (ralphjmedia)

[sweep]

Hi,

According to Mr Pearson, "with" is a tactic that will speed up execution of code

http://www.cpearson.com/excel/optimize.htm

[ralphjmedia]

Sorry! Will do so in future. Post formatting corrected (I believe). Is it now eligible to receive/display additional responses? Thanks.

[shg]

Aside from performance improvement, the With statement eliminates duplication; you only need to change the object name in the With statement.

[Kyle123]

It depends on the context, the main argument against it is simply that it is not as readable - this seems to be why it wasn't added to C# where it still exists in VB.Net. Of course whether this is actually the case is highly subjective - in this context it is pretty unambiguous, however since it is possible to have nested "With"s and there is no auto indentation in the IDE, things can become a bit hairy if you use it a lot and don't indent properly.

I'm also somewhat sceptical that it actually offers any noticeable performance improvement over declaring a variable and referencing that.

In the end, it's personal preference - it's really up to you whether you use i or not, there's no right answer.

[Kyle123]

It depends on the context, the main argument against it is simply that it is not as readable - this seems to be why it wasn't added to C# where it still exists in VB.Net. Of course whether this is actually the case is highly subjective - in this context it is pretty unambiguous, however since it is possible to have nested "With"s and there is no auto indentation in the IDE, things can become a bit hairy if you use it a lot and don't indent properly.

I'm also somewhat sceptical that it actually offers any noticeable performance improvement over declaring a variable and referencing that.

In the end, it's personal preference - it's really up to you whether you use i or not, there's no right answer.

[romperstomper]

I'd ask your experienced coder why he thinks it's better to write the same variable three times.

[ralphjmedia]

OK, to better clarify, here's the simple either/or:
Setting aside issues of personal preference, readability, or microseconds of processing time possibly saved/lost - is this 'legal' or bad practice (below)?

Please Login or Register  to view this content.

I was told that a "dot operator" was required after the object when electing to employ the 'With' statement - just the object, followed by nothing, and then a collection of operations below, closed by an 'End With' was wrong - if no common operators exist, the above code should look like this:

Please Login or Register  to view this content.

It appears to work either way, and of course, there's no discernable execution time when it comes to a single block of code.
Moving forward, I am looking for the best method to employ across a complex application that contains a number of UserForms, Charts, Ranges, etc. that all see a number of operations applied, depending on conditions that change based on user input (clicking a command button, selecting a ComboBox choice, etc).

Again, my thanks.

[Kyle123]

Both are fine and purely down to personal preference. Makes absolutely no difference either way