We've seen compile errors in Excel VBA. How about some run-time errors?
Run-time error '1004'
AutoFit method of Range class failed
If you do Selection.AutoFit, instead of Selection.Columns.AutoFit. In Dot Net, this kind of cr- gets caught at compile-time. As an interesting side, you can explicitly set the column width in terms of number of characters.
You can develop your own run-time error handler and reference it using "On Error Goto (Insert Name of RTEH here)" but please note it will not be re-entrant.
Thursday 25 February 2010
Tuesday 23 February 2010
Sunday 21 February 2010
Excel Macro-Enabled Workbooks in 2007, Worksheet Functions and Error 51
What's the game with xlms files (Excel macro-enabled workbooks)? Why do I need to save to a "macro-enabled" file to use macros?
The purported justification is added security.
Microsoft are willing to complicate programmability (the key strength of the OLE architecture) under the pretext of security.
A short-term solution is to save as Excel 1997-2003 workbook.
Ok, so why are we so bothered about this.
Let's say we want to create a new Excel worksheet function. We can create this in a module. Suppose we make a function that computes sales commission which returns 5% of whatever number the user puts in. Easy enough. If you didn't define a variable, you'll get an error "Variable not defined" (assuming Option Explicit is set, almost always a good idea).
Suppose you write a worksheet function and a message box appears: "That function is not defined". What does it mean? Usually, it means you have created a function using a reserved keyword in VBA. For example, you can't define a worksheet function called step, because step is a VBA reserved keyword e.g. For I = 1 To 10 Step 2.
Taking Square Roots
To compute a square root in VBA use the SQR function. This is the same as SQRT in an Excel worksheet. Oftentimes, you'll find the raw VBA method is somewhat different to the worksheet function.
Taking Logarithms
In VBA, log means natural logarithm. In a worksheet, log means log to base 10. Alternatively you can use log(number, base).
Other things "not defined"
We've seen the error "Function not defined" now suppose you open your workbook one day and it says "User-defined type not defined". Problem is - you can't find the location where the code is breaking. Even shoving a breakpoint in where you think the problem is doesn't help.
Why? Because it's a "Compile Error" not a run-time error! First step, identify all type definitions in your VBA modules (they start with the prelude "Public Type" or "Private Type"). Then see all usages. Next check WHERE the Type is defined. Are there functions defined before it? Try commenting out those functions.
You do the steps above and try to rerun your program (I avoid the dimunitive term macro). Now, the Type error vanishes, but the next time you use the type you see the message "Compile Error: Internal Error". Out of curiosity, click Help and you will see a description of the error: you will see it is "Internal Error (Error 51)". Sheepishly, the docs ask the developer to "make sure (the) error wasn't generated by the Error statement or Raise method". The explanation and solution is given as: "An internal malfunction has occurred in Visual Basic .. contact Microsoft Product Support Services to report the conditions under which this error appeared". This is an error in the Visual Basic symbol table caused by the order in which you define things. Visual Basic requires you to define objects in the order: Types, Functions and Functions of Functions. Messing up the order (e.g. by so-called "refactoring" - one of the most stupid terms in computing) or missing a definition that the compiler doesn't catch can result in Error 51.
The purported justification is added security.
Microsoft are willing to complicate programmability (the key strength of the OLE architecture) under the pretext of security.
A short-term solution is to save as Excel 1997-2003 workbook.
Ok, so why are we so bothered about this.
Let's say we want to create a new Excel worksheet function. We can create this in a module. Suppose we make a function that computes sales commission which returns 5% of whatever number the user puts in. Easy enough. If you didn't define a variable, you'll get an error "Variable not defined" (assuming Option Explicit is set, almost always a good idea).
Suppose you write a worksheet function and a message box appears: "That function is not defined". What does it mean? Usually, it means you have created a function using a reserved keyword in VBA. For example, you can't define a worksheet function called step, because step is a VBA reserved keyword e.g. For I = 1 To 10 Step 2.
Taking Square Roots
To compute a square root in VBA use the SQR function. This is the same as SQRT in an Excel worksheet. Oftentimes, you'll find the raw VBA method is somewhat different to the worksheet function.
Taking Logarithms
In VBA, log means natural logarithm. In a worksheet, log means log to base 10. Alternatively you can use log(number, base).
Other things "not defined"
We've seen the error "Function not defined" now suppose you open your workbook one day and it says "User-defined type not defined". Problem is - you can't find the location where the code is breaking. Even shoving a breakpoint in where you think the problem is doesn't help.
Why? Because it's a "Compile Error" not a run-time error! First step, identify all type definitions in your VBA modules (they start with the prelude "Public Type" or "Private Type"). Then see all usages. Next check WHERE the Type is defined. Are there functions defined before it? Try commenting out those functions.
You do the steps above and try to rerun your program (I avoid the dimunitive term macro). Now, the Type error vanishes, but the next time you use the type you see the message "Compile Error: Internal Error". Out of curiosity, click Help and you will see a description of the error: you will see it is "Internal Error (Error 51)". Sheepishly, the docs ask the developer to "make sure (the) error wasn't generated by the Error statement or Raise method". The explanation and solution is given as: "An internal malfunction has occurred in Visual Basic .. contact Microsoft Product Support Services to report the conditions under which this error appeared". This is an error in the Visual Basic symbol table caused by the order in which you define things. Visual Basic requires you to define objects in the order: Types, Functions and Functions of Functions. Messing up the order (e.g. by so-called "refactoring" - one of the most stupid terms in computing) or missing a definition that the compiler doesn't catch can result in Error 51.
Saturday 20 February 2010
From Application Software to Operating Systems: The Awesome Story of David Cutler
Mark Russinovich is the kingpin of Windows troubleshooting. He sold a company called SysInternals to Microsoft in 2006. In a 1998 article in Windows Magazine, Mark talked about the history of NT and its intellectual origins in the progamming backwaters of DEC. In the article he describes the background of David Cutler, Chief Architect of NT, and part of the original VMS development team. He made a transition from writing application software at DuPont to writing operating systems at DEC. Digital announced VMS in 1977. Cutler was restless by 1981 and threatened to leave Digital. In response Digital gave Cutler 200 hardware and software engineers - Cutler moved to Seattle (Home of Starbucks) and started a dev-center out there in Washington State. Their mandate was to develop a new CPU architecture and OS for the 1990s. In 1988, Cutler's project was cancelled and MS execs leaped in to grab Cutler, along with 20 engineers from DEC. The result was Windows NT. It took over 200 developers and testers to bring it into fruition. With NT came the awesome new Win32 API, the 32-bit evolution of the 16-bit API of Windows 3.0.
Usenet joke: If you increment each letter in VMS you get WNT.
Usenet joke: If you increment each letter in VMS you get WNT.
Subverting the Office 2007 Keyboard Shortcut Time Lag
To access MS Office Button (the moral equivalent of the File Menu), the keyboard shortcut Alt-F takes you there. This (quite often) displays a time lag with respect to the previous versions of Excel. Part of the reason can be attribute to the more intensive graphics (for example when you hit Alt, a bunch of labels get illuminated in the Ribbon). You also need to be au fait with the Escape key to disable any menu selection (for example if you have hit a bad shortcut and want to reset your settings so you can attempt a new k/b shortcut).
One way of dealing with the time lag is to allow for it when you use keyboard shortcuts. For example you can do the following keystrokes: 1) escape, to disable any previous menu selections, 2) hit alt -> pause for 2s, 3) hit F. This is ridiculous but is one way to guarantee successful execution of a k/b shortcut.
The most useful interactions with the Button is access to the "Excel Options" page. To access type: Alt-F-I (Alt-Fixed-Income (Options)). Quick guide to the sections:
1. Formulas - allows you to set automatic calculation on or off.
2. Proofing - enables you to disable autocorrect.
3. Advanced - allows you to configure the supposed multithreaded calculation.
4. Trust Center - followed by Alt-T (Trust Center Settings) allows you to set Privacy Options on your workbook.
Cntrl-F1 allows you to minimise or restore the Ribbon (like an HUD).
One way of dealing with the time lag is to allow for it when you use keyboard shortcuts. For example you can do the following keystrokes: 1) escape, to disable any previous menu selections, 2) hit alt -> pause for 2s, 3) hit F. This is ridiculous but is one way to guarantee successful execution of a k/b shortcut.
The most useful interactions with the Button is access to the "Excel Options" page. To access type: Alt-F-I (Alt-Fixed-Income (Options)). Quick guide to the sections:
1. Formulas - allows you to set automatic calculation on or off.
2. Proofing - enables you to disable autocorrect.
3. Advanced - allows you to configure the supposed multithreaded calculation.
4. Trust Center - followed by Alt-T (Trust Center Settings) allows you to set Privacy Options on your workbook.
Cntrl-F1 allows you to minimise or restore the Ribbon (like an HUD).
Friday 19 February 2010
Thursday 18 February 2010
VBA Annoyances
CONDITIONAL BREAKPOINTS
You CAN'T SET conditional breakpoints in Excel VBE...but you can SIMULATE one with the if statement.
PRIVACY WARNING WHEN SAVING WORKBOOKS
Privacy warning: This document contains macros, ActiveX controls, XML expansion pack information, or Web components. These may include personal information that cannot be removed by the Document Inspector.
How to I get rid of this tripe?
Excel Option > Trust Center > Trust Center Setting > Macro Settings > click Enable All Macros ....
Excel Option > Trust Center > Trust Center Setting > Active X Settings > click Enable All controls without restriction ....
EXCEL 2007 CLAIMS TO DO MULTI-CORE CALCULATION
Where is this functionality at the VBA level.
NO VOLATILE KEYWORD IN EXCEL VBA
Boo!
NO NOTION OF CLOSURES IN BUILDING WORKSHEET FUNCTIONS
Closures would be real useful when implementing mathematical models in Excel.
You CAN'T SET conditional breakpoints in Excel VBE...but you can SIMULATE one with the if statement.
PRIVACY WARNING WHEN SAVING WORKBOOKS
Privacy warning: This document contains macros, ActiveX controls, XML expansion pack information, or Web components. These may include personal information that cannot be removed by the Document Inspector.
How to I get rid of this tripe?
Excel Option > Trust Center > Trust Center Setting > Macro Settings > click Enable All Macros ....
Excel Option > Trust Center > Trust Center Setting > Active X Settings > click Enable All controls without restriction ....
EXCEL 2007 CLAIMS TO DO MULTI-CORE CALCULATION
Where is this functionality at the VBA level.
NO VOLATILE KEYWORD IN EXCEL VBA
Boo!
NO NOTION OF CLOSURES IN BUILDING WORKSHEET FUNCTIONS
Closures would be real useful when implementing mathematical models in Excel.
Microsoft Office Documents Storing Personal Data
Office docs may contain some PII (personally identifiable information). If so, you may get errors when trying to save documents (Document Inspector will complain!)
Tuesday 16 February 2010
Mastering MS Word 2007: Footnotes versus Endnotes
References->Insert Footnote. (It's the big button, to the left of Insert Endnote - yes, I know it's confusing). I prefer Footnotes to Endnotes any day.
Monday 15 February 2010
Excel VBA Tips and Tricks
Some random coding tricks of Excel VBA.
Internal Speech Marks
To include speech marks in a string, just precede the "internal" speech mark with another speech mark. Example: s= "Head of the ""Global"" Team".
Internal Speech Marks
To include speech marks in a string, just precede the "internal" speech mark with another speech mark. Example: s= "Head of the ""Global"" Team".
Thursday 11 February 2010
Adding ActiveX Controls to Worksheets in Excel 2007
This is easy. You just need to know where to find the Forms menu. Click on Developer tab, and Insert.
Mark of Experience (Code == Code)
Reading code and not caring about coding style. Used to so many different styles of coding.
Sunday 7 February 2010
Excel Ranges
What is the difference between Range.Value and Range.Value2? They are basically identical except that Value2 doesn't recognise currency or date objects. So dates would be translated into integer values..
The Unofficial Rules of Software Development (Coding)
Technical and Mechanical Rules
So, you're suffering from programmer's block? No worries let the following precepts help you.
1. If you write the interface the implementation writes itself.
2. If you write main() the program follows.
Here are some more maxims to guide and inspire professional software development:
1. Do not be deterred when people desire to see code rather than understanding. Focus on understanding and the code will come.
2. At the same time, do not ignore implementation.
3. Once implemented, reflect. Could I have done things better. What was good about my design, what could be improved. Could I have done the same job FASTER, MORE EFFICIENTLY.
4. Writing properly structured and well-documented code is a great skill.
5. The most satisifying coding sessions are those that start when you are hazy and fuzzy about a subject and progress to a stage of clarity, at least clarity about what you were confused about to start with.
6. Coding sessions through the night is like going through a long, dark tunnel. You need faith and hope to keep you going.
7. Program night or day, summer or winter.
8. Michael Dell: "Celebrate for a nanosecond then move on". There is always an interesting problem waiting to be solved.
9. Creativity, the desire to solve unsolved problems, or to solve problems more efficiently than people of the past, that is what drives programmers.
Freewheeling
How to become freewheeling in programming? Programming should be as easy and straighforward as playing in sand. But to achieve this requires long hours of practice and analysis. Sometimes you get programmers block and nothing seems to work. Consider taking a break, watching television, a mystery drama or some such. Amazingly, after a period of mental relaxation (not sleep but relaxation) the mental block may be removed.
So, you're suffering from programmer's block? No worries let the following precepts help you.
1. If you write the interface the implementation writes itself.
2. If you write main() the program follows.
Here are some more maxims to guide and inspire professional software development:
1. Do not be deterred when people desire to see code rather than understanding. Focus on understanding and the code will come.
2. At the same time, do not ignore implementation.
3. Once implemented, reflect. Could I have done things better. What was good about my design, what could be improved. Could I have done the same job FASTER, MORE EFFICIENTLY.
4. Writing properly structured and well-documented code is a great skill.
5. The most satisifying coding sessions are those that start when you are hazy and fuzzy about a subject and progress to a stage of clarity, at least clarity about what you were confused about to start with.
6. Coding sessions through the night is like going through a long, dark tunnel. You need faith and hope to keep you going.
7. Program night or day, summer or winter.
8. Michael Dell: "Celebrate for a nanosecond then move on". There is always an interesting problem waiting to be solved.
9. Creativity, the desire to solve unsolved problems, or to solve problems more efficiently than people of the past, that is what drives programmers.
Freewheeling
How to become freewheeling in programming? Programming should be as easy and straighforward as playing in sand. But to achieve this requires long hours of practice and analysis. Sometimes you get programmers block and nothing seems to work. Consider taking a break, watching television, a mystery drama or some such. Amazingly, after a period of mental relaxation (not sleep but relaxation) the mental block may be removed.
User "XYZ" is already using Sheet.xls - Open as Read Only?
Sometimes Excel crashes (2007 particularly during OLE operations). Open task manager and kill off all Excel processes then re-open the spreadsheet.
OLE is really broken in Office 2007.
OLE is really broken in Office 2007.
When do you work best as a programmer?
Some people have times of day when they are most productive e.g. late at night, or early in the morning, when they are more free from distractions.
Debugging Excel VBA (F8 is your Friend)
F8 Step Into
Shift-F8 Step Over
The VB interpreter has three states: "NOT RUNNING", "RUNNING", "BREAK". If Excel appears to have frozen, check that the VB interpreter is not hanging in the break state.
Shift-F8 Step Over
The VB interpreter has three states: "NOT RUNNING", "RUNNING", "BREAK". If Excel appears to have frozen, check that the VB interpreter is not hanging in the break state.
Friday 5 February 2010
Excel VBA Runtime Error Handling
Consider the following statement in Excel VBA:
I = 3/2
the answer is either 1 or 1.5, depending on the data type of I (i.e. Integer or Double). If I don't declare I, it will default to Double.
However, if I do
I = 3/0
I will get a message "Runtime Error 11: Division by Zero". How should errors like these be handled? Excel VBA does not have a try-catch-finally yet, so we need to use the built-in error handling mechanism.
I = 3/2
the answer is either 1 or 1.5, depending on the data type of I (i.e. Integer or Double). If I don't declare I, it will default to Double.
However, if I do
I = 3/0
I will get a message "Runtime Error 11: Division by Zero". How should errors like these be handled? Excel VBA does not have a try-catch-finally yet, so we need to use the built-in error handling mechanism.
VLOOKUP, HLOOKUP and other Friendly Excel Functions
Lookup formulas look up values in tables. Think of it as searching for a name in the telephone directory.
Example:
VLOOKUP( B2, TelData, 2, FALSE )
means lookup the value B2 in TelData, and return the 2nd column of TelData, ASSUMING a row has been matched. Setting the fourth argument to FALSE, means find an EXACT MATCH.
Example:
VLOOKUP( B2, TelData, 2, FALSE )
means lookup the value B2 in TelData, and return the 2nd column of TelData, ASSUMING a row has been matched. Setting the fourth argument to FALSE, means find an EXACT MATCH.
Thursday 4 February 2010
Worksheet Headers and Footers in Excel 2007
Click on the Insert menu. Then click "Header and Footer" (button should be next to "Text box"). To make headers/footers visible and invisible go to View menu and toggle between "Normal" and "Page Layout".
Office VBA
Office VBA
Go into VB Editor and hit F2. This gives you the "contents" of each module.
Excel Developer Tab (Design Mode)
What if you are hacking an Excel spreadsheet and want to see the code behind a button? For some reason, the "Design Mode" button is inactive!
Look below the "Ribbon" to see if there is a "Security Warning" saying "active content has been disabled". Click on Options, and 1) Enable Macros and ActiveX, 2) Enable Links.
Go into VB Editor and hit F2. This gives you the "contents" of each module.
Excel Developer Tab (Design Mode)
What if you are hacking an Excel spreadsheet and want to see the code behind a button? For some reason, the "Design Mode" button is inactive!
Look below the "Ribbon" to see if there is a "Security Warning" saying "active content has been disabled". Click on Options, and 1) Enable Macros and ActiveX, 2) Enable Links.
Subscribe to:
Posts (Atom)