Tag Archives: Function

Excel VBA macro example: How to count concurrent times

Here is an example VBA macro for solving a call log analysis problem:

We have call log

  • Column A: Date of the call
  • Column B: Time of the call
  • Column C: Duration of the call

How many concurrent calls?

Following screen capture demonstrates the situation:

Screen Shot 2013-02-12 at 11.54.03

To solve the problem I implemented Visual Basic macro ConcurrentCalls() and helper functions max(), min() and isConcurrent(). Here is the code:

Function max(a, b)
  If a > b Then
    max = a
  Else
    max = b
  End If
End Function

Function min(a, b)
  If a > b Then
    min = b
  Else
    min = a
  End If
End Function

Function isConcurrent(start1, end1, start2, end2)
  If max(start1, start2) <= min(end1, end2) Then
    isConcurrent = True
  Els
    isConcurrent = False
  End If
End Function

Function ConcurrentCalls()
    maxconcurrent = 1
    r1 = 2
    Do
        concurrent = 1
        r2 = 2
        Do
            start1 = Cells(r1, 1).Value + Cells(r1, 2).Value
            end1 = start1 + Cells(r1, 3).Value
            start2 = Cells(r2, 1).Value + Cells(r2, 2).Value
            end2 = start2 + Cells(r2, 3).Value
            If r1 <> r2 And isConcurrent(start1, end1, start2, end2) Then
                concurrent = concurrent + 1
            End If
            r2 = r2 + 1
        Loop Until Cells(r2, 1) = ""
        maxconcurrent = max(maxconcurrent, concurrent)

    Loop Until Cells(r1, 1) <> ""
    ConcurrentCalls = maxconcurrent
End Function

Feel free to examine and let me know if you see a bug or can imagine a better solution!

Check also 101 Excel Secrets and Everyday Excel video course.

How to use Excel’s VLOOKUP-function

VLOOKUP-function is a great help when you need to find data from the large tables. With VLOOKUP you can search for a value in the first column of the table and return a value in the same row from the column you specify. VLOOKUP has three mandatory parameters and one optional parameter:

  1. parameter: Value to search in the first column
  2. parameter: Table to search from
  3. parameter: Index of the column from which the matching value must be returned. The index of the first column is 1
  4. optional parameter: If omitted or TRUE, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will only find an exact match.
The following video demonstrates a simple example how to use VLOOKUP:

How to use SUMIF()-function

Excel’s SUMIF()-function is handy function for summing items matching specified criteria.
The syntax of the SUMIF is

SUMIF(range, criteria, [sum_range])

  • range: range of the cells to apply the criteria against
  • criteria: determines which cells to add
  • sum_range: this is optional parameter specifying which cells to sum. If omitted, the range is used as the sum_range

Following video demonstrates the usage of SUMIF:

How to write Excel VBA function

If there is no proper function for your purposes, you can always write one by yourself. All you need is a basic understanding of Basic-programming. It is not hard!

In this demonstration we implement macro function for reversing text (e.g. “help”–>”pleh”). Following steps are needed:

  • Add Developer tab to Excel Ribbon bar:
    Excel 2011 for Mac: Select click gear in the Ribbon’s right edge and select Ribbon Preferences…
    Excel 2010 for Windows: Right clickon the Ribbon and select Customize Ribbon…

    –> Make sure that the Developer-Tab is selected in the Tab list
  • Select Developer-Tab
  • Start Excel’s Visual Basic -editor
  • Add new module by opening context menu from VBAProject and selecting Insert –> Module
  • Write following code to Module1
    Function TransposeText(txt)
        Dim returntxt
        returntxt = ""
        For i = Len(txt) To 1 Step -1
            returntxt = returntxt + Mid(txt, i, 1)
        Next i
        TransposeText = returntxt
    End Function
  • Now your function is ready to use
  • Go back to the worksheet and test the new function
Here is the video demonstrating this using Excel 2011 for Mac:

How to use Excel Conditional Formatting to highlight past dates

Excel has very powerful feature called conditional formatting. In this post I will demonstrate how to use the feature to automatically highlight past dates in different way than future dates.

  • I use TODAY()-function to get current date
  • I set conditional formatting so that when the value of the cell is less than TODAY(), it will be formatted

Following video demonstrates this:

How to use Excel’s SUMPRODUCT()-function

An easy way to sum products of corresponding numbers in several arrays is to use Excel’s SUMPRODUCT()-function. It takes one or more arrays as parameters and calculates sum of products.
Following video demonstrates first the traditional way to do that and then same by using SUMPRODUCT()-function: