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