Visual Basics for Applications in Microsoft Excel 2016

Description

The aim of this course is to enable you to create sophisticated spreadsheet solutions in
Excel, with the ability to extract and manipulate data. Also to enable you to manage your
Visual Basic code and distribute your application to other users.

What you will learn

1 Visual Basic Explained

Aims of this session

To explain the constructs and principles required for manipulating Excel objects through
Visual Basic

On completing this session you will understand

• The Excel object model
• Objects and collections
• Methods and properties
• How to use VBA help

2 The VB Code Editor

Aims of this session

To enable you to use the facilities provided by Project Explorer, and to create modules and
basic subroutines

On completing this session you will be able to

• Use Project Explorer
• Create VB modules
• Understand coding conventions
• Create and comment basic subroutines

3 Variables and Data Types

Aims of this session

To understand variable types and how to declare them, and to understand scope of variable
declarations.

On completing this session you will be able to

• Understand variable types and when to use them
• Declare variables
• Understand scope of variables
• How to declare and use constants

4 Loops and Conditions

Aims of this session

This session enables you to create conditional branches in your programs, and also to iterate
a part of your program a set number of times or until a condition is true.

On completing this session you will be able to

• Use simple IF statements
• Use multiple IF conditions and SELECT… CASE
• Use the 3 main types of loop

5 Looping Over Collections

Aims of this session

To use your knowledge of loop constructs, Excel objects and collections to enable you to
loop over collections of objects.

On completing this session you will be able to

• Understand object variables
• Declare and initialise object variables
• Loop over collections

6 Event handling

Aims of this session

To develop an understanding of event driven programming, and enable you to program
event handlers

On completing this session you will be able to

• Understand how to program events
• Understand the important events to handle
• Assign macros to events

7 Procedures: Functions and Subs

Aims of this session

To understand the reasons for using procedures and the principles of creating them.

On completing this session you will be able to

• Decide when it is appropriate to use a procedure
• Declare functions, subs and appropriate arguments
• Understand how to pass arguments to a procedure
• Return a value from a function

8 Running code, debugging and error handling

Aims of this session

To enable you to use Visual Basic tools to run your code, to analyse the flow of your program
and detect bugs. Also to understand error handling.

On completing this session you will be able to

• Understand run mode and design mode
• Run code from your development environment
• Run code from your application
• Set breakpoints to stop your program
• Examine the value of variables at any point
• Step through a program or macro line by line
• Respond to error conditions in a way which is helpful to the user

9 Forms, Menus and Buttons

Aims of this session

To enable you to design and create forms and program the properties and events of your
form.

On completing this session you will be able to

• Design user friendly forms
• Work with form controls
• Create custom dialog boxes
• Program properties and events of the form

Further information

Nicholson Solutions
Provider:
Nicholson Solutions
Duration:
1 Day
Price:
POA
Availability:
On demand
Course type:
Classroom
Locations:
Aberdeen, Edinburgh, Inverness, Westhill

Contact Information

Nicholson Solutions

Westhill Business Centre
Arnhall Business Park
Westhill
AB32 6UF

Credentials

Locations