History of some of my Excel faux pas (boo-boos) 4

[TomBarsh]

I have been using Excel since Excel 3.0 with the old xlm macro language. Not that I consider myself an expert on Excel by any means, just a good journeyman.

For entertainment, here are a few of the things that have driven me batty with Excel over time. No telling how many hours were expended trying to resolve each of these.

1- Some time back I had a VBA routine to "register" various transactions in a system. To make it easy on myself I assigned shortcut keys Ctrl + r. Boy, did that make life easier! But there was some kind of situation, I do not recall the details, but something was not working right in all conditions. I wondered if somewhere in some file that was open all the time (personal.xls, etc) there was another routine that used the same keystrokes. I even started trying to find a way to list all of these shortcut key strokes; I don't think I ever finished that project...yes, yet another unfinished project! Anyway, it turned out that I had forgotten that Ctrl + r was the built-in shortcut to "copy right". yikes, egg on my face.

2- Many of my VBA routines are short, sweet, to-the-point utilities that might have a short life. So for convenience I sometimes use names like tom1, tom2, bob7, bob77, etc. This goes back to the days of Excel 5 and VBA. Well, a few months ago I was trying to 'link' such a routine to a form control button on a worksheet and kept getting errors! I could give that routine another name and it would link to the button.

What it proved to be is that my sub named "tom12" in a Module could not be linked to the button on a worksheet. Excel thought that "tom12" referred to the cell in column "tom" and row "12". But a similar name in the VBA code for the worksheet could be linked to the command button...even if these were on different sheets! I realized that this conflict would not have happened except that sometime back Excel was opened up to more rows and columns than I'll use in a lifetime. But why only when in the Module, not in the worksheet's VBA code?

3- My most recent "gotcha!!!" was again a name thing, actually the very same cause as above but in a different circumstance. I wanted to call a sub that was in another workbook. I followed the simple procedure as described in Help, as found at some good Excel sites on the web, and as described in my Walkenbach 'Excel 2007 Power Programming with VBA' book. The darn things just would not work for me, I always would get a cryptic error, driving me to check the "Trust Center" (boy, I had forgotten all about that thing), the VBA Tools/References, etc, all sorts of places where I fear to tread. For some reason I just tried changing the name (maybe in preparation for posting a question here at eng-tips) and by golly the doggone thing worked! arrggghhh, sometimes Excel is so-ooo frustrating!

 

[TomBarsh]

To be clear on point 3 above, the statement is very simple: Application.Run "my_vba_target.xlsm!Tom12"

But the reference to "Tom12" will fail. But it works with any name that does not appear to refer to a cell address. But try Googling that.

 

[BUGGAR]

I didn't know you could link a spreadsheet to a bunch of Word files. And send out all the bids before the bid opening.

 

[SkipVought]

John Walkenbach, my Excel/VBA hero! I particularly like his excellent Excel Charts book, including lots of VBA, especially for getting Chart Events for ChartObject.Chart, embedded charts.

My faux pas was with regard to workcenters that had the form nnnnxn. Along came workcenters 1234E2. That encouraged me to research how Excel assumes various things about characters that are entered into the sheet, and consequently regarding text files that are opened by Excel, like .csv files. As a result I wrote several FAQs @ Tek-Tips:

http://www.tek-tips.com/faqs.cfm?fid=5827

&

http://www.tek-tips.com/faqs.cfm?fid=7375

I'm sure I made LOTS more over the past 20+ years, but none that stick in my mind. I learned and moved on.

Oh, yes, the time that DB Admin contacted me and asked me why I was hitting an Oracle server thousands of times an hour, dragging down performance. I had distributed about 100 db function for users to get ad hoc data directly from the source tables for their worksheet use. Here i had failed to use the Parameter object in my ADODB access and that put a huge overhead on the system. Another lesson learned.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[TomBarsh]

Now I can add a new one. Referring to a particular workbook/worksheet in VBA. Command did not work even though it worked great without the particular workbook/worksheet qualifiers. After a frustrating 20 minutes (spread over about an hour) realized my worksheet tab title had "two spaces" in it instead of only one (as it looked). I thought I was smart not wasting time to copy and paste the name. Another lesson learned.

 

[SkipVought]

Regarding referencing worksheets, I'd suggest using the CodeName. The users can mess with the sheet name all they like and your code will not break in that account.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[TomBarsh]

Skip, interesting concept. Thanks! I'll give it a whirl.

 

[SkipVought]

When I start an Excel VBA project, I routinely modify each sheet CodeName to something meaningful, such as, for a sheet named Requirements, I might give the CodeName wsREQ.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[TomBarsh]

Not quite a knucklehead stunt: I was working on three complex and intricately linked workbooks this week. It was handy to size the Excel window so that it ran across my two monitors, then I had one workbook on one monitor, and the two others on the other monitor. This really made it handy to watch the events happening; I had forgotten how handy this can be. But when the Excel window is resized back to normal on one monitor any open files that are/were on the right side of the Excel window are not visible. So, one might forget that they are open, and if such files are saved then they will show up at the same location when reopened...they'll be off the visible window and seem to be "missing". Still, it's a useful view for complex work.

 

[electricpete]

Some of my bonehead moves in the past involved mis-spelling a variable name.
Now I add "option explicit" (no quotes) at the beginning of every module.
That enforces a requirement that every variable must be declared.
Now if I misspell a variable, it gets flagged as an undeclared variable and my spelling error is immediately apparent.

=====================================
(2B)+(2B)' ?

 

[SkipVought]

And/or start your variable/reserve name and ctr+SPACE to auto finish or list in context choices in your code. But yes, yes, YES! Set option explicit in Tools > Options.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[analogkid2digitalman]

I third the Option Explicit.

Skip- thanks for the tip on CodeName. I usually protect my worksheets and thus have not ran into someone changing the names of the sheets but I will use CodeName in the future now that I am aware of it.

-AK2DM

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"It's the questions that drive us"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[davidbeach]

Is there any other way of coding than having Option Explicit? ;-)

 

[MechaPro]

There is another way to protect your worksheet name, I use this all the time to maintain the integrity of my workbook.

It can be done in Workbook BeforeClose or BeforeSave option
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim WS As Worksheet
Dim WSCount As Integer

Dim strWSNames(2) As String
strWSNames(1) = "MyWorksheet1"
strWSNames(2) = "MyWorksheet2"

WSCount = 1

For Each WS In ThisWorkbook.Worksheets
If WS.Name <> strWSNames(WSCount) Then
WS.Name = strWSNames(WSCount)
WSCount = WSCount + 1
End If
Next WS
End Sub

Let me know if you have any questions! Cheers
Probal

 

[TomBarsh]

Note to self: when creating a custom number format, "0.033" is not the same as "0.0##". A little difference there. ; - )

also. get eyes checked

 

[Agent666]

I recently got caught out doing a half day of writing VBA directly in an excel addin, Quit Excel and realised there is no prompt to save the changes to the *.xlam addin resulting in all my work being lost!

 

[TomBarsh]

Agent666... Ouch! that would hurt!

 

[IDS]

I recently discovered that if you plot a function with a trend line on a line chart (as opposed to an XY chart), and display the trend line equation, the equation will be completely wrong unless the x values are a continuous sequence of integers starting at 1. I have never been caught out with that one myself, because I hardly ever use line charts, but looking at the trend line examples on the Web, they almost all use line charts, and almost no-one gives any warning about the trend line equation.

More details at:

https://newtonexcelbach.wordpress.com/2017/04/16/displaying-trend-line-equations-on-line-charts/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SkipVought]

If you use a Line Chart (or most other chart types other than Scatter) the x-axis defaults to a Category axis, one slot per y value. However, it is possible to modify the x-axis to be proportional x values.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IDS]

If you use a Line Chart (or most other chart types other than Scatter) the x-axis defaults to a Category axis, one slot per y value. However, it is possible to modify the x-axis to be proportional x values.

Could you expand on that? I thought the x axis was always treated as text in a line chart. How can you assign values other than 1,2,3...?

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/