Excel™-Visual Basic – Worksheet Macros BPEV_VB2
This second level Excel™ course goes on to demonstrate how to use the built-in Macro Capture capabilities in order to get a start in VBA programming, where the macro is stored as an executable subroutine that performs a single function. We show how you can explore a macro that is produced by Excel™ and transform the VBA code into a routine that can be embedded into a full-fledged VBA application (that, by the way, is still considered a macro by Excel™).
This second part of the course explores more of the built-in capabilities of Excel™ and how you can maximize the utility of these capabilities in your projects. We already have a good grasp of what Excel™ can do for us: it is now a matter of responding to the informational needs of our clients (or employers). In this part of the course we focus on mastering the more hidden parts of what we can use Excel™ for, knowledge that is immediately useful in the workplace. We may use Excel™ to create illustrations for reports (proposals, evaluations, etc.) that would otherwise get lost in a sea of words. We can see some overlap between capabilities of Excel™ and those in MS-Word™ for creating presentations based on the numbers you produce in Excel™, but there is no comparison in terms of the sheer calculation power Excel™ brings to the table.
We dwell on this because there is a temptation to use Excel™-VBA as a programming language to replace QuickBasic of Visual Basic applications from the past. VBA does not run as efficiently because it is interpreted, but it has more power because it can tap into the Excel™ framework to make it do the heavy lifting where possible. By understanding more about this framework it is much easier to develop powerful applications that are less complex programming challenges but potentially highly complex integration challenges to make Excel™ do what you want it to do. The secret to success is to look at Excel™ macros – the way you manipulate things in Excel™ can be captured and represented in VBA subroutines called macros that provide good examples of how you can make VBA control the Excel™ framework.
At the end of this course you will be able to move up to VBA programming while keeping Excel™ functionality in mind, so that you can exploit the benefits of “hard coded solutions logic” with the flexibility of a formula-enabled worksheet that delivers the unique Excel™ capabilities. It is good to have choices, to exploit the tools available to you, and it is powerful that you can build tools that can be user-customized at the Excel™ level. We have a number of tools used in our course program that provide excellent examples of that capability that you will be able to create for new applications we have not even thought of yet.
The use of tables, and in particular pivot tables, is a common application to present information in a structured manner. Excel™ has capabilities to help in formatting the presentation of tables to make them easier to read for an audience. We will explore a number of options you must be aware of in order to be job-ready when you enter the workplace. Analytics can make use of a special feature called pivot tables and charts that help you to categorize information in different ways and to provide an interactive view of the data and to rotate the data for a different perspective.
One of the benefits of the Excel™ framework is that it comes loaded with functions that can be used to build powerful applications. Some functions are unique to VBA, others are unique to Excel™ framework, and then we see a huge amount of overlap. In fact, you can call framework functions from VBA by using the appropriate mechanism, so by learning the use of functions you further accumulate the skills you need to create business applications. In this course we will show you how to use the Excel™ Help as a substitute for a reference manual, so that wherever you go and work you will have the information you need to design and implement Excel™ logic to the fullest.
The Excel™ workbook is actually a series of database tables that you can load and manipulate. The nature of that database may be surprising: this is the same “engine” that drives MS-Access™ but the user interface has virtually no resemblance to the Access interface. Although you can also program MS-Access™ with SQL applications, you may be surprised that a similar capability exists in Excel™. We will explore the capability of using a complex workbook with multiple worksheets that are used concurrently in true database fashion, and how porting information from external data processing applications into Excel™ can open opportunities to do various analytics processes that are often impossible to do with those external applications. We will show how Excel™ manages cross-worksheet access, to set the stage for a more powerful VBA capability for managing data.
The final surprise is that many people already use VBA “subroutines” that are created with Excel™ macro-capture capabilities. We may be able to use manual routines to manipulate data: when you use macro capture the same manual routines are recorded as individual steps and turned into a macro. If you view such a macro you soon realize that you are using VBA to view the subroutines representing each macro. This is an excellent way to learn VBA programming – while the macro code may be very generic, operating in a step-by-step fashion, it clearly shows how you can interact with the Excel™ framework from a program. This may be streamlined if you get the hang of VBA programming, but sometimes the macro would just be embedded in (or called from) a business application you create.
Learning Formats BPEV_VB2
This course is currently available in a classroom setting (public or company private) with approximately 15 contact hours. The training should be followed by independent learning to gain hands-on practice with what has been learned in class. Therefore we recommend at least a one week gap to let the student master the subject matter before taking the next course.
PDF – Certificate Of Completion
Each course offers a certificate of completion that identifies the course, the student, and a brief description of the course. To receive a certificate the student must have attended at least 80% of the course sessions. This personalized certificate is forwarded to the student by Email.
PDF – Course Notebook
Each course includes a notebook in PDF format that provides the minimum knowledge the student must master in order to obtain the certificate. In the notebook you will find references to other study materials. Students receive the notebook by Email when their registration is confirmed.
PDF – Program Overview
An overview of this study program can be downloaded from the website by right-clicking on the program link on the enquiry page.
PDF – Current Training Schedule
A list of upcoming training sessions can be downloaded from the website by right-clicking on the schedule link on the enquiry page.
Registration – Service Providers
To register for any training course please look on the enquiry link page of your service provider (from where you accessed this website). On the page you will find a registration request form where you can order the course that you are interested in. The availability dates will be provided to you, along with payment instructions if you decide to go ahead.
|