A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.
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.
Set c = .Find(ID, LookIn:=xlValues)
If Not c Is Nothing Then
myAddress = c.Address
if ID = iNumber
'do something else
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> myAddress
Set d = .Find(ID, LookIn:=xlValues)
If Not d Is Nothing Then
myAddr = d.Address
Set d = .FindNext(d)
Loop While Not d Is Nothing And d.Address <> myAddr
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
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
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
if Not (c Is Nothing) then
set resultsRange = resultsRange.Union(c)
for each c in resultsRange.cells()
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 ;)
"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:
"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 -- 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.
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" &
This topic is archived. No further replies will be accepted.Other recent topics
Powered by FogBugz