Excel

Right Click

2008-11-12T15:13:00Z

Right click mouse events are one of my favorite VBA features. Following is an example of the code needed to add a right click event. You can also download the spreadsheet.

Public Sub addRightClick()

    'Add right click event
    Dim cmd As CommandBar
    Dim cntrl As CommandBarControl
    Const strTag As String = "HelpExcel.com"

    Set cmd = Application.CommandBars("Cell")

    On Error Resume Next
    cmd.Controls(strTag).Delete
    On Error GoTo 0

    Set cntrl = cmd.Controls.Add(msoControlButton)

    cntrl.FaceId = Int(Rnd() * 1000) + 1
    cntrl.Caption = strTag
    cntrl.OnAction = "showDialog"

End Sub

Public Sub showDialog()

    MsgBox "Right click event added by" & vbCrLf & "HelpExcel.com", vbInformation, "HelpExcel.com"

End Sub

Arrays

2008-11-10T22:29:00Z

One of the most powerful features in Excel is the formulaic array. Formulaic arrays allow you to process sets of data as opposed to calculating just a flat formula. Let's say you had a list and you wanted to count the number of items in that list. One of the easiest ways to do this is with a Sum... If... formulaic array.

=sum(if(a1:a100="Item",1,0))

Instead of just pressing enter, you would hold down the control & shift keys and press enter. This would treat the formula as a set and sum up every item in the set, resulting in a count of all cells that contain the word Item in cells A1 to A100.

Common

2008-11-07T15:09:00Z

I learned how to do a For... Next... Loop when I was about 14 years old. Over 20 years later & I am the patron saint of For... Next... Loops. There are many variations on Loops... you've the For/For Each/Do/Do Until & While.

I find myself using some Loop variation constantly. Following is sample code that I use all the time:

Public Sub common()

    Dim rng As Range
    Set rng = Sheet1.Range("a1")

    Do Until rng.Value = ""

        rng.Offset(0, 1).Value = rng.Value
        Set rng = rng.Offset(1)

    Loop

End Sub

Recording Macros

2008-11-05T15:23:00Z

A client called last night wanting to programmatically set the minimum value on his Y axis for an embedded chart. Suprisingly, I remembered most of the object property that I needed to modify, but just couldn't get it all... that's when I did what I always do. Record a macro. This is frequently the best method to explore the Excel Object Model when you are stuck.

Marketing

2008-04-24T09:41:00Z

I received a phone call from a Marketing Team wanting to know how to create a drop down list within a cell. Data Validation is the perfect way to accomplish this.

From the Data menu, select Validation. A screen similar to the following should appear:

Under the Allow: selection, choose List. In the Source: drop down, type in the items you wish to have appear in the cell, separated by a comma. It's that simple.

Interest

2008-04-21T02:44:00Z

I just got off the phone with a customer who wanted to know how to calculate compound interest. There are two easy ways to accomplish this in Excel. First, on a line by line basis, secondly, using exponents.

Line by Line:

Download a copy of the spreadsheet to follow along. Cell A2 contains the amount you are seeding your account with. Cell B2 is 1, the first year of interest. Cell C2 is the interest you expect to earn that year. Cell D2 is the result of multiplying cells A2 and C2, returning the interest in dollars earned. Cell A3 is the result of adding cells A2 and D2, capital plus interest earned. Cell B3 is the result of adding one to cell B2, increasing the year by one. Cell C3 is set to equal cell C2, which assumes the same interest rate every year. Cell D2 can be copied to cell D3 as the formula will remain the same. Copy cell range A3 through D3 down as far as you would like to the see the compound result.

Exponent:
The above can be accomplished using exponents. The above layout is essentially each year compounding, or building off the prior year. The formula to see the result of a multi-period compound effect is 1 plus the interest rate to the power of the number of periods, multiplied by the initial capital. In our example above, we can enter the formula =1.05^20 * 1000 to receive the same result as we did on the line by line basis.

Extraction

2008-04-17T00:49:00Z

The most common question I receive from clients are parsing related. Many people use Excel to compare or extract data from lists. Recently, a client presented me with a workbook that contained many spreadsheets that contained information on their clients. The client wanted a way to extract the email addresses from all the cells containing email addresses within the workbook.

Download the example to follow along.

In the example provided, I created a workbook with two sheets, each containing random numbers and an email address embedded into cells on each sheet.

What we wish to accomplish from a programming perspective is that we want to search through all cells within the entire workbook. The first step is to enter our IDE (accomplished by pressing Alt-F11 on the keyboard) and add a module.

If you are following along, you will notice two modules, one called modAd and one called modSearch. Double click on modSearch, you will see the following code:

Public wbNew As Workbook
Public rngNew As Range

Public Sub search()

    Dim wb As Workbook
    Dim sht As Worksheet
    Dim cl As Range

    Set wb = ThisWorkbook

        For Each sht In wb.Sheets

            For Each cl In sht.UsedRange.Cells

                If InStr(1, cl, "@") > 0 Then

                    'There is an email address in this cell
                    add2NewWB cl.Value

                End If

            Next

        Next

    Set cl = Nothing
    Set sht = Nothing
    Set wb = Nothing

End Sub

Private Sub add2NewWB(strEmail As String)

    If wbNew Is Nothing Then
        Set wbNew = Workbooks.Add
        Set rngNew = wbNew.Sheets(1).Range("a1")
    End If

    rngNew.Value = strEmail
    Set rngNew = rngNew.Offset(1)

End Sub

The public subroutine search initiates and performs the bulk of the work required to cycle through all the cells within the workbook with the following two for... each statements:

For Each sht In wb.Sheets

For Each cl In sht.UsedRange.Cells

The above ensures that every cell will be encountered by our condition, which will test if there is an @ symbol in the cell, thereby cluing us into whether or not there is an email address in the cell:

If InStr(1, cl, "@") > 0 Then

The bulk of the work is done with the three lines of code above. VBA is very easy to learn & extremely powerful, delivering results that can be re-used consistently to save time & effort.

Please leave comments & suggestions on this & future posts. If you have an Excel emergency, you can call me anytime by clicking the Call Now button on the left side of the screen.