Some VBA-tips and thoughts on good solutions

  • 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.

    Option Explicit

    Sub Double_Single_Quotations()
    Dim stText As String

    stText = "This is a textstring that includes ""quotations"" which ""we"" need to handle."

    stText = Replace(stText, String(2, 34), """")

    Debug.Print stText

    End Sub
    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

    Option Explicit

    Sub Insert_Date_Time()
    Dim rnSelection As Range

    Set rnSelection = Selection

    With rnSelection
    .Value = Now
    End With

    End Sub
    Step 2 - Hooking the Procedure up to the Shortcut

    Place the following code into the ThisWorkbook-module

    Option Explicit

    Private Sub Workbook_Open()
    Application.OnKey "^+;", "Insert_Date_Time"
    End Sub

    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:

    Option Explicit

    '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
    End Type

    Public pub As Pub_Var

    'This procedure can exist somewhere else in the VB-project.
    Sub Procedure_Work()

    pub.vaData = VBA.Array("Item1", "Item2", "Item3")

    pub.bFlag = True

    pub.stCaption = "A good way to control public variables."

    End Sub
    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.

    The Planning

    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.

    The Purpose

    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.

    The Data

    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
    ISBN 1-59059-432-0

    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.

  • Re: Some VBA-tips and thoughts on good solutions


    Thanks for sharing your experience. Its appreciated.

    I know in my projects I have to guard against putting too much code in a single Procedure instead of modularizing the project. Also, I tend not to document properly. When looking back at old code I could kick myself for not documenting enough of not only the code but all aspects of the project.

    A copied your suggestions and have them pinned to my wall



    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.