Pertmaster - Macro Tutorials |

Pertmaster - Macro Tutorials

Macro Tutorials
This document is an extract from the Pertmaster help file version h8.5.1.

Pertmaster - Macro Tutorials | 1

Table of Contents
1. 2. 3. 4. 5. Macro Tutorials Macro Tutorial - Report writing macro Macro Tutorial - Global report writing macro Macro Tutorial - Event driven macro: Conditional formatting PDF Documentation and Printing Help 1 1-5 5-6 6-9 9-10

Macro Tutorials
These tutorials take you through the steps of creating macros in Pertmaster.

The ability to create and run macros will depend on the version of Pertmaster you are using see Pertmaster Products (on-line documentation) If you are reading this is in the on-line help you may want to print out this tutorial or use the PDF booklet installed in the Pertmaster Documentation folder - see PDF Documentation and Printing Help (Section 5)

Tutorial contents: 1. 2. 3. 4. Report writing macro Global report writing macro Event driven macro: Conditional formatting Creating a form based macro

Files used Pertmaster Files (installed with Pertmaster)

Tutorial_Macros_Report(v1.1).plan Plan that is used in the Report writing macro tutorials. Tutorial_Macros_ConditionalFormat(v1.2).plan Plan that is used in the Event driven macro tutorial.

Macro Tutorial - Report writing macro

In this tutorial we will design a macro that creates a simple report in MS Excel. The macro will be a Plan macro. The macro created in this tutorial requires MS Excel to run. About Plan Macros Plan macros are saved as part of the plan they were created in. If a plan is copied or moved then any Plan macros will still be available. A Plan macro can only be run from the plan it was created in. Use Global macros to create a macro which is available to any open plan - see the tutorial Report writing macro (Section 3) . 1. Open sample plan

Open the sample plan installed with the Pertmaster named "Tutorial_Macros_Report(v1.1).plan" Tools | Macros | Visual Basic Editor - You will be taken to Microsoft Visual Basic application. Expand "PertmasterPlan(Tutorial_Macros_Report(v1.1).Expand "PertmasterPlan (Tutorial_Macros_Report(v1.1).plan)". Right-click on ThisPlan and select View Code. The code window will be displayed on the right.

Pertmaster - Macro Tutorials | 2

2. Referencing MS Excel The macro will use MS Excel to create our report. To use MS Excel a reference to it must be added to the macro.

In Visual Basic Editor: Select "PertmasterPlan(Tutorial_Macros_Report(v1.1).plan)" Tools | References | Find and check Microsoft Excel 9.0 Object Library (or if not 9.0 then whichever version of Excel is available). Click OK .

3. Entering the macro code

Copy and paste the following code in into the code window .

Sub CreateReport() ' menutitle=Excel Task Report If MsgBox("This will create an Excel report. Continue?", vbOKCancel) = vbOK Then

Pertmaster - Macro Tutorials | 3

Dim Dim Dim Dim Dim Dim m_objExcel As Object m_objWorkbook As Object objTask As Task objTasks As Tasks nRow As Integer objSheet As Excel.Worksheet

Set m_objExcel = New Excel.Application ' start up Excel m_objExcel.Visible = False ' do not display Excel yet Set m_objWorkbook = m_objExcel.Workbooks.Add 'create new workbook Set objSheet = m_objWorkbook.Sheets(1) ' Title nRow = 1 objSheet.Cells(nRow, nRow = nRow + 1 objSheet.Cells(nRow, objSheet.Cells(nRow, objSheet.Cells(nRow, nRow = nRow + 1

"A") = "Pertmaster Report - " & ActivePlan.Information.Title "A") = "ID" "B") = "Description " "C") = "Comment"

' Populate spreadsheet with task information Set objTasks = ActivePlan.Tasks For Each objTask In objTasks objSheet.Cells(nRow, "A") = objTask.ID objSheet.Cells(nRow, "B") = objTask.Description objSheet.Cells(nRow, "C") = objTask.Comment nRow = nRow + 1 Next ' Adjust column widths objSheet.Columns("B:C").EntireColumn.AutoFit m_objExcel.Visible = True End If End Sub

4. Saving the macro Plan macros are saved with with the plan file (.plan). The save command can be initiated from within Microsoft Visual Basic or the Pertmaster application.

In MS Visual Basic - File | Save (filename)

5. Running the macro

Close the Microsoft Visual Basic application - File | Close and return to Pertmaster Risk Expert . Macros run faster if the MS Visual Basic application is closed.

In Pertmaster - Tools | Macros | Macros.

Pertmaster - Macro Tutorials | 4

Select 'PertmasterPlan (Tutorial_Macros_Report(v1.1).plan' from the dropdown list. Click on 'CreateReport'. Click Run . If you receive the message "Compile error: User-defined type not defined" it is likely that the reference created in step 2 above has not been completed. Reset the macro ( Run | Reset) and check the reference has been added correctly.

"This will create an Excel report. Continue?". Click OK . MS Excel will be opened and populated with data:

Pertmaster - Macro Tutorials | 5

Good work! Move onto the next tutorial to learn about Global macros.

Macro Tutorial - Global report writing macro

About Global Macros A Global macro can be run from any plan. In the last tutorial we created a Plan macro that generated a Report in Excel. As we may want to run this report writing macro on other plans we will the same macro as a Global macro .

1. Create a new global macro

Start up Pertmaster. Close down any open plans using File | Close . Tools | Macros | Macro Manager | Create New Macro File. Enter ExcelReport in the Macro Project Name. Keep the default Macros Folder .

Click OK and the new macro will be added. Click OK in the Macro Manager dialog and the Microsoft Visual Basic editor will be opened.

2. Reference MS Excel The macro will use MS Excel to create the report. To use MS Excel a reference to it must be added to the macro.

Tools | References | Find and check Microsoft Excel 9.0 Object Library (or if not 9.0 then whichever version of MS Excel is available).

3. Add the code

Copy and paste the visual basic code used in the Plan macro tutorial into the open code window replacing any existing code: Plan macro tutorial (Section 2)

3. Save the Global macro

File | Save ExcelReport.vba

4. Run the Global Macro A global macro can be run from any plan that is open. We will now open up a plan and run the macro.

File | Close and return to Pertmaster Risk Expert. Open any plan using File | Open. Tools | Macros | Macros.

Pertmaster - Macro Tutorials | 6

Change Macros in: to 'ExcelReport (ExcelReport.vba)'. Click on 'CreateReport'. Click Run .

Excellent! You have created a Global macro that can be used with any plan. That covers the basics of Global macros. To find out about macros have a look at the other macro tutorials.

Macro Tutorial - Event driven macro: Conditional formatting

4.1 About event driven macros
The macros created in the Global and Plan tutorials only run when the user explicitly requests them to run via the Macros menu. Event driven macros are automatically run by events that occur in the application and plan. In this tutorial we will create an event driven macro that colors each task bar depending on the value entered against a task. The event that will drive our macro is AfterPlanModify. This event occurs whenever a change is made to the plan. Event driven macros can be Plan or Global. When using a Global event driven macro you have the additional option of using application events as well as plan events. For example there is an application event called Application_AfterStart that is fired when the Pertmaster application starts.

1. Open the conditional formatting sample plan The first step is to create a macro that will perform the function we require, in this case formatting the task bar color. To save you time the macro has already been entered in the example project so all we need to do is make the macro event driven.

Start up the Pertmaster application. Open the sample file installed with Pertmaster 'Tutorial_Macros_ConditionalFormat(v1.3).plan' Click Enable when the macro warning message is displayed. Tools | Macros | Visual Basic Editor (or use Alt + F11 ) Double-click on modFormat to display the code.

Pertmaster - Macro Tutorials | 7

In Modules you should see a sub routine called conditionalFormat() and a function called GetColor . The GetColor function is called by the conditionalFormat sub routine. The code is shown below:

' -------------------------------------------------------------------' ' ' ' ' ' ' ' ' ' ' ' Title: Tutorial Conditional formatting Version: 1.04 Description: Task bars are coloured automatically based on values entered user text 1. Change values in user text 1 to see effect. Created by: Tim Stevens (Pertmaster Team) Created in: Pertmaster Project Risk Expert 8.0.99 Modifications: Values converted from v7 Text fields to use v8 Task codes Notes: --------------------------------------------------------------------

Option Explicit Private Const COLORFIELD As String = "Task Color"

Sub ConditionalFormat() Dim objTask As Task Dim objTasks As Tasks

' Set up objTasks as a collection of all the tasks in the plan ' objTasks can then be used to loop through all the tasks Set objTasks = ActivePlan.Tasks

For Each objTask In objTasks If objTask.UserFields.UserField(COLORFIELD) = "" Then objTask.Style.BarStyle.Color = -1 ' -1 indicates <default> objTask.Style.BarStyle.FillStyle = -1 ' -1 indicates <default> Else objTask.Style.BarStyle.Color = GetColour(objTask.UserFields.UserField(COLORFIELD)) objTask.Style.BarStyle.FillStyle = efsSolid End If Next End Sub

Private Function GetColour(strColour As String) As Long strColour = UCase(strColour) 'make it non case sensitive Select Case strColour Case "" GetColour = -1 Case "BLACK" GetColour = RGB(0, 0, 0)

Pertmaster - Macro Tutorials | 8

Case "DBLUE" GetColour = RGB(0, 0, 128) Case"DGREEN" GetColour = RGB(0, 128, 0) Case "DCYAN" GetColour = RGB(0, 128, 128) Case "DRED" GetColour = RGB(128, 0, 0) Case "DMAGENTA" GetColour = RGB(128, 0, 128) Case "DYELLOW" GetColour = RGB(128, 128, 0) Case "GREY" GetColour = RGB(192, 192, 192) Case "DGREY" GetColour = RGB(128, 128, 128) Case "BLUE" GetColour = RGB(0, 0, 255) Case "GREEN" GetColour = RGB(0, 255, 0) Case "CYAN" GetColour = RGB(0, 255, 255) Case "RED" GetColour = RGB(255, 0, 0) Case "MAGENTA" GetColour = RGB(255, 0, 255) Case "YELLOW" GetColour = RGB(255, 255, 0) Case "WHITE" GetColour = RGB(255, 255, 255) Case Else ' <default> GetColour = -1 End Select End Function

2. Making the macro event driven The conditionalFormat macro can be run at any time. However we would like this macro to be run whenever a change is made to Task Color . An event that is triggered when a change is made to Task Color is the AfterPlanModify. This event is triggered by any change to the plan. Though we are only interested in changes to Task Color we must use this event as there are no events that are only triggered by a change to the user text field. In this step we will add the event AfterPlanModify and enter a line of code that runs the conditionalFormat macro.

In the tree structure find and double-click on ThisPlan. This opens the code window. In the code window, click on the the first drop down list and select Plan - this populates the second drop down list with the available events.

In this tutorial the event we wish to use is the AfterPlanModify. This event occurs after any value in the plan is changed.

Click on the second drop down list and choose AfterPlanModify. This adds the event sub routine (two lines of code) to the code window. This code calls our macro when the plan is modified:

Pertmaster - Macro Tutorials | 9

modFormat.conditionalFormat

Save and Close the Visual Basic Editor.

3. Triggering the event driven macro As discussed event driven macros do not need to be run explicitly by you as they are driven by an event. In our tutorial the event that triggers our macro is AfterPlanModify. We will change the value into the Task Color column to trigger this event and see the effect.

Add the user Task Color column if not displayed (use Format | Columns ). In the user Task Color column click on a cell and choose a color from the dropdown, BLACK, BLUE, RED, MAGENTA, etc. as per code above. The task bar color should change. Clear text to reset to the default color.

PDF Documentation and Printing Help

PDF Documentation Some of the on-line help (e.g. tutorials) can be found in the Documentation folder that is created when the Pertmaster software is installed. The documentation is saved in the Adobe PDF format. The default installation folder for the documentation is: C:\Program Files\Pertmaster Software\Pertmaster8x\Documentation Printing an individual help topic After printing a help topic, Windows can sometimes freeze the help file. If this occurs right-click on the Pertmaster help application icon in Windows Start menu Taskbar (usually located at the bottom of the screen) and choose Restore . 1. 2. Select the required topic. Click on the Print button.

3.

Choose Print the selected topic.

Pertmaster - Macro Tutorials | 10

Printing a chapter of the help After printing a chapter of the help, Windows can sometimes freeze the help file. If this occurs rightclick on the Pertmaster help application icon in Windows Start menu Taskbar (usually located at the bottom of the screen) and choose Restore . 1. 2. 3. Select the required chapter. Click on the Print button Choose Print the selected heading and all the subtopics .

The example below has the Risk Tutorial - Part 1 selected. Clicking on the Print button and selecting Print the selected heading and all the subtopics will print out the whole of the 'Risk Tutorial - Part 1'.