31. May 2018

Benefit from your Excel VBA Know-how

Benefit from your Excel VBA Know-how

In this tip and trick we want to show you how to activate and use Excel’s VBA commands from within INOSIM’s VBA-Editor.

Using the integrated Excel-Workbook with INOSIM’s VBA commands

In INOSIM, each project provides an integrated Excel-Workbook, called Parameters. It can contain both Excel sheets for input data, and sheets designated for customized result reports. The workbook can be opened by double-clicking its entry in the database window:

You can also access the workbook with the integrated Visual Basic editor in INOSIM. For that purpose INOSIM provides dedicated Visual Basic commands.

For example, if you want to write the names and filling levels of all units to a sheet in the Excel workbook, you can use the code below.

'Using INOSIM commands
'to write the name and filling levels
'of all units to the Parameters-Table

Dim unitIndex As Integer 
Dim mySheet As Sheet
Dim mySheetName As String

mySheetName = "FillingLevelsSheet" 
Set mySheet = Parameters.Sheets(mySheetName) 

For unitIndex = 1 To Units.Count 
	'For each unit, a row is written in the specified sheet
	'the first column contains the name of the unit,
	'the second column contains the contents of the unit 
	' Note that the first row of the sheet is skipped, as a user-written header is present

	mySheet.CellYX(unitIndex+1,1) = Units.Item(unitIndex).Name
	mySheet.CellYX(unitIndex+1,2) = Units.Item(unitIndex).Contents 
Next

The result might look like this:

Applying INOSIM Basic commands

The names and filling levels of all units are transferred to the Excel sheet FillingLevelsSheet, which has to be created beforehand.
The sheet contains the names in column A and the filling levels in column B.
In this example the first row containing the header was also written manually, thus the code skips the first row when writing into the Excel sheet.

Using Microsoft Excel’s Visual Basic commands

The same results as above can be achieved by using the Excel’s Visual Basic commands. This means that INOSIM users which already know these commands do not have to change to the special Visual Basic syntax applied by INOSIM. Instead, they can benefit from their knowledge of Visual Basic while working with INOSIM.

Enabling Excel’s Visual Basic commands

To use the Excel’s VBA commands, enable the Microsoft Excel Object Library in the Visual Basic Editor (Menu Tools/References) and press OK:

Enabling the Excel Object Library in the Tools/Refeences Menu

Afterwards you can use the ExcelWorkbook Property, which allows you to use Visual Basic commands like:

Set mySheet = Parameters.ExcelWorkbook.Worksheets("Table 1")

Using Excel’s Visual Basic commands

To replicate the behavior from the example above, the following code can be used:

'Using standard Visual Basic commands to write
'the name and filling levels of all units to the Parameters-Table

Dim unitIndex As Integer
Dim mySheet As Worksheet
Dim mySheetName As String

mySheetName = "FillingLevelsSheet2"
Set mySheet = Parameters.ExcelWorkbook.Worksheets(mySheetName)

For unitIndex = 1 To Units.Count
    mySheet.Cells(unitIndex+1,1).value = Units.Item(unitIndex).Name
    mySheet.Cells(unitIndex+1,2).value = Units.Item(unitIndex).Contents
Next

Unlocking Excel’s VBA Potential

You can now access all of Excel’s default VBA commands, resulting in endless possibilities to change this workbook. Add, rename and delete sheets, change the cell format (e.g., set a background color or change font size and color), create and design diagrams, and much more. For a full documentation visit Microsoft’s Excel VBA reference.

Example: Cell referencing with names

For example, cells can be named to ease referencing. Here, the name MyReferenceCell was assigned to cell B2 in sheet FillingLevelsSheet3. The cell can now be easily referenced, when reading or writing data with Visual Basic. This ensures that the code will continue to work, even if lines or columns are added (and thus, their numbers change).

In the following code snippet, names and fillings levels of all units are once again written to a sheet, but their position in the sheet is relative to the position of the named cell MyReferenceCell:

'Using standard Visual Basic commands to write
'the name and filling levels of all units to the Parameters-Table
'starting with a reference cell
'
' The Microsoft Excel Object Library has to be enabled for this to work
' Can be enabled from the topmenu: Tools/References

Dim unitIndex As Integer
Dim mySheet As Worksheet
Dim mySheetName As String
Dim referenceCellName As String

mySheetName = "FillingLevelsSheet3"
referenceCellName = "MyReferenceCell" 'The name of cell B2 in the sheet FillingLevelSheet2 
Set mySheet = Parameters.ExcelWorkbook.Worksheets(mySheetName)

For unitIndex = 1 To Units.Count
    mySheet.Range("MyReferenceCell").Offset(unitIndex,0).Value = Units.Item(unitIndex).Name
    mySheet.Range(referenceCellName).Offset(unitIndex,1).Value = Units.Item(unitIndex).Contents
Next

Downloads

  • Example Project
  • PDF printout of this Tip & Trick

More Questions?

Want to know more about this topic or have another question? Please contact us!

Array ( [posts_per_page] => 3 [post_type] => [category__in] => Array ( [0] => 36 ) [orderby] => rand [order] => ASC )

More Tips & Tricks

In this tip, you learn how to create PDFs which display a selected time range of a complex Gantt chart. You also learn how to…

This Tip & Trick is about using a VBA Timer execution timer for VBA controls and measuring the execution duration of VBA controls. To check…

16. March 2019

Custom Bars

Custom Bars Custom bars are a new feature introduced in INOSIM 12. They allow you to display processes in your Gantt chart which are not…

more

Direct Contact

During local business hours

Germany +49 231 97 00 250

USA +1 214 663 3101

India +91 9766 331 092