Excel - vba
Short course
In London
Description
-
Type
Short course
-
Location
London
-
Duration
1 Day
this two-day course is designed to give participants a sound working knowledge of vba, to develop and automate spreadsheet solutions. the modular design includes practical examples and case studies. participants will acquire a robust vocabulary to devise real solutions and promote effective use of microsoft excel. Suitable for: no previous programming skills are necessary, although a good working knowledge of excel is an advantage including using some functions.
Facilities
Location
Start date
Start date
Reviews
Course programme
this two-day course is designed to give participants a sound working knowledge of vba, to develop and automate spreadsheet solutions. the modular design includes practical examples and case studies. participants will acquire a robust vocabulary to devise real solutions and promote effective use of microsoft excel.
the uses that excel vba may be put to include:
- automate a repetitive task - speeds it up, makes the task more reliable because the macro/routine will always work.
- automate awkward tasks - you no longer need to remember how to do the task afresh every time, just run the macro/routine.
- simplify tasks for non-experts - create the macro/routine that will allow a user with little excel skill to perform complex tasks in excel
- perform a task that is not possible in standard excel e.g. new calculating functions for commissions or discounts, importing or exporting data at the click of a button
target audience:are you ready?
no previous programming skills are necessary, although a good working knowledge of excel is an advantage including using some functions.
in tuition use objective-based training. each exercise is clearly described first, run through and then reviewed. this means that by the end of the course you will be able to:
- record macros and use relative and absolute references to avoid problems
- use the visual basic editor to write, edit, organise, debug and run procedures
- use loops and conditions to automate repetitive or awkward tasks
- create and use custom functions to enable and to simplify calculations and formatting
- use conditions to determine what action must be taken depending upon the spreadsheet data
- create user-interactive procedures to ask the user what to do next or what data should be used
- create forms to simplify a procedure like data entry
- transfer the data from a macro/routine to a spreadsheet
- transfer the data from a spreadsheet to a macro/routine
- debug the code if it goes wrong
- trap errors and give users more friendly error messages than the default messages
After completing this course, participants will be able to:
macros
- record
- absolute and relative reference
- where macros can be stored
- running macros using buttons and keyboard shortcuts
- screen components and their uses
- modifying it to make it easier to use
- edit the code to improve its functionality and speed
- create new modules for storing code
- if condition to make the code perform a task if the conditions (the data) is right
- select case condition to improve your understanding
- to loop the code so that a macro works all the way down a column
- add a condition to a loop to stop it at the right point
- understand the different types of loops
- store data in a variable
- use variables in calculations
- use variables to store user input data
- declare variables to stop using so much memory
- understand the scope of variables to make the work in other worksheets
- understand the use of static and constant variables
- use the immediate window to see code results
- run code one line at a time to see the effect of each line
- set break points to speed up debugging
- create a form to simplify data entry
- use the different types of controls to input the data into e.g. text box, drop down list box
- add code to command buttons to perform specified actions
- transfer the data from a form to a spreadsheet
- create calculating functions especially for your operation
- include conditions within a function to use with e.g. commission or discount calculations
- understand the excel object model to prepare you for more advanced code writing
excel - formulas and functions
excel - intermediate
excel - introduction
tailored training is available
Excel - vba