Learn to Code with Tas and Excel Lesson 2 – Tas3D Zone Writer

Learn to Code with Tas and Excel Lesson 2 - Tas3D Zone Writer

In the last lesson of this series, we learned how to setup our machine to write macros, and we wrote our first simple Excel macro. Our first macro:

  • Opened an instance of the 3D modeller
  • Created a new 3D modeller document
  • Changed the building name
  • Saved & closed the file

In this lesson, we’ll write a macro that can read zone names from an excel spreadsheet and add them to an existing 3D modeller file. Specifically, we’ll:

  • Open an instance of the 3D modeller
  • Open an existing 3D modeller document
  • Add new zones to the file with names read from the spreadsheet
  • Save & close the file. 

We’ll then modify our macro to create zone groups for the new zones, and we’ll also add the option to read out the existing zones list from the 3D modeller file. 

As promised in the last post, we’re building on the things we learned already. So lets dive in. 

Part 1: Adding zone names to Tas3D files

Before you begin...

To begin, we’ll need a new 3D modeller file. Just open the 3D modeller and save the blank file to your desktop

We’ll also need a list of zone names in excel:

 

These zone names can be whatever you like, but I’ve added a column header of ‘Zone Names’ in cell A1 so I can identify which data is in that column. I have also added the file path to the Tas3D file we wish to operate on in cell E1. 

The script

As with last time, we’ll look at the script as a whole before breaking down each part. Have a read of it and see if you can understand what’s going on before we go into detail:

				
					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 newZone As TAS3D.Zone
    
    'Set the starting rowIndex
    rowIndex = 2
    
    '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)
        
        'Add a zone to the default zone group
        Set newZone = t3dApp.Building.GetZoneSet(1).AddZone()
        
        'Change the name of the zone we just added
        newZone.Name = zoneName
        
        '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
        
    'Message box, so we know when it's done running
    MsgBox "Finished!"
    
End Sub

				
			

Lots going on here! We’ve started using primitive variables,  return values from functions, while loops, if statements and control statements. But as always, we’ll break it down slowly and go into more detail about each of these topics at the end of the post. 

We start by reading the file path from the spreadsheet:

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

On line 2, we dimension a variable of type string (a variable that can hold text), and on line 3 we set its value equal to whatever is in the cell with row index 1 and column index 5. This corresponds to cell E1. Note that we didn’t need to use the set keyword — this is because the filePath variable is a primitive type. We only use the set keyword when we’re working with objects. 

Why did we do this? We did this because if we give our macro to a colleague or are using it a lot, its inconvenient to keep opening the code editor and having to find and change the line of code that determines which file we’re adding zone names to. We didn’t have to, but it makes things easier! And less intimidating for our colleagues.

We also need the file path when we open the file and save the file, so by reading it from the spreadsheet and storing it in a variable, we only have one place to change the file path when we’re working with a different file. Very neat. 

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

Next we dimension a new reference variable to control an instance of the 3D modeller, and then on line 3 we create the new instance using the new keyword and assign it to the t3dApp reference variable using the = operator and the set keyword. 

We’re almost ready to use the TAS3D.T3Document.Open function to open an existing 3D modeller file, but before we do, if we look at the function in the object browser we can see that it returns a value of type boolean:

A boolean is a primitive variable type which can be one of two values: True or False. What then, does it mean if the Open function returns True or False? Tas3D returns True when the open function is successful and false when the function is not successful.

For example, if you’re trying to open a file and the file is missing, (the file path could be invalid), the Open function will return False. This will also happen if the file is in use by another application or another instance of the 3D modeller. If the operation is successful, the function returns True.

In order to check the return value of this function, we’ll dimension a boolean variable called ‘ok’ to store the value the Open function returns and we’ll check to see what that value is after we have tried to Open the file:

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

Now we can call the Open function, storing the functions return value in the ok variable:

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

Here, we have passed in the filePath variable we declared earlier and set to contain the value of cell E5. If cell E5 was empty, the Open function would return False, so it would be useful to check the result and exit our macro early if we couldn’t open the file. After all, there’s no point reading the zone names from the spreadsheet if we cant open the file we want to save them in!

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

To check the value of the ok variable, we are using an if statement. We are saying if the value of ok is Not True, display a message box and exit our subroutine early. 

If statements are extremely useful as they control the flow of our macro based on conditions; for more information see the details box at the end of this post.

				
					 'Define our loop variables
    Dim rowIndex As Integer
    Dim zoneName As String
    Dim newZone As TAS3D.Zone
    
    'Set the starting rowIndex
    rowIndex = 2
    
    '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)
        
        'Add a zone to the default zone group
        Set newZone = t3dApp.Building.GetZoneSet(1).AddZone()
        
        'Change the name of the zone we just added
        newZone.Name = zoneName
        
        'Increment the row index (so we read the next cell down)
        rowIndex = rowIndex + 1
    
    Wend
				
			

Now to the heart of our macro. Remember what we’re trying to do. We need to:

  • Loop through each row that could contain a zone name and see if its empty
  • If there’s a zone name in that cell, store it in a variable
  • Add a new zone to the Tas3D file
  • Set the name of the new zone to be the one we read from the non-empty cell

In order to keep track of which row we’re currently looking at, we define a primitive variable rowIndex. On line 7, we set its initial value to 2 as row 1 column 1 contains the header Zone Name, and we dont want to accidentally create a zone name called Zone Name!

We also want a reference variable to control our new Zone object that we’ll create; we do so on line 4, and we dimension a primitive string variable to store the zone name we’ve read from the sheet. 

Line 10 is a While Loop. While the condition is true, the code between line 10 and the Wend on line 24 will repeat in order. 

First the while loop condition is checked; there is text in Cells(2,1), so we move onto line 13. 

On line 13 we read the zone name in Cells(2,1) and store it in the variable zoneName.

On line 16 we add a new Zone object to the first zone set in the Building object. We access the first zone set by using the GetZoneSet() function belonging to Building. If you look in the object browser, you’ll see its return type is TAS3D.ZoneSet, which has the function AddZone

On line 19 we change the Name of the Zone object to be the zone name we read earlier.

Next, we increment the rowIndex by 1, so its value goes from 2 -> 3. The while loop then repeats and checks to see if Cell(3,1) is empty.

As I only have 8 zone names in my spreadsheet, when rowIndex = 10, cells(10,1) will be empty and the while loop will stop executing. 

It this seems a bit confusing, watch the video accompanying this post as its easier to walk through each line with an explanation. We can even use the debugger in the visual basic environment to walk through each line one at a time to understand what’s happening, which I’ll demonstrate in the video. Also don’t forget to checkout the additional details at the bottom of this post, regarding while loops!

				
					'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
        
    'Message box, so we know when it's done running
    MsgBox "Finished!"
				
			

By now you should be able to follow whats happening here; we use the ok variable we declared earlier to check to see whether the Save function executed successfully, and if it doesnt, we display a message and exit our macro early.

On line 10 we close the file, and then on line 13 we disconnect the reference variable that controls the 3D modeller instance from the object itself so Excel closes the 3D modeller instance for us.

We then display a helpful message box so we can tell when our macro has finished running. Though our macros so far have ran very quickly, if we had thousands of zone names in our spreadsheet it could take a little while!

What about zone sets?

Ok so far we’ve relied on the fact that our existing Tas3D file has a default zone set, and we’ve just added our new zones to that existing zone set. 

If we wanted to add our zones to a brand new zone set, we could modify our Macro as follows:

				
					Sub AddZoneNamesNewZoneSet()
    '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
    
    'Create a new zone set to store our zones in
    Dim newZoneSet As zoneSet
    Set newZoneSet = t3dApp.Building.AddZoneSet
    newZoneSet.Name = "My new zone set"
    
    'Define our loop variables
    Dim rowIndex As Integer
    Dim zoneName As String
    Dim newZone As TAS3D.Zone
    
    'Set the starting rowIndex
    rowIndex = 1
    
    '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)
        
        'Add a zone to the default zone group
        Set newZone = newZoneSet.AddZone()
        
        'Change the name of the zone we just added
        newZone.Name = zoneName
        
        '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
        
    'Message box, so we know when it's done running
    MsgBox "Finished!"
    
End Sub
				
			

If you look at lines 23-25, you’ll see we first dimension a new ZoneSet reference variable so we can refer to our new zone set. We then create one by using the AddZoneSet function that belongs to the Building object

We then use that newZoneSet on line 42 and add the new zones directly to it. 

Question

What do you think would happen if we kept running our macro on the same Tas3D file?

Answer

The macro would keep adding zones with the same name to the 3D modeller file!

Details: If Statements

If statements control the flow of our macros and allow us to react differently to different situations. They take the form:

				
					If [condition] then
    'code for if its true
End if
				
			

This if statement executes the code between lines 1 and 3 if the condition is true. We can also execute different code if the condition is false:

				
					If [condition] Then
    'code if true
Else 
    'code if false
End If
				
			

We can also check multiple conditions in a single If Statement:

				
					If [condition 1] Then
    'code
Else If [condition 2] Then
    'code
Else If [condition 3] Then
    'code
End if
				
			

We can have as many ‘Else If’ statements as we like. 

The [condition] placeholders are statements that evaluate to a value of either  True or False. For example:

				
					'Example 1
Dim x as Integer
x = 4

If x = 4 Then
    'x is 4
Else
    'x is not 4
End If

'Example 2
If (1 + 5) = 6 Then
    'The condition is true
End If

'Example 3
Dim booleanValue as Boolean
booleanValue = True

If booleanValue Then
    'the booleanValue is true!
End If
				
			

We can also use the Not keyword to evaluate whether a condition evaluates to False (false is equivalent to ‘not true’):

				
					Dim x as Integer
Dim y as Integer
x = 4
y = 4

If Not x = y Then
    'The variables are not equal
End If
				
			

To learn more about If Statements, see ExcelEasy

Details: While Loops

While loops allow us to repeat a section of code while a condition is true. Remember, a condition is just a statement that evaluates to either True or False

				
					While [condition]

    'Some code

Wend
				
			

If the condition evaluates to False by the time we encounter the loop, the code in the loop will not be executed. 

If the condition is true, usually the code within the while loop has the ability to cause the condition to eventually evaluate to False, otherwise the loop would repeat forever!

				
					Dim x as Integer
x = 1

While x < 10            ' < is the less than operator
    
    'Write the value of x to the sheet
    Cells(x,1) = x
    
    'Increment the value of x
    x = x + 1
    
Wend
				
			

This snippet writes the numbers 1 to 9 to the current worksheet. The condition on line 4 gets evaluated every time the loop repeats, so as line 10 alters the value of x, eventually x will be equal to 10 and therefore the condition will be false and the code will stop repeating. 

There are a few different types of loops; there are For loops, While loops and Do While loops. For more information about loops, see ExcelEasy

Whats next?

Next time we’ll look at how we can modify our macro to dynamically add zones to different zone groups based on the contents of our excel spreadsheet, rather than just adding them all to the same spreadsheet. 

We’ll also keep an eye out for duplicate zone names. Click here to go to the next lesson.

Learn to Code with Tas and Excel Lesson 1 – Setup

Learn to Code with Tas and Excel Lesson 1 - Setup

In this post, I’ll run through how to get ready to write your first Excel macro. If you haven’t already, I suggest you read the intro post to find out more.

With each post in this series, there will be a written explanation and a video demonstrating what to do. 

1. License Tas

It might seem obvious, but please make sure your copy of Tas Engineering is licensed via the Register button in the Tas Manager. 

If your copy of Tas isn’t licensed properly, your Macro might behave strangely and it can lead to some very hard to track down bugs. 

2. Enable Excel developer Tab

When we’re writing macros in Excel, we do so in the Visual Basic for Applications window. The button to open this is hidden away in the Developer tab in the ribbon, which isn’t visible by default.

 

To enable it, click on File > Options > Customise Ribbon, and check the Developer checkbox.

 

The developer tab also allows us to run our macros, and add buttons to our spreadsheet to make running them quicker and easier.

 

Once you’ve enabled this tab, it’ll should always be visible in your ribbon.

And thats it! That’s pretty much all you need to do. Now you just need to open the VBA editor and write your first macro!

Writing your first macro

Add a new module

To open the Visual Basic editor, click on the Developer tab in the ribbon and click on the Visual Basic button. The VBA editor will pop up, and then you just need to add a module to start writing code:

Insert module into excel workbook

If all goes according to plan, you’ve created your first module and you’ll be presented with a white document in which you can type code. In case you’re wondering what a module is, it;s just a way to organise different bits of code and group them together

At this point you can start writing macros, but they wont be able to use Tas yet. To automate Tas, you’ll need to reference the appropriate type libraries.

 

Reference Tas Type Libraries

The Tas type libraries are files that tell the excel programming language what you can do when interacting with the Tas software.

To reference the Tas3D type library, click on Tools > References in the VBA editor, and select Tas3D:

 

If you are writing a macro that operates on TBD files or TCD files, you’ll need to pick those type libraries instead. The full list is:

  • 3D Modeller – Tas3D
  • Building Simulator – TBD
  • Constructions Database – TCD
  • Calendar Database- TCR
  • Internal Conditions Database – TIC
  • Weather Database – TWD
  • Systems – TPD
  • Ambiens – TAI
The Object Browser

After you’ve referenced the Tas type libraries, you can see what functions are available for a type library using the Object Browser. To open it, just press F2 on the keyboard or click on View > Object Browser:

 

Hello, Tas3D!

Lets write some code. Copy and paste the below code into your new module. You’ll need to change the file path to a folder on your computer.

 

				
					Sub HeloTas3D()
    
    'Open a new instance of the 3D modeller
    Dim t3dApp As TAS3D.T3DDocument
    Set t3dApp = New TAS3D.T3DDocument
    
    'Create a new document using the instance we just opened
    t3dApp.Create
    
        
    'Change the building name to "Hello, World!"
    t3dApp.Building.Name = "Hello, World!"
    
    'Save the 3D modeller file. YOU NEED TO CHANGE THIS LINE!
    t3dApp.Save ("C:\users\hilmya\desktop\MyNewFile.t3d")
    
    'Close the file
    t3dApp.Close
    
    'Close the 3D modeller instance
    Set t3dApp = Nothing
        
    'Message box, so we can tell when its finished
    MsgBox "Finished!"
End Sub

				
			

To run your macro, press F5 on your keyboard. The macro will run, and you should get a new Tas3D file created at the location you specified in line 15 of the previous code snippet and you should get a message box pop up saying “Finished” if everything ran OK.

 

If you open the Tas3D file that was created when you ran your Macro and open the Building dialog (ctrl + B), you should see the phrase ‘Hello, World!’ in the building name field.

 

Rememebr, if you want to re-run your macro, you’ll need to delete the 3D modeller file first.

I don't understand the code!

Now that we’ve written and ran our first macro, lets go through each part of the Macro to understand whats going on in a bit more detail. We’ll start with creating a new instance of the 3D modeller:

				
					'Open a new instance of the 3D modeller
    Dim t3dApp As TAS3D.T3DDocument
    Set t3dApp = New TAS3D.T3DDocument
				
			

Line 1 is a comment – comments start with the apostrophe character, and allow us to leave notes to ourselves and our colleagues to help them understand the purpose of that section of code. Comments are really useful, as they can help us understand our intentions when we come back and review the code later.

Line 2 ‘Dimensions’ a new object reference variable called t3dApp with type TAS3D.T3Document.  Variables represent data which can change throughout the execution of your program, and there are two broad types – primitive variables and object reference variables. We’ll discuss these more in the next section.

For now, you can think of a reference variable like a remote control – we can use the remote control to control an object; in this case, an instance of the 3D modeller is the object.

Line 3 actually creates a new instance of the 3D modeller, and links up this new instance to the ‘remote control’ reference variable we created on the previous line.

You said I opened a new instance of the 3D modeller, but where is it? I dont see it!

When we create instances of the Tas applications using programming languages, they remain hidden – they have no visible User Interface (UI). This doesn’t mean you can’t see them, though!

Run your macro again, but this time keep an eye on the ‘Processes’ list in the Windows Task Manager. You should see Tas3D appear when the macro starts, and dissappear when the macro finishes.

Too quick to see? We can add a message box to pause things so we can take a peek. Modify your macro like this:

 

				
					    'Open a new instance of the 3D modeller
    Dim t3dApp As TAS3D.T3DDocument
    Set t3dApp = New TAS3D.T3DDocument
    MsgBox "Quick, look in the Task Manager"
				
			

When you press OK, the macro will continue and the Tas3D instance will disappear.

				
					    'Create a new document using the instance we just opened
    t3dApp.Create
				
			

Next, as we’re creating a new 3D modeller document from scratch, we need to call the Create function.

This function tells the 3D modeller to make a new document – the 3D modeller creates default building elements, default zone lists and everything else you see when you create a new document using the user interface.

				
					    'Change the building name to "Hello, World!"
    t3dApp.Building.Name = "Hello, World!"
				
			

The 3D modeller file we just created has a Building property, and the Building property has a property called Name. In this step, we use the equals sign to change the value of that property to something else — in this case, the string “Hello, World!”. We used the equals sign earlier to ‘link up’ our Tas3D remote to our Tas3D object.

				
					'Save the 3D modeller file. YOU NEED TO CHANGE THIS LINE!
    t3dApp.Save ("C:\users\hilmya\desktop\MyNewFile.t3d")
				
			

Now we’re saving the file by calling the Save function — this is equivalent to pressing File > Save As in the 3D modeller. But as we don’t have a dialog to ask us where we want to save the file, we need to specify the file path using a string (the bit in the speech marks).

				
					    'Close the file
    t3dApp.Close
    
    'Close the 3D modeller instance
    Set t3dApp = Nothing
				
			

Next we use the Close function to close the file we created, and we set the t3dApp object reference variable to point to Nothing — this means it no longer controls the 3D modeller instance we opened earlier, so Excel will automatically close it as it can see we’re not using it any more.

				
					    'Message box, so we can tell when its finished
    MsgBox "Finished!"
				
			

Finally, we want to display a message box so its really clear that the macro has finished running. We do this using the MsgBox function thats built into excel, and we call this function using the string “Finished!”.

We’ve covered a lot in this first lesson and learned a lot of new things. Don’t worry if you feel overwhelmed, we’ll keep coming back to the things we’ve introduced in this first lesson as they’ll form the building blocks to more ambitious Macros!

If you haven’t watched the video for this lesson, I demonstrate and discuss the content of this lesson in more detail, so definitely watch it!

Details - Subroutines, Functions, Methods and Properties

Subroutines, functions and methods are essentially the same things and the terms often get used interchangably. They’re just ‘organisational blocks of code’, which can be re-used.

In VBA, subroutines cannot return a value but functions can. For example:

 

				
					'This function adds two numbers, and gives the value back to the code that executed it
Function AddNumbers(number1 As Double, number2 As Double) As Double
     AddNumbers = number1 + number2
End Function
				
			
				
					'This subroutine displays a message box containing the file path it is executed/called with
Sub FilePathMessageBox(FilePath As String)
    MsgBox FilePath
End Sub
				
			

The above function and subroutine have arugments – in the function they are called number1 and number2 and represent variables we wish to operate on within our function. Sometimes arugments are called Parameters.

Properties belong to Objects, (see the Details- Variables section), and are used to access variables that belong to that object. We saw one earlier when we accessed the Building property of the Tas3D document we created.

For more indepth information check out excel-easy.

Details - Variables & Objects

When a computer program is running, the computer program stores data in the computers memory. As a programmer, if you want to store data in memory and access it when your program is running, you can do so with Variables.

Lets look at the following simple macro:

				
					Sub StringVariable()
    
    Dim FirstName As String
    
    FirstName = Cells(1, 1)
    
    MsgBox FirstName
End Sub
				
			

Here, we’re creating a String variable calle FirstName, and setting its value to whatever is in cell A1 in the current Excel spreadsheet. We then display a messagebox showing the value of that String:

 

This enables us to react dynamically to the data we have and make our macro operate accordingly. 

I mentioned earlier that there are two broad types of variable – primitive variables and object reference varables. Primitive variables store simple values – things like text and numbers. Object reference variables store a reference to an Object – an Object is just a group of reference variables and possibly some functions associated with them.

Primitive Variables

  • Integer  – stores whole numbers, example- 56, -2, 12
  • Single – stores small decimal numbers – 12.5, 8.0, 12.2545, -45.32
  • Double – stores decimal numbers with more accuracy – 34.32234543476767
  • Boolean – stores true/false values. 

Non Primitive Variables

  • String – stores text, example – “Hello, there!”
For more information about variables, see excel-easy.

If you’ve made it this far and you’re still with me, well done! In the next lesson, we’ll make a macro that writes zone names to an existing 3D modeller file!

Coding: Take your Tas modelling to the next level

Coding: Take your Tas Modelling to the next level with Excel

				
					Sub Hello()
    Dim model As TAS3D.T3DDocument
    Set model = New TAS3D.T3DDocument
    
    model.Building.Name = "Hello, World!"
End Sub
				
			
hello_world_tbd_cropped

When EDSL sponsored the CIBSE Building Simulation Awards 2020, I noticed that almost all of the candidates had one thing in common; the use of Python scripts, macros and other programming tools to supercharge their modelling capability.

There’s no doubt coding is going to form an important part of the energy modelling of the future, but many feel overwhelmed by the prospect and don’t know where to start. Sound familiar? If so, this blog post series is for you.

In this series of blog posts, i’ll explain how to get started with the Tas Application Programming Interface – no prior experience required. The tools you’ll need to get started? A copy of Tas Engineering and Microsoft Excel.

Which programming languages are supported?

The Tas API has been part of Tas Engineering since the very beginning, which makes it extremely powerful. It uses features built into the Micorsoft Windows operating system which means it can be used with almost every programming language that will run on your PC, including:

  • Microsoft .net (c#, vb.net)
  • Python
  • Visual Basic for Applications (VBA; excel)
  • Java
  • C++, C
  • Matlab/Octave
You can even use it with Jupyter notebooks!

Why Excel?

With so much choice available and with Visual Basic for Applications (VBA) being fairly old, it’s a fair question! I chose Excel because:

  • Almost everyone has it
  • Excel is a great way to store data
  • The language is very similar to VB.net (very modern)

Almost everyone has it – you don’t need to waste time downloading, installing and setting up complicated development environments on every computer you’re using or want to run your program on.

Storing Data – When your clients send you data for internal conditions, how often does arrive as an Excel document? Wouldn’t it be great if you could press a button in excel and have the data go straight into your TBD file?

Language Similarity – As VBA is so similar to VB.net, when you’re ready to transition to a more modern and more powerful language, it’ll be a breeze. All the techniques I’ll teach you are transferrable, as most modern programming languages have a surprising amount in common!

What will I learn?

In this series of blog posts, I’ll teach you the basics of the Tas Programming Interface using practical examples of automating every day tasks.

Tas3D

We’ll start with the 3D modeller, and how to quickly create zones and zone groups in your Tas3D file directly from excel.

We’ll then run a daylight calculation, and write the lux levels to an Excel spreadsheet.

Next we’ll export our model and merge it with our TBD file.

TBD

When we get to the Building Designer, we’ll write a macro to quickly and effortlessly create Internal Conditions.

We’ll also write a macro to read/write heating and cooling loads from zones.

We’ll then simulate the TBD file to TSD.

TSD

We’ll write a script to extract results to a table, and process the data to look for certain values.

In order to teach you how to do these things, i’ll also need to teach you the basics of Object Oriented Programming — i’ll teach you the basics of variables, loops and conditions.

I’ll also introduce you to the Excel API!

I'm stuck! Help!

If, at any time along the way, you feel a bit lost and confused and run into any difficulty with the exercises in this tutorial series, don’t panic — I’m here to help. Just email the support team, mention this blog and your message will find its way to me and I’ll see what I can do.

If you do run into any difficulty and are able to resolve it, I’d still like to hear about it so I can improve the content for other learners such as yourself!

Whats Next?

In the next post, i’ll be showing you how to get started with Excel and Tas, and how to write your first macro! Click here to check it out.