Previous article Next article
Excel articles
Microsoft Excel Courses Teach Advanced Macro Techniques
Wed 11th April 2007
Excel users may already realise how recorded macros can streamline redundant actions and boost their productivity. However many macro users don't realise that they can further refine their macros by editing them directly. Microsoft Excel courses can illustrate advanced techniques to take macros to the next level.
When a user records a macro, it creates a list of instructions that Excel can use to repeat the corresponding actions. These instructions are written in a computer language called Visual Basic for Applications (VBA). VBA is designed to combine powerful programming commands with simple syntax that makes it easier to learn than most other computer languages.
Optimise recorded macros
Recording actions is a simple and powerful way to create macros but the method has limitations. Microsoft Excel courses teach students how to open up the macro code, read it, and change it to refine its operation. Consider a macro used in creating invoices. The user wants it to enter the current date into the "Invoice Date" cell. There are two ways to do this with a recorded macro and neither of them will produce the desired result.
If you enter the current date, such as January 15, when recording the macro, it will always use that date as the invoice date even when entered on a different day. You'll have to manually change the invoice date every time.
However, if you enter a function such as "=TODAY()" to try and avoid the problem, that will enter the invoice date correctly but that date will be dynamic. That means that every time the invoice is loaded, the invoice date will change to the current date.
Microsoft Excel courses can show you how to alter a recorded macro so that it calculates the correct invoice date as the date the invoice is created, then enter it as a static value so that date will not change when the invoice is loaded later.
Create new commands
Although Excel contains a wide variety of built-in commands, you may have unique needs. Microsoft Excel courses teach you how to build macros from scratch rather than by recording them, and how to make those macros available as new functions in formulas.
Consider the "=AVERAGE()" function. It takes a group of values and displays their average. This is fine if you have clean data but outlier values can invalidate the mean. "=TRIMMEAN()" can help, but it cuts off data whether they are outliers or not. Microsoft Excel courses can show you how to create a new command that can analyze each data point to see if it is an outlier or not and average only the sensible data to produce a more useful mean.
Find the right Microsoft Excel courses
The power of macros can make them seem overwhelming even to experienced Excel users. Look into the Microsoft Excel courses offered and find the one appropriate to your level. You will learn a host of new techniques that will open up a universe of possibilities for Excel workbooks.
Author is a freelance copywriter. If you would like more information on microsoft excel courses, you can visit https://www.stl-training.co.uk
Original article appears here:
https://www.stl-training.co.uk/article-151-microsoft-excel-courses-macro-techniques.html
Back to article list
Distribution notes
London's widest choice in
dates, venues, and prices
Public Schedule:
Buy now / Live dates
On-site / Closed company:
Get quote
Testimonials
Egmont UK Ltd National Account Manager Zoe Masters Effective Communication Skills
Shame there was not a café nearby for early arrivals! If continental breakfast could also be included or an optional extra that would be great. It is too early to eat breakfast before leaving home.
I thought Jon was fantastic, I was not looking forward to the course on a Friday, as I usually work at home and finish at 1pm on Fridays usually, but I was pleasantly surprised by how much I enjoyed the course. Jon's training style was spot on, he listened, was informative, I learnt a lot and feel more confident.
Thank you Jon!
Framestore Crewing Coordinator Andrew Draper Excel Intermediate
Well paced whilst also feeling supported in my learning.
I felt as though the group had slightly more knowledge of Excel and its uses, so to get up to speed and also learn new skills was hugely beneficial.
I am leaving knowing how I will apply some of my new skills to my work life, which will save me time in the long run.
Crown Prosecution Service District Crown Prosecutor Gary DOLBY Office 365 End User
I thought it was a really good day. So much information that I might have wanted to drill down on a couple of applications; say Teams and Word only in a single session.
More testimonials |
Clients
· Main client list
Article list
What's this?
PUBLICATION GUIDELINES