Capacitas Logo
About Us
Our Solutions
Our Services
Industries
Our Clients
Investor Relations
Training
Strategy
Benefits
Accreditation
Training Calendar
Onsite Training Quotation
Seminars
Workshops
Feedback
Research
News & Events

Capacity Planning and Performance Management
using Microsoft Excel VBA

Capacitas Training Centre,
London, United Kingdom.
2 Days

Learn how to use MS-Excel to:

  • Increase productivity by automating data analysis tasks
  • Automate the delivery of data and charts to the web
  • Build performance and capacity models of OLTP and batch systems
  • Improve the quality of your Excel solutions

Course Agenda

Day 1
9:00 - 9:30 Registration
09:30 - 10:00 Introduction
  • Visual Basic for Applications
  • Excel Product Versions
  • Macro Security
10:00 - 11:00 Writing Macros
  • Recording a Macro
  • Relative References
  • Debugging
11:00 - 11:15 Morning Break
11:15 - 12:15 Writing Macros (cont.)
  • Error handling
  • Event driven macros
12:15 - 13:15 Lunch
13:15 - 14:45 Writing User Defined Functions
  • Argument and function types
  • UDF performance issues
  • Volatile functions
  • Dynamic Ranges
14:45 - 15:15 Queuing Functions
  • Example: Erlang B blocking function
  • Example: Erlang C blocking function
  • Example: M/M/N queuing function
  • Example: M/G/1 queuing function
  • Example: M/G/N queuing function
15:15 - 15:30 Afternoon Break
15:30 - 16:15 Creating Menu Bar Items and Add-Ins
  • Menu bar items
  • Add-ins
16:15 - 17:00 Importing Data
  • Opening a file
  • String manipulation functions
  • Removing unnecessary and repeated headers from imported data
  • Example: Importing and analysing UNIX SAR data
  • Example: Importing and analysing Windows System Monitor data
  • Example: Importing and analysing W3C web logs
 
Day 2
9:30 - 10:30 Building a GUI
  • Adding navigation buttons
  • Cleaning worksheets and charts
10:30 - 11:00 Publishing to the Web
  • Publishing data
  • Publishing charts
11:00 - 11:15 Morning Break
11:15 - 12:15 Publishing to the Web (Cont.)
  • Publishing interactive content
12:15 - 13:15 Lunch
13:15 - 14:15 Building Performance and Capacity Models of Batch Systems
14:15 - 14:45 Building Performance and Capacity Models of OLTP Systems
14:45 - 15:00 Afternoon Break
15:00 - 17:00 Case Study: Modelling a Billing System

Course Brief

MS-Excel is probably the most important commonly-available analysis tool for IT professionals. This course describes how to use Visual Basic for Applications to extend the functionality of Excel to support performance and capacity management.

Course Prerequisites

Course Audience

  • Capacity Planners
  • Network Planners
  • Network Engineering
  • Performance Analysts
  • Technical Architects
  • System Architects

How Your Organisation Will Benefit

Attending this course you will learn how to:
  • Decrease the time spent manipulating data; allowing more time for analysis
  • Increase the impact and understanding of models through delivery of a GUI
  • Automatically publish data and charts to the web
  • Produce bespoke models that are not constrained by the functionality of a commercial modelling product

Or consider an onsite event.
© Capacitas Ltd 2008 Privacy Policy Code of Professional Practice