History of some of my Excel faux pas (boo-boos)
History of some of my Excel faux pas (boo-boos)
2
TomBarsh (Structural)
(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!
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)
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)
RE: History of some of my Excel faux pas (boo-boos)
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,
Just traded in my OLD subtlety...
for a NUance!
RE: History of some of my Excel faux pas (boo-boos)
RE: History of some of my Excel faux pas (boo-boos)
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: History of some of my Excel faux pas (boo-boos)
RE: History of some of my Excel faux pas (boo-boos)
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: History of some of my Excel faux pas (boo-boos)
RE: History of some of my Excel faux pas (boo-boos)
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)
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: History of some of my Excel faux pas (boo-boos)
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)
RE: History of some of my Excel faux pas (boo-boos)
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)
also. get eyes checked
RE: History of some of my Excel faux pas (boo-boos)
RE: History of some of my Excel faux pas (boo-boos)
RE: History of some of my Excel faux pas (boo-boos)
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)
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: History of some of my Excel faux pas (boo-boos)
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)
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,
Just traded in my OLD subtlety...
for a NUance!
RE: History of some of my Excel faux pas (boo-boos)
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,
Just traded in my OLD subtlety...
for a NUance!
RE: History of some of my Excel faux pas (boo-boos)
This article by John Peltier also looks relevant:
http://peltiertech.com/Excel/ChartsHowTo/CatVsValu...
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: History of some of my Excel faux pas (boo-boos)
Skip,
Just traded in my OLD subtlety...
for a NUance!