# 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:

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```
• 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: