Posted on

Learn to Code with Tas and Excel Lesson 4- Tas3D Zone Writer pt 3

In the last lesson, we modified our zone writer macro to:

  • Check for duplicate zone names
  • Check for existing zone groups and add zones to groups if they already exist

In this lesson, we’ll take a look at performance. So far, our macro works very well for adding a small number of zones to a model but as our model and the number of zones gets bigger, it can take a very long time to run!

Do I really need to worry about performance?

Usually, the time to start worrying about performance is when we try and use our macro and it takes a long time to run. After all, the whole point of writing these macros is to save time! 

Getting a feel for the difference between fast code and slow code is quite useful though, as it you’ll intuitively write fast code from the start and develop an intuition for how to keep things snappy. 

Lets time our macro

Lets modify our existing macro so we can time how long it takes to run. First, lets add a new subroutine called TimeAddZoneNames. We’ll use this function to call our existing function, AddZoneNames, and time how long it took to run:

				
					Sub TimeAddZoneNames()
    
    'Declare some variables to keep track of when we started timing and how much time has elapsed
    Dim StartTime As Double
    Dim SecondsElapsed As Double
    
    'Use the built in 'Timer' function to get the current number of seconds since midnight
    StartTime = Timer
    
    'Call our macro that adds zones to our model
    AddZoneNames
    
    'Call timer again, and calculate the difference in seconds
    SecondsElapsed = Round(Timer - StartTime, 2)
    
    'Display a message showing the time it took to run
    MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
End Sub

We’ll also need to modify our AddZoneNames subroutine to remove the ‘finished’ messagebox at the end. 

Think about it

Why do we need to remove the finished message box from AddZoneNames?

Answer

If we didnt remove the message box, our timer would time how long it takes us to press OK to the Finished message box. We're interested in timing how long it takes to add zones to the 3D modeller file, not how quickly we can press Finished!

Last but not least, we’ll need to change the button we use to run our macro to call our TimeAddZoneNames subroutine:

To do this, right click on the button and press Assign Macro. 

How long does it take to run?

Using the above modifications, i’ve timed how long it takes to run our macro when we’re adding a variable number of zones to the model. Results below. 

When we’re only adding 30 zones, the macro takes 3.5 seconds to run. Pretty good.

When we want to add 200 zones, it takes over a minute. Maybe that’s ok, we can usually spare a minute.

When we want to add 500 zones, it takes over 12 minutes!! This is not good. What if we make a mistake and need to re-run it? that’s almost half an hour of wasted time!

These times are from a fast 4GHz processor – take a moment to see how long it takes to add 200 zones on your machine and see how the times compare. 

Why does it take so long to run?

You might be wondering why this macro takes so long to run when computers can perform billions of calculations every second. Lets time how long a simple operation such as an addition takes:

				
					Sub addMillionTimes()
    Dim i As Long
    i = 0
    While i < 1000000
        i = i + 1
    Wend
End Sub

I timed calling this function, which adds 1 to a variable 1 million times, to see how long it would take to execute. Even with declaring the variable and assigning space for it in the computer RAM, this macro took 0.01 seconds to run!

Now lets compare it to a function which retrieves the building name in a file:

				
					Sub getBuildingNameMillionTimes(doc As TAS3D.T3DDocument)
    Dim i As Long
    Dim name As String
    i = 0
    While i < 1000000
        name = doc.Building.name
        i = i + 1
    Wend
End Sub

This subroutine takes a jaw dropping 46 minutes to run! Why? Because when we use a type library to control another application, the Windows operating system has to perform many security checks each time we call a function belonging to that library. Therefore, if we want to write fast macros, we need to reduce any unnecessary type library function calls.

In the case of our existing macro, every time we add a zone to the file we need to check every single zone in the file to see if it already exists. If we have 10 zones in the file and we want to add another, we have to check 10 zones before we can add a new one. A checking operation involves getting a reference to a Zone object then reading its name (3 operations). 

If we have 499 zones in our file, we have to check 499 zones names before we can add another one. 

But what if we could check each of the zone names once and remember the result, so that next time we want to add one we can save a lot of time? 

Dictionaries

Fortunately, we can use an object called a Dictionary in order to create a lookup table in the computers memory of zone names and zone references. 

Before we start adding zones to our file, we can read all the zones in the 3D modeller file once and add them to our lookup table. 

				
					Function CreateZoneDictionary(doc As TAS3D.T3DDocument) As Scripting.Dictionary
    Dim lookup As Dictionary
    Set lookup = New Scripting.Dictionary
    Dim curZone As TAS3D.Zone
     
     
    Dim curZoneIndex As Integer
    curZoneIndex = 0
    
    While Not doc.Building.GetZone(curZoneIndex) Is Nothing
        
        'Get a reference variable for the current zone
        Set curZone = doc.Building.GetZone(curZoneIndex)
        
        lookup.Add curZone.name, curZone
            
        
        'incremenet the zone index so we look at the next one in the file when the loop repeats
        curZoneIndex = curZoneIndex + 1
    Wend
    
    Set CreateZoneDictionary = lookup
    
End Function

Note that in order to use Dictionaries, you need to reference the Microsoft Scripting Runtime via Tools > References.

This function creates a dictionary on line 3, and then iterates over every zone in the file. On line 15, it stores the zone name as the dictionary key and a reference to the zone as the value in the dictionary. 

We can therefore use the dictionary to very quickly retrieve a reference to a zone using its name. Using this dictionary, we can re-write our ZoneExists function:

				
					Function ZoneExists(name As String, lookup As Scripting.Dictionary) As Boolean
    
  ZoneExists = lookup.Exists(name)
   
End Function

Looking up a value in a dictionary based on its key is extremely fast, as no type library function calls are required. 

Putting it all together

The complete macro, with the changes highlighted, can be seen below. I have also created a dictionary for zoneSets in order to speed up checking of zone sets exist already, and adding zones to them. 

				
					Sub TimeAddZoneNames()
    Dim StartTime As Double
    Dim SecondsElapsed As Double
    
    StartTime = Timer
    
    AddZoneNames

    SecondsElapsed = Round(Timer - StartTime, 2)
    
    MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
End Sub




Sub AddZoneNames()
    'Read the file path from the spreadsheet
    Dim filePath As String
    filePath = Cells(1, 5)

    'Open the 3D modeller
    Dim t3dApp As TAS3D.T3DDocument
    Set t3dApp = New TAS3D.T3DDocument

    'Declare a variable for checking if operations were successful
    Dim ok As Boolean

    'Try to open the existing file
    ok = t3dApp.Open(filePath)

    'Check if it did open successfully
    If Not ok Then
        MsgBox "Couldnt open the file; is it in use?"
        Exit Sub
    End If
    

    'Define our loop variables
    Dim rowIndex As Integer
    Dim zoneName As String
    Dim zoneSetName As String
    Dim newZone As TAS3D.Zone
    Dim zoneSet As TAS3D.zoneSet

    'Set the starting rowIndex
    rowIndex = 2
    
    'lookups
    Dim zoneLookup As Dictionary
    Dim zoneSetLookup As Dictionary
    Set zoneLookup = CreateZoneDictionary(t3dApp)
    Set zoneSetLookup = CreateZoneSetDictionary(t3dApp)
    

    'Check each zone name cell to see if it contains something
    While Not IsEmpty(Cells(rowIndex, 1))

        'Read the zone name from excel
        zoneName = Cells(rowIndex, 1)
        
        'Read the zone set name from excel
        zoneSetName = Cells(rowIndex, 2)

        'Check that the zone name isnt already in use
        If ZoneExists(zoneName, zoneLookup) Then
            Cells(rowIndex, 3) = "Skipped"
        Else
            'Check to see if there is a zone set already in the file with the right name
            Set zoneSet = GetZoneSet(zoneSetName, zoneSetLookup)
            
            'If it doesnt exist, make a zone set wtih that name
            If zoneSet Is Nothing Then
                Set zoneSet = t3dApp.Building.AddZoneSet(zoneSetName, "", 0)
                
                'Add it to the dictionary
                zoneSetLookup.Add zoneSetName, zoneSet
            End If
            
            'Add a zone to the zone set
            Set newZone = zoneSet.AddZone()
            
            'Change the name of the zone we just added
            newZone.name = zoneName
            
            'Add it to the dictionary
            zoneLookup.Add zoneName, newZone
            
            'Note the addition was a success
            Cells(rowIndex, 3) = "added"
            
        End If



        'Increment the row index (so we read the next cell down)
        rowIndex = rowIndex + 1

    Wend

    'Save the file and check the save was successful
    ok = t3dApp.Save(filePath)

    If Not ok Then
        MsgBox "Couldn't save the file!"
        Exit Sub
    End If

    'Close the file
    t3dApp.Close

    'Close the 3D modeller
    Set t3dAppp = Nothing


End Sub

Function CreateZoneDictionary(doc As TAS3D.T3DDocument) As Scripting.Dictionary
    Dim lookup As Dictionary
    Set lookup = New Scripting.Dictionary
    Dim curZone As TAS3D.Zone
     
     
    Dim curZoneIndex As Integer
    curZoneIndex = 0
    
    While Not doc.Building.GetZone(curZoneIndex) Is Nothing
        
        'Get a reference variable for the current zone
        Set curZone = doc.Building.GetZone(curZoneIndex)
        
        'Add the zone to the dictionary, using its name as the key
        lookup.Add curZone.name, curZone
            
        
        'incremenet the zone index so we look at the next one in the file when the loop repeats
        curZoneIndex = curZoneIndex + 1
    Wend
    
    Set CreateZoneDictionary = lookup
    
End Function

Function CreateZoneSetDictionary(doc As TAS3D.T3DDocument) As Scripting.Dictionary
    Dim lookup As Dictionary
    Set lookup = New Scripting.Dictionary
    Dim curSet As TAS3D.zoneSet
     
     
    Dim curZoneSetIndex As Integer
    curZoneSetIndex = 0
    
    While Not doc.Building.GetZone(curZoneSetIndex) Is Nothing
        
        'Get a reference variable for the current zone
        Set curSet = doc.Building.GetZoneSet(curZoneIndex)
        
        'Add the current zone set to the dictionary, using its name as the key
        lookup.Add curSet.name, curSet
            
        'incremenet the zone index so we look at the next one in the file when the loop repeats
        curZoneSetIndex = curZoneSetIndex + 1
    Wend
    
    Set CreateZoneSetDictionary = lookup
    
End Function

Function ZoneExists(name As String, lookup As Scripting.Dictionary) As Boolean
    
  ZoneExists = lookup.Exists(name)
    
End Function

Function GetZoneSet(name As String, lookup As Scripting.Dictionary) As TAS3D.zoneSet
    
    If lookup.Exists(name) Then
    
        Set GetZoneSet = lookup(name)
        
    Else
    
        Set GetZoneSet = Nothing
        
    End If
  
End Function

As we have already discussed how dictionaries work, we wont go through this macro line by line – hopefully the comments will be enough to explain what’s going on, along with your experience from the lessons so far. 

How much time did we save?

The amount of time we saved by using dictionaries might shock you. 

To add 1,000 new zones using dictionaries took only 2.16 seconds. With the old version, it took over 12 minutes to add half that number!

Details: Dictionaries

Dictionaries are common in most programming languages, but sometimes they go by other names such as ‘Hash Maps’. A dictionary is an object that can store a number of pairs of items. These pairs consist of a Key and a Value.

The Key can be of any type, as can the Value. 

Each key in a dictionary must be unique — that is, you cant have the same key in there multiple times!

If you’re still unsure what a dictionary is, you can think of it as a kind of lookup table. Imagine you had a table of council tax bands and the prices you’d pay for each band:

Band Price
A £20,000
B £30,000
C £43,000
D £51,000

Here, the band is the key and the price is the value. If you wanted to find out the price for being in a band, you’d look it up in the table. 

Dictionaries have functions that allow you to:

  • See if there is an entry in a dictionary for a key already (dictionary.exists)
  • Add items (dictionary.add)
  • Remove items (dictionary.remove)

For more information about dictionaries, see the Excel Macro Mastery topic on the subject. 

Next Lesson

In this lesson we’ve learned something very important – that we should try to call certain type libraries functions as infrequently as possible in order to write fast macros. We’ve also looked at one way in which we can achieve this – by calling these functions once and storing the result to use later, so we don’t have to check again.

In the next lesson, we’ll move on from our zone writer macro and write one to perform some daylight calculations in the 3D modeller. We’ll also learn how to read the lux values for each point and write them to our spreadsheet. 

Posted in Uncategorized.