Chapter Eight

Visual Basic for

Applications (VBA)

McGraw-Hill/Irwin © 2005 The McGraw-Hill Companies, Inc. All rights reserved.

Chapter Eight Learning Objectives

Understand the structure of the VBA Integrated Development Environment

Record VBA macros from Excel and Arena Understand the structure of Excel based VBA

code Develop Arena models that utilize the VBA block Develop Arena models using VBA that reads

Variable values from an Excel workbook Develop Interactive Arena models using VBA

OnKeystroke routine

Chapter Eight Worksheet Menu

Chapter 8 Exhibits

Use Ctrl-Shift-R to return to this

worksheet

Process Analysis and Improvement: Tools and Techniques Copyright ©2005 by The McGraw-Hill Companies, Inc.

Filename Sheet / Page Name Exhibit Number Application / Caption

Drawings.vsd Excel Parent Child Relationships 4 Excel Parent – Child Relationships

Drawings.vsd Excel Objects Model 5 Excel Objects Model

Worksheets.xls Variables 9 Excel Recording Macro Selection Sequence

Worksheets.xls Order Book 25 Worksheet Order Book

Worksheets.xls Arena VBA Routines 34 Arena VBA Routines

Worksheets.xls Arena VBA Models 35 Arena Models Using VBA Routines

Worksheets.xls Variables 41 Model Manufacturing Cell VBA.doe Input Data Worksheet

Worksheets.xls Smarts 54 Arena VBA Smarts Models

Related Files

Filename Source Application

Manufacturing Cell VBA.doe Text CD-ROM Extended Manufacturing Simulation Model

Manufacturing Cell VBA.xls Text CD-ROM Extended Manufacturing Simulation Model Variable Data

smExcelInterface.bas Text CD-ROM Rockwell Software VBA linkage to Excel routines

VBA Generate.doe Text CD-ROM Self generating Arena model

VBA Large Accumulator.doe Text CD-ROM Assures Academic Limits using VBA code

VBA Output.doe Text CD-ROM Simple VBA output to Excel & text file

View Selected Worksheet or Drawing

Chapter Eight Files

CD File Description

Chapter 8 Drawings.vsd Chapter 8 Drawings

Chapter 8 Presentation.ppt Chapter 8 PowerPoint Presentation

Chapter 8 Worksheets.xls Chapter 8 Worksheets

Manufacturing Cell VBA.doe Extended Manufacturing Simulation Model

Manufacturing Cell VBA.xls Extended Manufacturing Simulation Model Variable Data

smExcelInterface.bas Rockwell Software VBA linkage to Excel routines

VBA Generate.doe Self generating Arena model

VBA Large Accumulator.doe Assures Academic Limits using VBA code

VBA Output.doe Simple VBA output to Excel & text file

Chapter Eight Outline

Introduction to Visual Basic for Applications

VBA Integrated Development Environment

VBA Object Model Overview Recording VBA Macros Excel Macros Arena Support of VBA

Introduction to VBA

VBA is included with many windows applications, press Alt-F11 Microsoft Office Arena AutoCAD

Getting Started with VBA Learn the Application First Learn What You Need, When You Need It

VBA Integrated Development Environment (IDE)

Properties WindowProject ExplorerCode WindowVBA HelpObject Browser

VBA Integrated Development Environment (IDE)

Exhibit 8-1 p. 219VBA Integrated Development Environment

Project Explorer

Properties Window

Code Window

VBA Help (F1)

Exhibit 8-2 p. 219VBA IDE Help Window

Object Browser (F2)

Exhibit 8-3 p. 221VBA Object Browser Window

VBA Object Model Overview

Properties and Methods

VBA ReferencesExcel

Application

Worksheet

Workbook

Child Parent

Worksheet

Child ChildParentParent

Range

ChildParent

Range

ChildParent

Range

ChildParent

Range

ChildParent

Exhibit 8-4 p. 222Sheet Parent Child Relationships

Excel VBA Object Model

Exhibit 8-5 p. 223Page Excel Objects Model

Properties and Methods

Change Property Application.Workbooks("Book1.xls").Worksheets("Sheet1").Range("

B3").Value = 3 Application.Workbooks("Book1.xls").Worksheets("Sheet1").Range("

B3").Font.Bold = True

Use Method Application.Workbooks("Book1.xls").Close

VBA References

Tools > References

Exhibit 8-6 p. 224Excel-VBA References Dialog

VBA References

Available References Types: Type Libraries

• .olb, .tlb, .dll Executable Files

• .exe, .dll ActiveX Controls

• .ocx

Exhibit 8-7 p. 225VBA Add References dialog box

VBA References

Exhibit 8-8 p. 225Arena-VBA References Dialog Box

Recording VBA Macros

Recording an Excel VBA Macro Recording an Arena VBA Macro

Recording an Excel VBA Macro

Tools > Macro > Record New Macro…

Exhibit 8-10 p. 226 Excel Recording Macro DialogExhibit 8-9 p. 226

Sheet Variables

Recording an Excel VBA Macro

Edit > Move or Copy Sheet …

Exhibit 8-12 p. 227Save As Manufacturing Cell VBA.xls

Exhibit 8-11 p. 226Excel Move or Copy Worksheet Dialog

File > Save As… A:\Manufacturing Cell VBA.xls

Exhibit 8-13 p. 227 Excel Recording Macro Stop Button

Editing & Running Excel Macro

Exhibit 8-15 p. 227 Excel Macro Execution Dialog

Exhibit 8-14 p. 227Excel Macro Code

Recording an Arena VBA Macro

Tools > Macro > Record Macro…

Exhibit 8-17 p. 228 Arena Recording Macro Dialog

Exhibit 8-16 p. 228Arena Recording Macro Selection Sequence

Recording an Arena VBA Macro

Generate and Save a simple Arena model

Exhibit 8-18 p. 228VBA Generate.doe

Exhibit 8-19 p. 228Arena Recording Macro Stop Button

Editing & Running Arena Macro

Exhibit 8-21 p. 229Arena Macro Execution Dialog

Exhibit 8-20 p. 229Arena Macro Code

Excel Macros

Macro security Preventing Macro Viruses Worksheet Order Book Workbook Chapter n Worksheets.xls

Macro security

Exhibit 8-22 p. 229Excel Options Dialog

Tools > Options > Security

Macro security

Exhibit 8-24 p. 230Excel Macro Dialog

Exhibit 8-23 p. 229 Excel Security Options Dialog

Tools > Options > Security

Preventing Macro Viruses

A macro virus is a type of computer virus that's stored in a macro within a workbook or add-in program

Microsoft Excel doesn't scan the floppy disk, hard disk, or network drive to find and remove macro viruses

Antivirus software protection can be purchased, installed, frequently updated

Worksheet Order Book Macros

Order Book

Process Analysis and Improvement: Tools and Techniques

Copyright ©2005 by The McGraw-Hill Companies, Inc.

To enter data using Form, Press F5, select Orders,

and Data > Form …Filtered Quantity

Filtered Backlog

Filtered Shipments

Filtered Backorders

Current Date 2/22/2004Right Click to Select from

List Sums 113,400 $435,000 13,000 3,000

Sequence Order Date Due Date

Lead Time Days Slack Days Customer Product Unit Price

Order Quantity

Outstanding Order Value

Shipped Date

Shipped Quantity

Backorder Quantity

1 1/7/2004 2/20/2004 44 -2 Ajax Widget $5.00 5,000 $0 2/22/2004 5,000 Shipped

2 1/9/2004 2/8/2004 30 -14 Acme Improved $4.50 6,000 $13,500 2/1/2004 3,000 3,000

3 1/11/2004 2/1/2004 21 -21 Interstate Standard $2.50 5,000 $0 2/1/2004 5,000 Shipped

Sort by Due Date Sort by Order Date

Exhibit 8-25 p. 230Sheet Order Book

Exhibit 8-26 p. 230Right Button Click Menu

Worksheet Order Book Macros

Exhibit 8-27 p. 230Macro Selection Menu

Exhibit 8-28 p. 231VBA Integrated Development Environment

Workbook Chapter 8 Worksheets.xls Routines

Exhibit 8-30 p. 232 ThisWorkbook Routines

Chapter 8 Worksheets.xls

Exhibit 8-32 p. 233Function psFntOpenVisio

Chapter 8 Worksheets.xls

Exhibit 8-33 p. 234Subroutine psSubReturn

Chapter 8 Worksheets.xls

Public Sub psSubSortOrderBookbyDueDate()On Error GoTo ErrorExit Application.GoTo Reference:="Orders" Selection.Sort Key1:=Range("C4"), Order1:=xlAscending, Key2:=Range("B4") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom Application.GoTo Reference:="CurrentDate" Exit SubErrorExit: MsgBox "Error #" & Err.Number & ": " & Err.Description & _ vbCrLf & "psSubSortOrderBookbyDueDate"End Sub

Subroutine psSubSortOrderBookbyDueDate

Chapter 8 Worksheets.xls

Exhibit 8-29 p. 231VBA Code Error Message Box

Public Sub psSubSortOrderBookbyOrderDate()On Error GoTo ErrorExit Application.GoTo Reference:="Orders" Selection.Sort Key1:=Range("B4"), Order1:=xlAscending, Key2:=Range("C4") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom Application.GoTo Reference:="CurrentDate" Exit SubErrorExit: MsgBox "Error #" & Err.Number & ": " & Err.Description & _ vbCrLf & "psSubSortOrderBookbyOrderDate"End Sub

Subroutine psSubSortOrderBookbyOrderDate

Public Sub psSubViewSelectedObject()Const SheetColumnChapter As Long = 1Const SheetColumnFileName As Long = 1Const SheetColumnSheetName As Long = 2Const SheetRowChapter As Long = 1Const SheetRowStart As Long = 3Static Chapter As StringStatic FolderWorkbook As StringStatic First12 As String * 12Static Name As StringStatic SheetRow As LongStatic TestCharacter As String * 1Static VisioApp As Visio.ApplicationStatic VisioDocument As Visio.Document On Error GoTo ErrorExit0 FolderWorkbook = ThisWorkbook.Path Call psSubReturn Chapter = Trim(Cells(SheetRowChapter, SheetColumnChapter).Value) SheetRow = Selection.Row If SheetRow < SheetRowStart Then SheetRow = SheetRowStart TestCharacter = Trim(Cells(SheetRow, SheetColumnFileName).Value) If TestCharacter Like "[W]" Then Name = Trim(Cells(SheetRow, SheetColumnSheetName).Value) First12 = Name If First12 Like "ThisWorkbook" Then MsgBox "Press Alt-F11 to enter the VBA Editor"

Chapter 8 Worksheets.xls

Exhibit 8-31 p. 232psSubViewSelectedObject

Chapter 8 Worksheets.xls ElseIf Len(Name) > 0 Then On Error GoTo ErrorExit1 Sheets(Name).Select Else Beep End If ElseIf TestCharacter Like "[D]" Then Name = Trim(Cells(SheetRow, SheetColumnSheetName).Value) Set VisioApp = psFntOpenVisio On Error GoTo ErrorExit2 VisioApp.Documents.Open (FolderWorkbook & "\" & Chapter & " Drawings.vsd") On Error GoTo ErrorExit3 VisioApp.ActiveWindow.Page = Name Else Beep End If Exit SubErrorExit0: MsgBox "Error #" & Err.Number & ": " & Err.Description & _ vbCrLf & "psSubViewSelectedObject" Exit SubErrorExit1: MsgBox "Workbook Sheet: " & Name & " not found" Exit SubErrorExit2: MsgBox "Visio Drawing File: " & FolderWorkbook & _ "\" & Chapter & " Drawings.vsd" & " not found" Exit SubErrorExit3: MsgBox "Visio Drawing Page: " & Name & " not found" Exit SubEnd Sub

Exhibit 8-31 p. 232psSubViewSelectedObject

Arena Support of VBA

Arena Model Logic Routines VBA Based Arena Models

VBA Large Accumulator.doe Manufacturing Cell VBA.doe VBA Output.doe Beer Game.doe VBA Code

Arena VBA Smarts Models

Arena Support of VBA

Exhibit 8-34 p. 235Sheet Arena VBA Routines

VBA Based Arena Models

Exhibit 8-35 p. 235Sheet Arena VBA Models

VBA Large Accumulator.doe

Exhibit 8-36 p. 235VBA Large Accumulator.doe Network Logic Flow

VBA Large Accumulator.doe

Exhibit 8-37 p. 236Declarations

' Process Analysis and Improvement: Tools and Techniques' Copyright ©2005 by The McGraw-Hill Companies, Inc.Option Compare Text ' Upper & lower case are equivalentOption Explicit ' All variables must be defined' Arena Variables available through entire projectPublic SimanModel As Arena.ModelPublic Siman As Arena.Siman' User Constants & Variables available through entire projectConst maxAttributes As Long = 2Const AccumulatorSize As Long = 500Public ArenaFolder As StringDim AttributeValues(AccumulatorSize, maxAttributes) As DoublePublic ModelFileName As StringPublic ModelFolder As StringPublic PointerInput As LongPublic PointerOutput As Long

Defines code variables

VBA Large Accumulator.doe

Exhibit 8-38 p. 237Subroutine ModelLogic_RunBeginSimulation

Public Sub ModelLogic_RunBeginSimulation()Static ArenaView As view' Subroutine executes when F5 is pressed On Error GoTo ErrorExit ' Setups error recovery PointerInput = 0 PointerOutput = 0 Set SimanModel = ThisDocument.Model Set Siman = SimanModel.Siman With SimanModel' Get the location of Arena and the current model ArenaFolder = Mid(.Application.FullName, 1, Len(.Application.FullName) - _ (Len(.Application.Name) + 4)) ModelFolder = Mid(.FullName, 1, Len(.FullName) - Len(.Name)) ModelFileName = Mid(.Name, 1, Len(.Name) - 4) End With Exit SubErrorExit: ' Jump here if an error occurs MsgBox "ModelLogic_RunBegin Error #" & Err.Number & ": " & Err.DescriptionEnd Sub

Executes when model is started (F5)

VBA Large Accumulator.doe

Exhibit 8-39 p. 237Subroutine VBA_Block_1_Fire

Private Sub VBA_Block_1_Fire()Static AttributeNumber As Long On Error GoTo ErrorExit ' Setups error recovery PointerInput = (PointerInput Mod AccumulatorSize) + 1 AttributeValues(PointerInput, 0) = Siman.RunCurrentTime For AttributeNumber = 1 To maxAttributes AttributeValues(PointerInput, AttributeNumber) = _ Siman.EntityAttribute(Siman.ActiveEntity, AttributeNumber) Next Exit SubErrorExit: ' Jump here if an error occurs MsgBox "Error #" & Err.Number & ": " & Err.Description & vbCrLf & "VBA_Block_1_Fire"End Sub

Executes when entity enters VBA 1

VBA Large Accumulator.doe

Exhibit 8-40 p. 237Subroutine VBA_Block_2_Fire

Private Sub VBA_Block_2_Fire()Static AttributeNumber As Long On Error GoTo ErrorExit ' Setups error recovery PointerOutput = (PointerOutput Mod AccumulatorSize) + 1 For AttributeNumber = 1 To maxAttributes Siman.EntityAttribute(Siman.ActiveEntity, AttributeNumber) = _

AttributeValues(PointerOutput, AttributeNumber) Next Exit SubErrorExit: ' Jump here if an error occurs MsgBox "Error #" & Err.Number & ": " & Err.Description & vbCrLf & "VBA_Block_2_Fire"End Sub

Executes when entity enters VBA 2

Manufacturing Cell VBA.doe

Exhibit 8-41 p. 238Sheet Variables

Manufacturing Cell VBA.doe

Exhibit 8-42Declarations

' Process Analysis and Improvement: Tools and Techniques' Copyright ©2005 by The McGraw-Hill Companies, Inc.Option Compare Text ' Upper & lower case are equivalentOption Explicit ' All variables must be defined' Arena Variables available through entire projectPublic SimanModel As Arena.ModelPublic Siman As Arena.Siman' User Constants & Variables available through entire projectConst SheetName As String = "Variables"Const SheetColumnStart As Long = 1Const SheetRowStart As Long = 4Public ModelFileName As StringPublic FileNumber As LongPublic ArenaFolder As StringPublic ModelFolder As String

Defines code variables

Public Sub ModelLogic_RunBeginSimulation()' Subroutine executes when F5 is pressed On Error GoTo ErrorExit ' Setups error recovery Set SimanModel = ThisDocument.Model Set Siman = SimanModel.Siman With SimanModel' Get the location of Arena and the current model ArenaFolder = Mid(.Application.FullName, 1, Len(.Application.FullName) - _ (Len(.Application.Name) + 4)) ModelFolder = Mid(.FullName, 1, Len(.FullName) - Len(.Name)) ModelFileName = Mid(.Name, 1, Len(.Name) - 4) End With FileNumber = 0 Call smInitializeExcel(False, 1) ' Setups the Excel interface' Open and Read Excel Simulation Workbook If FileNumber = 0 Then FileNumber = smOpenExcelWorkbook(ModelFolder & "\" & ModelFileName & ".xls") Call psSubSheetVariablesRead End If Call smExitExcel ' Close Excel Exit SubErrorExit: ' Jump here if an error occurs MsgBox "ModelLogic_RunBegin Error #" & Err.Number & ": " & Err.Description & vbCrLf & _ "Error Reading Simulation Workbook " & ModelFileNameEnd Sub

Manufacturing Cell VBA.doe

Exhibit 8-43 p. 239Subroutine ModelLogic_RunBeginSimulation

Executes when model is started (F5)

Public Sub psSubSheetVariablesRead()Static SheetRow As LongStatic VBAChanges As LongStatic VariableName As StringStatic VariableSymbolNumber As LongStatic VariableValueOriginal As DoubleStatic VariableValueWorkbook As Double On Error GoTo ErrorExit ' Setups error recovery VBAChanges = 0 With smXL.Workbooks(FileNumber).Worksheets(SheetName) SheetRow = SheetRowStart' Continue as long as Column 1 is not blank Do While Len(.Cells(SheetRow, SheetColumnStart).Value) > 0' Get the Variable Name from Column 1 VariableName = Trim(.Cells(SheetRow, SheetColumnStart).Value)' Check if the Variable Name is known to this Arena model VariableSymbolNumber = Siman.SymbolNumber(VariableName) If VariableSymbolNumber > 0 Then' Get the Variable Value from Column 2 VariableValueWorkbook = .Cells(SheetRow, SheetColumnStart + 1).Value VariableValueOriginal = Siman.VariableArrayValue(VariableSymbolNumber) If VariableValueWorkbook <> VariableValueOriginal Then' Change the Variable Value in the Arena model Siman.VariableArrayValue(VariableSymbolNumber) = VariableValueWorkbook

Manufacturing Cell VBA.doe

Exhibit 8-44 p. 239Subroutine psSubSheetVariablesRead

Executed by ModelLogic_RunBeginSimulation

If VBAChanges = 0 Then Open ModelFolder & "\" & ModelFileName & ".TXT" For Output As #1 Print #1, "Workbook "; ModelFileName; Tab(40); "Changed Values" Print #1, "Worksheet "; SheetName; Tab(40); "Initial"; Tab(50); "New" End If VBAChanges = VBAChanges + 1 Print #1, VBAChanges; Tab(6); VariableName; _ Tab(40); VariableValueOriginal; Tab(50); VariableValueWorkbook End If Else MsgBox "Variable Name " & VariableName & " not found in Arena model " & _ ModelFileName End If SheetRow = SheetRow + 1 Loop End With If VBAChanges > 0 Then Close #1 Siman.VariableArrayValue(Siman.SymbolNumber("VBA.Changes")) = VBAChanges MsgBox VBAChanges & " VBA Variables Changed" Else MsgBox "No VBA Variables Changed" End If Exit SubErrorExit: ' Jump here if an error occurs MsgBox "Error #" & Err.Number & ": " & Err.Description & vbCrLf & _ "Error Reading Simulation Workbook " & ModelFileName & _ vbCrLf & "Sheet Name: " & SheetName & " Sheet Row " & SheetRowEnd Sub

Manufacturing Cell VBA.doe

Exhibit 8-44 p. 239Subroutine psSubSheetVariablesRead

Manufacturing Cell VBA.doe

Exhibit 8-45 p. 239Error Message Box

Exhibit 8-46 p. 240Variable Change Message Box

VBA Output.doe

Exhibit 8-47 p. 240Network Logic Flow

Exhibit 8-52 p. 240VBA Output.xls

VBA Output.doe

Exhibit 8-49 p. 241Declarations

' Process Analysis and Improvement: Tools and Techniques' Copyright ©2005 by The McGraw-Hill Companies, Inc.Option Compare TextOption Explicit' Systems Modeling VariablesPublic ArenaModel As Arena.ModelPublic Siman As Arena.Siman' Productive Systems Constants & VariablesConst KeyColumn As Long = 1Const Variables As Long = 2Const SheetName As String = "Output"Dim FileNameDoe As StringDim FileNumberSimulation As LongDim FolderArena As StringDim FolderModel As StringDim SheetRow As LongDim VariableSymbolNumber(Variables) As Long

Defines code variables

Private Sub ModelLogic_RunBeginSimulation()Static ArenaView As viewStatic Index As Long On Error GoTo ErrorExit' Setup Interface to Arena Set ArenaModel = ThisDocument.Model Set Siman = ArenaModel.Siman With ArenaModel FolderArena = Mid(.Application.FullName, 1, Len(.Application.FullName) - _ (Len(.Application.Name) + 4)) FolderModel = Mid(.FullName, 1, Len(.FullName) - Len(.Name)) FileNameDoe = Mid(.Name, 1, Len(.Name) - 4) End With VariableSymbolNumber(1) = Siman.SymbolNumber("Variable.1") VariableSymbolNumber(2) = Siman.SymbolNumber("Variable.2")' Open a new Excel Workbook smInitializeExcel False, 1 FileNumberSimulation = smNewExcelWorkbook' Generate first row of Excel worksheet SheetRow = 1 With smXL.Workbooks(FileNumberSimulation).Worksheets("Sheet1") .Cells(SheetRow, KeyColumn).Value = "Minutes" For Index = 1 To Variables .Cells(SheetRow, KeyColumn + Index).Value = "Variable." & Index Next Sheets("Sheet1").Name = SheetName End With SheetRow = SheetRow + 1 Exit SubErrorExit: MsgBox "ModelLogic_RunBegin Error #" & Err.Number & ": " & Err.DescriptionEnd Sub

VBA Output.doe

Exhibit 8-50 p. 241Subroutine ModelLogic_RunBeginSimulation

Executes when model is started (F5)

Private Sub VBA_Block_1_Fire()Static Index As LongStatic VariableValue As Double On Error GoTo ErrorExit With smXL.Workbooks(FileNumberSimulation).Worksheets(SheetName) .Cells(SheetRow, KeyColumn).Value = Siman.RunCurrentTime For Index = 1 To Variables If VariableSymbolNumber(Index) > 0 Then VariableValue = Siman.VariableArrayValue(VariableSymbolNumber(Index)) .Cells(SheetRow, KeyColumn + Index).Value = VariableValue End If Next End With SheetRow = SheetRow + 1 Exit SubErrorExit: MsgBox "Error #" & Err.Number & ": " & Err.Description & vbCrLf & _ "Error VBA_Block_1_Fire " & vbCrLf & "Sheet Name: " & SheetName & " Sheet Row " & SheetRowEnd Sub

VBA Output.doe

Exhibit 8-48 p. 3Subroutine VBA_Block_1_Fire

Executes when entity enters VBA 1

Private Sub ModelLogic_RunEndSimulation() On Error GoTo ErrorExit Call smSaveExcelWorkbook(1, FolderModel & FileNameDoe & ".xls") Call smExitExcel Exit SubErrorExit: MsgBox "ModelLogic_RunBegin Error #" & Err.Number & ": " & Err.DescriptionEnd Sub

VBA Output.doe

Exhibit 8-51 p. 241Subroutine ModelLogic_RunEndSimulation

Executes when model is complete

Beer Game.doe VBA

Exhibit 8-53 p. 242Subroutine ModelLogic_OnKeystroke

Private Sub ModelLogic_OnKeystroke(ByVal keyCode As Long) On Error GoTo ErrorExit If keyCode = Asc("d") Then DemandMean = DemandMean - 1 Siman.VariableArrayValue(VariableDemandMean) = DemandMean ElseIf keyCode = Asc("D") Then DemandMean = DemandMean + 1 Siman.VariableArrayValue(VariableDemandMean) = DemandMean End If Exit SubErrorExit: MsgBox "ModelLogic_OnKeystroke Error #" & Err.Number & ": " & Err.Description & vbCrLf & _ "keyCode = " & keyCodeEnd Sub

Executes when key is pressed

Arena VBA Smarts Models

\Program Files\Rockwell Software\Arena\Smarts 29 Number of Smart models

Category

Model Name Smarts*.DOE Application

VBA and Visual Basic® 001 VBA—VariableArray ValueVBA and Visual Basic® 016 Displaying a UserFormVBA and Visual Basic® 017 Interacting with VariablesVBA and Visual Basic® 024 Placing Modules and Filling in DataVBA and Visual Basic® 028 UserForm InteractionVBA and Visual Basic® 081 Using a Shape ObjectVBA and Visual Basic® 083 Ending a Model RunVBA and Visual Basic® 086 Creating and Editing Resource PicturesVBA and Visual Basic® 090 Manipulating a Module’s Repeat GroupsVBA and Visual Basic® 091 Creating Nested Submodels Via VBAVBA and Visual Basic® 098 Manipulating Named ViewsVBA and Visual Basic® 099 Populating a Module’s Repeat GroupVBA and Visual Basic® 100 Reading in Data from ExcelVBA and Visual Basic® 109 Accessing InformationVBA and Visual Basic® 121 Deleting a ModuleVBA and Visual Basic® 132 Executing Module Data TransferVBA and Visual Basic® 142 Creating Nested Submodels in VBAVBA and Visual Basic® 143 VBA—Animation Status VariablesVBA and Visual Basic® 155 Changing and Editing Global PicturesVBA and Visual Basic® 156 Grouping ObjectsVBA and Visual Basic® 159 Changing an Entity AttributeVBA and Visual Basic® 161 User FunctionVBA and Visual Basic® 166 Inserting Entities into a QueueVBA and Visual Basic® 167 Changing an Entity PictureVBA and Visual Basic® 174 Reading/Writing Excel Using VBA ModuleVBA and Visual Basic® 175 VBA Builds and Runs a ModelVBA and Visual Basic® 176 Manipulating ArraysVBA and Visual Basic® 179 Playing Multimedia Files Within a ModelVBA and Visual Basic® 182 Changing Model Data Interactively

Arena SMART files, a collection of small example models that demonstrate a variety of modeling techniques and situations commonly encountered using Arena.Smart files have been specifically designed for use as a training or reference tool.

Each SMART File is listed in one or more of the categories below.

Exhibit 8-54 p. 243Sheet Smarts

Summary

Visual Basic for Applications Introduction to Visual Basic for Applications VBA Integrated Development Environment VBA Object Model Overview Recording VBA Macros Excel Macros Arena Support of VBA

Next: Process Analysis and Improvement Applications Chapter 9: Customer Service Center Chapter 10: Supply Chain Management Chapter 11: Student Projects