The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Problem with 'With' blocks in VBA

Hello all.

I have this problem with 'With' blocks and I can't find the right solution. Hope someone can lend a hand.

I have the following with block, which basically searches the sheet in a workbook for certain value.
If ID corresponds to certain value it calls Macro2 which contains another similar With block(which searches for another value in another workbook.
When Macro2 ends and code continues in the first With block (set c = .findnext(c)), which should return next same record within the searched range ...instead it returns Nothing and in the next line I get well known runtime error 91:Object variable or With block variable not set!
It seems variable 'c' loses reference when another With is used. Is there a way to fix this? Any suggestion/solution would be very appreciated. Thank you.

With Workbooks(3).Sheets(1).Range("C2:C5000")
  Set c = .Find(ID, LookIn:=xlValues)
  If Not c Is Nothing Then
      myAddress = c.Address
            if ID = iNumber
              Call Macro2
              'do something else
            end if

            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> myAddress         
End With
sub Macro2
With Workbooks(4).Sheets(1).Range("A2:A5000")
  Set d = .Find(ID, LookIn:=xlValues)
  If Not d Is Nothing Then
      myAddr = d.Address
            'some code
            Set d = .FindNext(d)
        Loop While Not d Is Nothing And d.Address <> myAddr
end with
end sub
VoodooFX Send private email
Thursday, October 13, 2005
I am initially sceptical as to whether this is duown to the use of the With construct. Tell me this: if you remove the With blocks and explicitly supply the with expression where needed, do you still get the same problem, or not?
Larry Lard Send private email
Thursday, October 13, 2005
Why not just ditch the "with" block and set a variable? The code would be easier to read too. Personally, if someone is working for me, "with" blocks are a no-no - because sooner or later they inevitably lead to nested "with" blocks, and (as my grandmother used to say*) that way madness lies.

* though not, admittedly, about "with" blocks
Syd Egan Send private email
Thursday, October 13, 2005
Yes, instead of using the With blocks, define a Range variable and store your range in that.
Kyralessa Send private email
Thursday, October 13, 2005
Thank you for your reply guys. I managed to work something out with those stupid With blocks. But I'll try it with variables nontheless.
Now that it works with With blocks and the task that this macro was developed for, is completed, I can concentrate on this variable approach.

Thank you again
VoodooFX Send private email
Thursday, October 13, 2005
The problem isn't the "with" blocks - Excel holds the "find" settings from the last find operation and ".findnext" just repeats them; the fact that macro2 has run a find operation of its own means that findnext isn't going to work - I would suggest running all of the find operations in macro1 first, putting the results into a range object and then running macro2, something along the lines of:


  dim resultsRange as Range
  set resultsRange = Nothing
  set c=.findNext(c)
  if Not (c Is Nothing) then
      set resultsRange = resultsRange.Union(c)
  end if

  for each c in resultsRange.cells()
      call macro2
  next c

Apologies for lousy syntax and capitalisation, but I'm rushing a little. Hope that this helps.

P.S. With blocks are only for use by slack, lazy and slapdash programmers, which is why I use them all the time (see above ;)
Paul Brown Send private email
Thursday, October 13, 2005
"With blocks" also have performance benefits because the compiler makes a temp variable for you.

There's nothing wrong with "with blocks", I use them all the time. Besides it's much nicer too look at than:

Smoochie Send private email
Thursday, October 13, 2005
"There's nothing wrong with "with blocks". . . "

That's a good one.  There are lots of bad things about With Blocks' and not many good things.  Many programmers avoid them like the plague, since in many cases they decrease readability (by increasing opacity and obscuring scope) and in most cases they increase the likelihood of buggy code.  Especially if you're nesting one With Block within another.

Malcolm Groves has has written an excellent article on good programming practices in Delphi that were prompted by a job he had analyzing problems in a Delphi job gone awry.  His conclusion:
"An amazing 4% of their bugs were caused by a single, unnecessary feature of Object Pascal. Any guesses? Yep, the with statement."

If you have any interest in reading the entire section on why you should avoid With Blocks you can find it midway down this article:
Herbert Sitz Send private email
Thursday, October 13, 2005
"If you have any interest in reading the entire section on why you should avoid With Blocks you can find it midway down..."

Which, by a hilarious coincidence, is where you usually find the error in a With block, too. Please don't confuse "I use" with "I recommend that you use"; for ten years I smoked cigarettes almost continuously and I don't recommend that either. "With" blocks come under a similar heading of "personal weaknesses that I really should give up".
Paul Brown Send private email
Thursday, October 13, 2005
Paul -- I agree.  I like to use With Blocks so long as it's just a single With (i.e., no nesting) and so long as the block being nested is short.  Anything more is asking for problems, imo.  Instead, just declare a nicely named little variable and assign your With object to it.
Herbert Sitz Send private email
Thursday, October 13, 2005
Ref "With Workbooks(3).Sheets(1).Range("C2:C5000")"

You can avoid unnecessary checking cells (or missing some if the solution is sucessfull/templatised) using the SpecialCells() function of the Range object.

ie xlCellTypeLastCell, xlCellTypeVisible

With Workbooks(3).Sheets(1).Range("C2:C" &
John Griffiths Send private email
Wednesday, October 26, 2005

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz