Refreshing the credentials after they expire

Once you have a working query, there is a chance that after an hour, Power Query will suddenly start reporting an authentication error. This means that the credential that you created has expired. If you are in the Power Query Editor, select the first query in the Queries bar on the left of the screen. In the Home tab of the ribbon, choose Refresh Preview. Repeat for any additional queries that you have in the Queries bar. If you are asked for the type of credentials, choose Anonymous.

While writing this chapter over the course of two days, I have had the Spotify credentials expire twice. The first time this happened, I was perplexed that perhaps I had mistyped some code. By the time it had happened again, I was confident that I simply needed to Refresh Preview for each query.

Building a custom function in Power Query

In VBA, if you have a bit of code that will be called from multiple procedures, it might be good to put that code in its own custom function. The same concept applies in Power Query.

A custom function in VBA might look like this:

Click here to view code image

Function fnSayHello(name)
 fnSayHello = "Hello " & name
End Function

The similar custom function in M would look like this:

Click here to view code image

(name) as text => 
let
    Result = "Hello " & name
In
    Result

There is a bit of code that will be used in both the SearchArtist and later in the ArtistAlbums and AlbumTracks queries. Right now, before you duplicate SearchArtist to make the ArtistAlbums query, it is time to move the code out to its own function.

Then, right-click on the Queries bar and choose New Query, Other Sources, Blank Query, as shown in Figure 18-5.

Rename the new query as fnGetToken by using the Name box on the right side of the Power Query Editor. Open the Advanced Editor and enter the following code into the code pane:

Click here to view code image

() as text =>
let
    clientId = "YOUR_APP_CLIENT_ID",
    clientSecret = "YOUR_APP_CLIENT_SECRET",
    binaryText = Text.ToBinary(clientId & ":" & clientSecret),
    base64Text = Binary.ToText(binaryText, BinaryEncoding.Base64),
    authCode = "Basic " & base64Text,
    urlAuth = "https://accounts.spotify.com/api/token",
    Source = Json.Document(
        Web.Contents(
            urlAuth,
            [
                Headers=[
                    #"Authorization" = authCode,
                    #"Content-Type" = "application/x-www-form-urlencoded",
                    #"Cache-Control" = "no-cache, no-store, must-revalidate"
                ],
                Content = Text.ToBinary("grant_type=client_credentials")
            ]
        )
    ),
    Token = "Bearer " & Source[access_token]
in
    Token

To make a function, you use the exact same code from the original query plus three additional lines. Figure 18-6 compares the original code and the function.

Using the new function in your code

You want to edit the SearchArtist query. From the Power Query Editor, right-click on the SearchArtist query in the Queries bar and choose Advanced Editor.

Everything after Let and down to Token = "Bearer" can be deleted.

Add a new line that says:

Token = fnGetToken(),

The SearchArtist query is significantly shorter now, as shown in Figure 18-7.

Moving the logic for the Token out to a function that can be called by both SearchArtist and the soon-to-be-created ArtistAlbums and AlbumTracks queries means that your code is easier to maintain. If you later need to change the Client ID or the Client Secret, there is only one place to change it.

Duplicating an existing query to make a new query

Next, it is time to make two new queries to retrieve a list of albums and then a list of songs on the album.

Right-click on SearchArtist in the Queries bar and choose Duplicate. Rename the new query as ArtistAlbums. Edit the duplicated query in the Advanced Editor.

The urlSearch line needs to be changed to use the API endpoint to request albums for a given artist ID. Copy the artist ID for Bruce Springsteen from the query preview and paste into this line of code. Also note that the URL after “v1” now points to artists instead of search:

Click here to view code image

urlSearch = "https://api.spotify.com/v1/artists/3eqjTLE0HfPfh78zjh6TqT/albums",

Later in the query, these two lines of code:

Click here to view code image

Artists = APIResult[artists],
Items = Artists[items],

are streamlined into a single line of code:

Items = APIResult[items],

The list of fields to return changes to:

Click here to view code image

    Result = Table.ExpandRecordColumn(ItemsAsTable, 
        "Column1", {"id", "name", "release_date", "total_tracks"}, 
        {"ID", "Name", "ReleaseDate", "TotalTracks"})

The edited ArtistAlbums query is shown in Listing 18-2.

LISTING 18-2 ArtistAlbums query for Bruce Springsteen

Click here to view code image

let
    Token = fnGetToken(),
 
    // hard-coded for Bruce Springsteen for now
    urlSearch = "https://api.spotify.com/v1/artists/" &
        "3eqjTLE0HfPfh78zjh6TqT/albums",
 
    APIResult = Json.Document(
        Web.Contents(
            urlSearch,
            [
                Headers=[
                    #"Authorization" = Token,
                    #"Content-Type"="application/json"
                ]
            ]
        )
    ),
    // Extract items and convert to table
    // Remove the Artists = as there is one less level to drill through
    Items = APIResult[items],
    ItemsAsTable = Table.FromList(Items, Splitter.SplitByNothing(), null, null,
        ExtraValues.Error),
    Result = Table.ExpandRecordColumn(ItemsAsTable, 
        "Column1", {"id", "name", "release_date", "total_tracks"}, 
        {"ID", "Name", "ReleaseDate", "TotalTracks"})
    in Result

Figure 18-8 shows the query preview for the ArtistAlbums query.

Querying the list of songs on an album

Duplicate the ArtistAlbums query and name the new query as AlbumTracks. The URLSearch for the Letter to You album is:

Click here to view code image

urlSearch = "https://api.spotify.com/v1/albums/5devPxQnSKVF2Ed0CVwQZh/tracks",

The results to return are as follows:

Click here to view code image

    Result = Table.ExpandRecordColumn(ItemsAsTable, 
        "Column1", {"id", "name", "track_number"}, 
        {"TrackID", "SongName", "TrkNumber"})

Figure 18-9 shows the list of songs and the query code.

Simplifying the SearchArtist query to a single line of code

Your goal is to change the SearchArtist, ArtistAlbums, and AlbumTracks queries to a single line of code that can be updated by VBA.

This can be done by moving most of the query logic to three new custom functions. Here are the steps to simplify the SearchArtist query.

Duplicate the SearchArtist query and rename it to fnGetArtists. Edit the code in the Advanced Editor to add a new first line with a parameter. Change the URLSearch line to concatenate the URL with that parameter:

Click here to view code image

(strQuery as text) as table =>
let
    Token = fnGetToken(),
    urlSearch = "https://api.spotify.com/v1/search?query=" & strQuery & 
        "&offset=0&limit=20&type=artist",

With all the logic moved out to the fnGetArtists, you can simplify the SearchArtist query to a single line of code:

Click here to view code image

let Result = fnGetArtists("bruce springsteen") in Result

Your strategy will be to use VBA to update that line of code! In VBA, you can change the code in a query by using the .Formula property:

Click here to view code image

ThisWorkbook.Queries(id).Formula = "let Result = fnGetArtists(""" & strQuery & _
    """) in Result"

You will see this line of VBA in context soon.

But first, you need to simplify the other two functions.

Simplifying the ArtistAlbums query

Duplicate ArtistAlbums and rename it as fnGetAlbums. Open the Advanced Editor. Add a new first line:

(strId as text) as table =>

Change the URLSearch line to:

Click here to view code image

urlSearch = "https://api.spotify.com/v1/artists/" & strId & "/albums",

Close the Advanced Editor. Open the ArtistAlbums query in the Advanced Editor, and it becomes a single line of code:

Click here to view code image

let Result = fnGetAlbums("3eqjTLE0HfPfh78zjh6TqT") in Result

Simplifying the AlbumTracks query

Duplicate the AlbumTracks query and rename it as fnGetTracks. Open the Advanced Editor. Add a new first line:

(strId as text) as table =>

Change the URLSearch line to:

Click here to view code image

urlSearch = "https://api.spotify.com/v1/albums/" & strId & "/tracks",

Close the Advanced Editor. Open the AlbumTracks query in the Advanced Editor, and it becomes a single line of code:

Click here to view code image

let Result = fnGetTracks("5devPxQnSKVF2Ed0CVwQZh") in Result

Grouping queries to clean up the queries list

You now have seven queries in the Queries bar. It is a good idea to clean up the environment to keep the project organized. Power Query allows grouping objects. To group the four functions together, select the first function in the Queries bar. Ctrl+Click the other three function queries.

Right-click and choose Move to Group, New Group, as shown in Figure 18-10.

Power Query asks you for the name for the group. Type Functions and click OK. In a few seconds, the Queries bar will be reorganized, with four functions shown in the Functions group and the remaining three functions grouped into Other Queries.

Group the other three functions into a new group called Results.

Each group can be collapsed or expanded using the triangle next to the group name. Figure 18-11 shows the Functions group collapsed and the Results group expanded.

Planning the arrangement of query results on your dashboard

So far, you have not loaded any of the queries to the Excel grid. Rather than accept the default of three queries loading on three new worksheets, you can plan to arrange them on a single dashboard worksheet.

Close the Power Query Editor by using the “X” in the top-right corner to return to Excel. Excel will ask if you want to keep or discard the changes. Choose Keep. Excel will insert three new worksheets, but you will move the query results from those worksheets to a single Dashboard worksheet.

Find the original worksheet called Sheet1.

Rename this sheet Dashboard.

In cell A1, type My Music Dashboard and apply a style of Heading1.

As shown in Figure 18-12, one arrangement could be to have a button to select the artist in E1:H2. The list of artists could start in A3. The list of albums could start in E4. The list of tracks could start in J5.

The exact location for these queries will have an impact on the VBA that you write, so make a note of where you will be loading the queries.

If you don’t see the Queries and Connections task pane along the right edge of Excel, choose Data, Queries & Connections. Note that the groups that you created in the Power Query Editor are used to organize this task pane as well. You can choose to collapse the Functions group.

Select cell A3 in the dashboard worksheet.

Right-click on SearchArtists and choose Load To…, as shown in Figure 18-13.

In the Import Data dialog, change from Connection Only to Table. The dialog should show Existing Worksheet, and the selected cell of A3 will be in the dialog, as shown in Figure 18-14. Click OK to load the results to A3.

Repeat these steps to load the Albums to E4 and the Tracks to J5.

Each query automatically changes the column widths to fit the longest value. At this point, you don’t need the Queries and Connections task pane, so use the “X” in the top-right corner to close it.

Add a shape to the top of the spreadsheet that can be used to run a macro. Add a label of Select Artist. Right-click the shape and choose Assign Macro.

In the Macro dialog, click New to create a new macro on a new module.

Use the code from Listing 18-3 as the macro for the button.

LISTING 18-3 Code to search for an artist

Click here to view code image

Sub RectangleRoundedCorners2_Click()
Dim id As String
Dim qtSearchArtist As QueryTable
Dim strQuery As String
    
    id = "SearchArtist"
    
    Set qtSearchArtist = ThisWorkbook.Worksheets("Dashboard") _
        .ListObjects(id).QueryTable
    
    If Not qtSearchArtist.Refreshing Then
        strQuery = InputBox("Enter artist name.", "Search Artist")
        If Trim(strQuery) = "" Then
            Exit Sub
        End If
        ' Update the query to search for the typed artist
        ThisWorkbook.Queries(id).Formula = _
            "let Result = fnGetArtists(""" & strQuery & """) in Result"
        qtSearchArtist.Refresh False
        qtSearchArtist.Parent.Cells(1, 1).Select
    End If
 
End Sub

To refresh the ArtistAlbum query table, you will use worksheet’s SelectionChange event instead of a button. So, when you click on an artist ID in columns A:C of the dashboard, it will update the ArtistAlbums query and display the result.

Open the VBA Editor with Alt+F11. In the Project Explorer, double-click on the dashboard worksheet. Use the following macro from Listing 18-4 in the code pane for the dashboard worksheet.

LISTING 18-4 Code on the worksheet code pane

Click here to view code image

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim id As String
Dim ThisArtist As String
Dim ThisAlbum As String
Dim qtArtistAlbums As QueryTable
Dim qtAlbumTracks as QueryTable
 
If Target.Cells.Count = 1 Then
    If Target.Column <= 3 Then
        If Target.Row > 3 Then
            ThisArtist = Cells(Target.Row, 1).Value
            If Trim(ThisArtist) <> "" Then
                id = "ArtistAlbums"
                Set qtArtistAlbums = ActiveSheet.ListObjects(id).QueryTable
                If Not qtArtistAlbums.Refreshing Then
                    ThisWorkbook.Queries(id).Formula = "let Result =" & _
"fnGetAlbums(""" & ThisArtist & """) in Result"
                    qtArtistAlbums.Refresh False
                    Application.Goto Range("E4")
                End If
            End If
        End If
    ElseIf Target.Column >= 4 And Target.Column <= 7 Then
        If Target.Row > 4 Then
            ThisAlbum = Cells(Target.Row, 5)
            If Trim(ThisAlbum) <> "" Then
                id = "AlbumTracks"
                Set qtAlbumTracks = ActiveSheet.ListObjects(id).QueryTable
                If Not qtAlbumTracks.Refreshing Then
                    ThisWorkbook.Queries(id).Formula = "let Result =" & _
"fnGetTracks(""" & ThisAlbum & """) in Result"
                    qtAlbumTracks.Refresh False
                    Application.Goto Range("J5")
                End If
            End If
        End If
    End If
End If
End Sub

When you click on an artist in columns A:C, the albums list automatically updates. Choose an album in the second query table and the track list automatically updates, as shown in Figure 18-15.

This example shows how you can use VBA to manage the queries created in Power Query. It is also an introduction to coding in M, the language of Power Query.

Storing global variables in a Settings record in Power Query

If you have variables that will be used in several queries, you can store them in a Settings record that is created via a query. These can be referred to in any other query.

Create a new query called config and copy and paste the following M code in the Advanced Editor:

Click here to view code image

let
    Settings = [
        apiBase = "https://api.spotify.com/",
        apiVersion = "v1",
        apiSearch = "/search",
        apiArtists = "/artists/{id}",
        apiAlbums = "/albums/{id}",
        recordsPerPage = 50,
        pagesMax = 5
    ] 
in
    Settings

Note that the query is called config. The “Settings” identifier here is simply for readability and the word “Settings” is not used again.

Once you have the config query, any other query can refer to a field in the config query like this: config[recordsPerPage].

Figure 18-16 shows the results of the config query in Power Query.

Simple error handling using try with otherwise

Say that you are returning five items at a time from a web page. You are looping through, returning items 0-4, 5-9, and 10-14. It is possible that an album might only have nine tracks and the final call will fail.

When you know there is a chance that you will get an error, you can handle this using the Try ThingThatMightFail Otherwise SubstituteValue syntax.

The following code is taken, out of context, from the fnGetSinglePage query. The code is trying to refer to the parent of the Record.Field source. In the case where you’ve asked for a page that does not exist, this would return an error. To prevent the query from returning an error, use the try … otherwise null construct to return a null value instead of an error:

Click here to view code image

try Record.Field(Source, parent) otherwise null

Using If logic in M

In VBA, you have If … ElseIf … Else … End If logic. In the M language, you will be assigning the results of the If to a step name. There is no End If needed. Instead, you end the statement with a comma.

Here is the code in VBA:

Click here to view code image

If a = 1 Then
    Data = "one"
ElseIf a = 2 Then
    Data = "two"
ElseIf a = 3 Then
    Data = "three"
Else
    Data = "more than three"
End If

Here is the equivalent code in M. Note that the ElseIf from VBA is two words in M and all the keywords are lowercase:

Click here to view code image

data = if a = 1 then "one"
 else if a = 2 then "two"
 else if a = 3 then "three"
 else "more than 3",

Here is an actual example that combines if, else, and try. This is from the gnGetSinglePage query in the sample workbook.

Click here to view code image

data = if parent is null then
            // If parent is not defined then the column element is already in the
root
            // so we set the data as Source
            Source
        else
            // The parent is defined, so the column element will be 
            // the sub record of the record called as the value of the parent
parameter
            try Record.Field(Source, parent) otherwise null,

Looping using List.Generate

The final construct is to perform the equivalent of a for-next loop from VBA in M. Consider this simple loop in VBA:

Click here to view code image

For i = 1 to 10
cells(i, 1).value = "This is row " & i
Next i

It is possible to do a similar loop in M using List.Generate, but it requires four lines:

• Initial function: () => [i = 0]

  List.Generate starts with an initial function returning a record that contains the control variable field named i, which is equal to 0 initially. This function is executed only once to assign starting values of the variables that will be used in the iteration and return a record. This record is then used in the scope of List.Generate function.

• Condition function: each [i] < 10

  List.Generate checks the condition function value on each iteration and only executes the next iteration if this function returns true. Here we are using i field as the control value that will be incremented in the next function and continue looping while it is less than 10.

• Next function: each [i = [i] + 1]

  This function sets new values for the variables that were assigned in the initial function, which is only the control value held by i field in our code and returned as the updated record to be used in the scope.

• Selector function: each “This is row “ & Text.From([i])

  Finally, this function returns something meaningful from the iteration.

Here is the equivalent loop in M:

Click here to view code image

let
    Result = List.Generate(
        () => [i = 1],
        each [i] < 11,
        each [i = [i] + 1],
        each "This is row " & Text.From([i])
    )
in
    Result

Figure 18-17 shows the result.

For a real-life example, this is a fnGetAllPages function. It uses a List.Generate loop to call fnGetSinglePage repeatedly:

Click here to view code image

(url as text, parent as any, column as text) as list =>
let
    // Using List.Generate function to create a loop in M Code
    AllPagesResult = List.Generate(
            ()=>[i = 0, res = fnGetSinglePage(url, 0, parent, column)],
                each [i] * config[recordsPerPage] <= [res][Total] and [i] <
                    config[pagesMax],
                each [i = [i] + 1, res = fnGetSinglePage(url, ([i] + 1) *
                    config[recordsPerPage], parent, column)],
                each [res][Data]
        )
in
    AllPagesResult

This completes the introduction to the M language. The rest of this chapter switches back to a discussion of using VBA to schedule a macro to run every N minutes.

Using Ready mode for scheduled procedures

The OnTime method runs only when Excel is in Ready, Copy, Cut, or Find mode at the prescribed time. If you start to edit a cell at 7:59:55 a.m. and keep that cell in Edit mode, Excel cannot run the CaptureData macro at 8:00 a.m., as directed.

In the preceding code example, I specified only the start time for the procedure to run. Excel waits anxiously until the spreadsheet is returned to Ready mode and then runs the scheduled program as soon as it can.

The classic example is that you start to edit a cell at 7:59 a.m., and then your manager walks in and asks you to attend a surprise staff meeting down the hall. If you leave your spreadsheet in Edit mode and attend the staff meeting until 10:30 a.m., the program cannot run the first three scheduled hours of updates. As soon as you return to your desk and press Enter to exit Edit mode, the program runs all previously scheduled tasks. In the preceding code, you find that the first three scheduled updates of the program all happen between 10:30 and 10:31 a.m.

Specifying a window of time for an update

You can provide Excel with a window of time within which to make an update. The following code tells Excel to run an update at any time between 8:00 a.m. and 8:05 a.m.:

Click here to view code image

Application.OnTime EarliestTime:=TimeValue("8:00 AM"), _
 Procedure:= "CaptureData ", _
 LatestTime:=TimeValue("8:05 AM")

If the Excel session remains in Edit mode for the entire five minutes, the scheduled task is skipped.

Canceling a previously scheduled macro

It is fairly difficult to cancel a previously scheduled macro. You must know the exact time that the macro is scheduled to run. To cancel a pending operation, call the OnTime method and use the Schedule:=False parameter to unschedule the event. The following code cancels the 11:00 a.m. run of CaptureData:

Click here to view code image

Sub CancelEleven()
Application.OnTime EarliestTime:=TimeValue("11:00 AM"), _
 Procedure:= "CaptureData", Schedule:=False
End Sub

It is interesting to note that the OnTime schedules are remembered by a running instance of Excel. If you keep Excel open but close the workbook with the scheduled procedure, it still runs. Consider this hypothetical series of events:

1. Open Excel at 7:30 a.m.

2. Open Schedule.xlsm and run a macro to schedule a procedure at 8:00 a.m.

3. Close Schedule.xlsm but keep Excel open.

4. Open a new workbook and begin entering data.

At 8:00 a.m., Excel reopens Schedule.xlsm and runs the scheduled macro. Excel doesn’t close Schedule.xlsm. As you can imagine, this is fairly annoying and alarming if you are not expecting it. If you are going to make extensive use of Application.Ontime, you might want to have it running in one instance of Excel while you work in a second instance of Excel.

Closing Excel cancels all pending scheduled macros

If you close Excel with File, Exit, all future scheduled macros are automatically canceled. When you have a macro that has scheduled a bunch of macros at indeterminate times, closing Excel is the only way to prevent the macros from running.

Scheduling a macro to run x minutes in the future

You can schedule a macro to run at a certain time in the future. The following macro uses the TIME function to return the current time and adds 2 minutes and 30 seconds to the time. The following macro runs something 2 minutes and 30 seconds from now:

Click here to view code image

Sub ScheduleAnything()
 ' This macro can be used to schedule anything
 WaitHours = 0
 WaitMin = 2
 WaitSec = 30
 NameOfScheduledProc = "CaptureData"
 ' --- End of Input Section -------
 
 ' Determine the next time this should run
 NextTime = Time + TimeSerial(WaitHours, WaitMin, WaitSec)
 
 ' Schedule ThisProcedure to run then
 Application.OnTime EarliestTime:=NextTime, Procedure:=NameOfScheduledProc
 
End Sub

Later, canceling this scheduled event would be nearly impossible. You won’t know the exact time that the macro grabbed the TIME function. You might try to save this value in an out-of-the-way cell:

Click here to view code image

Sub ScheduleWithCancelOption
 NameOfScheduledProc = "CaptureData"
 
 ' Determine the next time this should run
 NextTime = Time + TimeSerial(0,2,30)
 Range("ZZ1").Value = NextTime
 
 ' Schedule ThisProcedure to run then
 Application.OnTime EarliestTime:=NextTime, _
   Procedure:=NameOfScheduledProc
 
End Sub
 
Sub CancelLater()
   NextTime = Range("ZZ1").value
   Application.OnTime EarliestTime:=NextTime, _
 Procedure:=CaptureData, Schedule:=False
End Sub

Scheduling a verbal reminder

The text-to-speech tools in Excel can be fun. The following macro sets up a schedule that reminds you when it is time to go to a staff meeting:

Click here to view code image

Sub ScheduleSpeak()
 Application.OnTime EarliestTime:=TimeValue("9:14 AM"), _
   Procedure:="RemindMe"
End Sub
 
Sub RemindMe()
 Application.Speech.Speak _
   Text:="Bill. It is time for the staff meeting."
End Sub

If you want to pull a prank on your manager, you can schedule Excel to automatically turn on the Speak On Enter feature. Follow this scenario:

1. Tell your manager that you are taking him out to lunch to celebrate April 1.

2. At some point in the morning, while your manager is getting coffee, run the ScheduleSpeech macro. Design the macro to run 15 minutes after your lunch starts.

3. Take your manager to lunch.

4. While the manager is away, the scheduled macro runs.

5. When the manager returns and starts typing data in Excel, the computer will repeat the cells as they are entered. This is slightly reminiscent of the computer on Star Trek that repeated everything Lieutenant Uhura said.

After this starts happening, you can pretend to be innocent; after all, you have a strong alibi for when the prank began to happen. Here’s the code you use to do it:

Click here to view code image

Sub ScheduleSpeech()
 Application.OnTime EarliestTime:=TimeValue("12:15 PM"), _
   Procedure:="SetUpSpeech"
End Sub
 
Sub SetupSpeech())
 Application.Speech.SpeakCellOnEnter = True
End Sub

Scheduling a macro to run every two minutes

Say that you want to ask Excel to run a certain macro every two minutes. However, you realize that if a macro gets delayed because you accidentally left the workbook in Edit mode while going to the staff meeting, you don’t want dozens of updates to happen in a matter of seconds.

The easy solution is to have the ScheduleAnything procedure recursively schedule itself to run again in two minutes. The following code schedules a run in two minutes and then performs CaptureData:

Click here to view code image

Sub ScheduleAnything()
 ' This macro can be used to schedule anything
 ' Enter how often you want to run the macro in hours and minutes
 WaitHours = 0
 WaitMin = 2
 WaitSec = 0
 NameOfThisProcedure = "ScheduleAnything"
 NameOfScheduledProc = "CaptureData"
 ' --- End of Input Section -------
 
 ' Determine the next time this should run
 NextTime = Time + TimeSerial(WaitHours, WaitMin, WaitSec)
 
 ' Schedule ThisProcedure to run then
 Application.OnTime EarliestTime:=NextTime, _
 Procedure:=NameOfThisProcedure
 
 ' Get the Data
 Application.Run NameOfScheduledProc
 
End Sub

This method has some advantages. It doesn’t schedule a million updates in the future. You have only one future update scheduled at any given time. Therefore, if you decide that you are tired of seeing the national debt every 15 seconds, you only need to comment out the Application.OnTime line of code and wait 15 seconds for the last update to happen.