Tag: excel

Get Travel Time and Distance in Excel Using Google Maps Routes API

A few years ago, I made a tutorial on this website and also published to YouTube showing you how to create a function to calculate travel time and distance using the Google Maps API. That remains the most popular tutorial on this website. Google is in the process of retiring its Directions API, which is replaced with a new API called the Routes API. The new API is very similar, and we can use it to get travel time and distance in Excel just as we could with the previous API. This time, rather than using VBA, I’m going to show you how to do this with PowerQuery. I think PowerQuery is a bit more friendly for the average user, although in this case we are still going to need to do a bit of coding which you can simply copy and paste.

Continue reading “Get Travel Time and Distance in Excel Using Google Maps Routes API”

Add Change Case Buttons to the Microsoft Excel 365 Ribbon

If you work with both Microsoft Word and Microsoft Excel, you may have noticed that Microsoft Word has a “change case” button in the Font section of the ribbon which allows you to quickly capitalize or lowercase sections of your document. While Excel does not come with such a button by default, it is possible to add buttons to set the case of the highlighted cells.

Step 1: Add New Macros to Your Personal Macro Workbook

The personal macro workbook allows you to store macros that you would like to use in multiple workbooks. Once we’ve added macros to change case to the personal macro workbook, we can add buttons for them on the ribbon. Ensure the developer tab is enabled on the ribbon. Then, to make sure you have a personal macro workbook, go to Developer > Record Macro and record a macro. Stop recording, and open the Visual Basic editor from the same tab. In place of the recently recorded macro, add the following code:

Sub UpperCase()
'
' UpperCase Macro
'

'
    Dim cell As Range
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains text
        If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
            cell.Value = UCase(cell.Value) ' Capitalize the text
        End If
    Next cell
End Sub

Sub LowerCase()
'
' LowerCase Macro
'

'
    Dim cell As Range
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains text
        If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
            cell.Value = LCase(cell.Value) ' Lower case the text
        End If
    Next cell
End Sub

Sub ProperCase()
'
' ProperCase Macro
'

'
    Dim cell As Range
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains text
        If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
            cell.Value = StrConv(cell.Value, vbProperCase) ' Proper case the text
        End If
    Next cell
End Sub

Step 2: Add Change Case Buttons to the Ribbon

Right click the ribbon, and click “Customize the Ribbon…”. On the left, select Macros and move the macros into the desired area of the ribbon. Use the “Rename…” button to name the buttons and select an icon if desired.

That’s it! Because these macros are associated with your personal macro workbook, you can use the new buttons any time you are using Excel.