ClearContents bug help

[ddub25]

I created a Command Button with the following code.

Private Sub CommandButton1_Click()
Sheets("IB_FB_H_3_Working").Range("$D$7:$D$8,$G$9:$L$34,$M$9,$M$18,$M$27,$N$9,$N$18,$N$27,$O$9:$O$34,$T$9:$T$34,$V$9:$V$34").ClearContents
End Sub

Whenever I use it, it returns the error message, "Code execution has been interrupted". Even so, it does manage to clear the cells that I want cleared. When I click 'Debug',
it brings up the code with the 'End Sub'part highlighted in yellow. Is this the problem part? It hasn't been a problem in the past. Can anyone help?

Dan

 

[Rick Rothstein]

Thanks Rick, much appreciated. I closed down excel, re-booted my computer as both have been running for the past month non-stop and now I don't get the error. Maybe the program had gained lots of errors over the period and just needed a reset.

About the absolute references - I've just realised that even when using absolute references, the references do not adjust if new rows and columns are created. I have copied my table down 10times and created a button for each to delete (reset) the contents of each table. If I want to add a column to each table, lets say, I then have a really tedious task of re-referencing the correct cells for each button to delete. Is there not a way of fixing the code to the original cells so that when rows or columns are added, the references remain in the correct place?

Yeah, rebooting is always a good starting point when things (in Excel or in other programs as well) stop working as expected.

As for the references problem... if you create Defined Names for your ranges in Excel, then reference the ranges through those Defined Names in your VB code, the code will automatically adapt to any structural changes made to the worksheet(s) referenced by those Defined Names. Once you create the Defined Names for your ranges, you reference them in code by quoting them for the Range object's argument in the same way you quoted the actual addresses. For example, if your Defined Name was named MyRange and covered cells A1:C5,F5:H9, then instead of referencing them like this...

Range("A1:C5,F5:H9")

you would use this instead...

Range("MyRange")

 

[xenou]

jasmith4, so if I used your code format, I'd write it like this? How is this code different and more advantages than using the previous code where it was all on one line? I didn't quite get understand, sorry.

I think the difference is the first version is all on one line, and the second version is not.

 

[ddub25]

Thanks for all of your help. I've created a named range called "Table1" for my first table (not in a table format) that encompasses all of the original references ("$D$7:$D$8,$G$9:$L$34,$M$9,$M$18,$M$27,$N$9,$N$18,$N$27,$O$9:$O$34,$T$9:$T$34,$V$9:$V$34"). So I've adapted the code to:

Private Sub CommandButton1_Click()
Sheets("IB_FB_H_3").Range("Table1").ClearContents
End Sub

and it works great. And, yes I don't seem to have a problems with referencing when I add new rows, columns etc. Just got to create named ranges, buttons and code for each of the 10 tables, which will be a lot easier and easier to manage with changes, so thanks very much for alll your help.

Dan

 

[jasmith4]

xenou, you're an MVP, but I couldn't disagree with you more. I've done almost seven years of VBA coding too, in Word, Excel and Access, and even dabbling in PowerPoint and Outlook a little. I've even developing routines to export all code and forms to text files, remove all code and forms, and import all code and forms back. (I did this mostly for the purposes of version control via CVS, SourceSafe or whatever).

I've encountered LOTS of strangenesses in Excel: the debugger stopping on a line with no breakpoint, Excel either freezing or vanishing off the screen, the Project Explorer continuing to display a project for a workbook I've closed (which I STILL get!), and many more. I find over and over again that cleaning code (and also copying all sheets to a new workbook) cures many of these strangenesses and makes the workbook's size far less. And I've communicated directly with Rob Bovey, who says that his and many others' similar experiences are precisely why he developed Code Cleaner, and that it does essentially what I'm doing: exports and removes all code and forms, saves/closes/re-opens the workbook, and re-imports the code and forms.

 

[xenou]

No problem - if it works for you that's great (it seems to be the cure for others from time to time also). I just wanted to note that I don't find such things happening to me so the problem is not necessarily something that is inherent in VBA or to be expected by all VBA developers. The only item in your list I do notice (not for at least a year now, though) is code stopping on a line as if it were a breakpoint. I have always "fixed" that by just deleting and pasting back in as text the one line that was the problem.

ξ

Edit: It does however seem that we are both encountering lots of strangeness with duplicate posts being generated by this board - I've just deleted three of them from this one thread.

 

[jasmith4]

.

 

[jasmith4]

No problem - if it works for you that's great (it seems to be the cure for others from time to time also). I just wanted to note that I don't find such things happening to me so the problem is not necessarily something that is inherent in VBA or to be expected by all VBA developers. The only item in your list I do notice (not for at least a year now, though) is code stopping on a line as if it were a breakpoint. I have always "fixed" that by just deleting and pasting back in as text the one line that was the problem.

ξ

Edit: It does however seem that we are both encountering lots of strangeness with duplicate posts being generated by this board - I've just deleted three of them from this one thread.

I've tried deleting the line with the phantom breakpoint, and sometimes it works. But now you seem to admit that you've had strangenesses, although to me you're one of the few lucky ones that have suffered so few.
What about the Project Explorer maintaining a project when you've closed a workbook? Try this: open Excel, open the VBE, open the Project Explorer, switch back to Execl, and do Ctrl-N/Ctrl-W five times or so. Do you now see six projects in the Explorer? Quite often that happens to me.

 

[xenou]

Well, I admitted the phantom breakpoint happens occasionally but not often -- and only needs a few keystrokes to fix so I don't consider it a lot of strangeness - only a little bit of strangeness. Anyway, I can't get six projects to show in the Explorer window following your instructions (not that I was hoping to see that result, since it doesn't sound like a barrel of fun).

 

[jasmith4]

Try opening/closing six workbooks after you've been working on a big XLSM's code for a while.

 

[xenou]

I hope you'll forgive me if I don't spend the rest of the afternoon trying to get Excel to act weird. I did try again with only a smallish size project (1 module, 1 class module) - no sign of extra projects in the VBE. I usually have 8 or so addins and/or hidden macro workbooks going in Excel also.