Welcome to a hopefully new & healthy exciting Excel-year
Unlike previous posts this one will focus on Excel and VBA. At the end of this post, you will find some thoughts about what makes a good solution. The subject as such is quite wide so I decided to try to grab the main aspects of it only.
1. Handle Double Quotations in Textstrings
When working with text, especially with textfiles, we sometimes need to handle the presententation of quotations that surround individual words / expressions.
To better understand the issue take a look on the following:
This is a textstring that includes "quotations" which "we" need to handle.
When saving to a textfile, the quotations will be surrounded with a new pair of quotations:
"This is a textstring that includes ""quotations"" which ""we"" need to handle."
So if we need to read the string again, we can use Excel to remove the double quotations and replace them with single quotations. The the following example shows how this can be done.
Dim stText As String
stText = "This is a textstring that includes ""quotations"" which ""we"" need to handle."
stText = Replace(stText, String(2, 34), """")
The output looks like the following:
This is a textstring that includes "quotations" which "we" need to handle.
2. Improve Built-in Shortcuts
Recently a friend of mine asked if it was possible to improve the keyboard shortcut used to insert the date. He wanted to improve it by adding the time and also filling a selection of cells.
The shortcut Ctrl+Shift+ currently inserts the date based on the Windows regional settings of your computer.
Step 1 - Creating the Procedure in a Standard Module
Dim rnSelection As Range
Set rnSelection = Selection
.Value = Now
Step 2 - Hooking the Procedure up to the Shortcut
Place the following code into the ThisWorkbook-module
Private Sub Workbook_Open()
Application.OnKey "^+;", "Insert_Date_Time"
Of course the procedure should include SEH (structured error handling). It is preferable to save the workbook under the name Personal.xls and place it in the startup folder of Excel. By doing this, you will always have access to the improved shortcut.
3. Enumeration of Public Variables
Although I always recommend avoiding the use of public variables they tend to be part of larger applications.
In order to have them under control we can enumerate them and treat them as parts of a group as the following example shows:
'The following are placed in a standard module within the VB-project:
Public Type Pub_Var
stCaption As String
bFlag As Boolean
vaData As Variant
Public pub As Pub_Var
'This procedure can exist somewhere else in the VB-project.
pub.vaData = VBA.Array("Item1", "Item2", "Item3")
pub.bFlag = True
pub.stCaption = "A good way to control public variables."
4. What makes up a Good Solution?
Many questions and issues that are frequently raised on public forums relate to poor design. Poor design usually refers to the following five basic aspects:
* The lack of planning.
* The understanding of the purpose with the solution in mind.
* The consideration of the interface to be used.
* The understanding and insight about the data that the solution will be handling.
* The technical solution.
There is a tendancy to take shortcuts in the planning stage by starting up the project directly in Excel. Since Excel has no tool for outlining or mind-mapping we still need to use the classic approach with paper and pencil.
[INDENT]#1 Take the time to plan and design the solution with paper and pencil. [/INDENT]A tip is to create a "blue print", take a break, and later go back to it before making a final decision about it. The human brain still needs time to reflect and think unlike computer's CPUs!
For larger projects, I usually map the project on a big board, where I can see the whole project and be able to create a feeling for it as well as see the relation between different parts of the project.
Perhaps the most important aspect is to decide what the solution will actually be targeting at and to be loyal to it. A solution that tries to cover multiple purposes will probably have a great chance of failure.
[INDENT]#2 Decide which problem(s) needs to be solved with the solution and keep that in focus.[/INDENT]The Interface
One of the major mistakes is to try to eliminate the existence of Excel's interface. Don´t spend the time with this activity. After all, if you use Excel then the end-users also expect to recognise the interface. In addition, since you will never succeed to fully eliminate Excel's interface, use the time to focus on more important aspects of the project.
Another major mistake is to add too many controls in userforms, as well as try to solve as much as possible with the "master" form. Take the time to break it down and use several small subforms instead. In the long run it will
also be easier to maintain.
Another important aspect for the interface is to use acceptable size for the controls. Don't use too many colors or too many font types. The built-in forms in Excel should give you a good guidline on how to create workable forms.
[INDENT]#3 Keep the interface simple and clean as much as possible. [/INDENT] There is no reason to try to show the end-users how good you are with Excel from a technical point of view in the interface.
Different kinds of solutions involve different kinds of built-in tools. This means that you must make sure the data is structured in way that fits the tools and not try to do the opposite.
Show only data that is important in different views. Unfortunately many solutions tend to try to cover all available data at the same time. We rarely need all data available at once so there is no need for it.
The Technical solution
Make sure that the solution is bullet-proof, but at the same time don't overwork it. Some replies to questions on public forums are very good from a technical point of view but may be very difficult to implement. Aside from this, the final solution is not dependent on individual good formulas/VBA-solutions, it's the overall performance and reliability that really matters in the end. So instead of trying to implement an advanced solution keep it simple and make sure everything works as expected.
When using VBA make sure to comment special scenarios and partial solutions. We forget more then we can imagine!
[INDENT]#4 Keep it simple and straightforward.[/INDENT]To sum up:
A good design & structure can survive less good technical solutions while good technical solutions never can compensate for poor design and structure.
5. A new book
Paul Cornell have recently published a book about Pivottables, "A Complete Guide to Pivottables", which may be of interest for those of you who want and need more knowledge about this powerful tool in Excel. It cover the subject well, although I wanted more chapters devoted to VBA/VB.NET/VSTO, databases and the Pivottable-component of the OWC. A title that have the word Complete should indeed be complete!
A highly respected regular member at OzGrid, Mr Andy Pope, has been the technical reviewer of the book.
A Complete Guide to Pivottables - A Visual approach
Paul Cornell, Apress 2004
6. The End
The disaster and tragedy of the Tsunami have had a major impact in many people's lives around the world. I sincerely hope that the world will not forget the countries and the people in the area when the disaster is no longer a major headline in the world's news.
This post is dedicated to some of the victims who I really knew well.