×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

2
(OP)
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!

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

(OP)
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.

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

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.

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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.

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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.

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

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)' ?

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

Is there any other way of coding than having Option Explicit? winky smile

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

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

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

(OP)
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

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

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!

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

(OP)
Agent666... Ouch! that would hurt!

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

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/d...

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

Quote (Skip)

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/

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

As I recall, it was 6-7 years ago the last time I did something like this. I was actually plotting manufacturing part inventory:
1) on hand (on primary)
2) requirements (negative stacked column chart type on primary y-value axis)
3) replenishment (positive stacked column chart type on primary y-value axis)
4) shortages (negative stacked column chart type on secondary y-value axis)
5) surpluses (positive stacked column chart type in secondary y-value axis)

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

Doug,

I did this for a former colleague from Bell Helicopter a couple years as an example of what he could expect. Unfortunately this chart example actually uses a category axis, because I aggregated the chart data point values by week. The axis can be changed to a DATE axis type and the plot is proportionally accurate but not visually impactful.

If you want to check it out, enable macros, select the cell containing RP-RQ Surplus-Shortage Forecast Chart in the Dashboard.


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

I have found John Walkenbach's Excel Charts book very enlightening for making charts do extraordinary things.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources