_EMAGENIT_2019.png) |
Schedule | Classes | Manuals | Learning | Design | Contact | About |  |
Next Class: 9/12/2022
How our class can help you.
Our 2-day class shows you the critical Excel VBA skills needed to start making useful automated tools immediately.
It’ll show you how to design Excel VBA tools that perform time saving tasks like formatting worksheet data, looking up table values, and calculating downloaded data. Our class will also show you how to automate features like Excel's data tools, charts, and PivotTables to make rapid reporting tools.
Join us and our class will show you how to write Excel VBA code correctly, what Excel commands to use, how to set your problems up, and much more.
| Free Repeats Repeat your Excel class with us for an entire year for free. |  |
| NASA Project See how we helped NASA with the Mars Rover using Excel. |  |
| Discounts We provide discounts to active / retired US Military and DOD. |  |
| Customers View our customers like Apple, Boeing, and the US Army. |  |
Key Excel VBA topics covered in class in class.
Click to enlarge, scroll < > if hidden.
Available: Public >, Virtual >, Onsite >
How we run the class: We focus our training on what our customers need. When training begins, we analyze those needs and shift our training outline appropriately. We will stress topics or add topics that our customers want. No two training sessions are ever the same with EMAGENIT.
Day-1
Day-2
Select our beginning Excel VBA training if you or your group have:
Phone Number: 1.805.498.7162
Business Hours: 8:30 - 5:00 PM PT
You can email us at info@emagenit.com >
Contact us by form.
The VBA Editor is where you view, type, edit, debug and run your VBA code >. VBA code is typed in what are called modules >. You can also use the Editor to build userforms which are basically floating screens that display ActiveX controls.
The VBA Editor has many windows that can be displayed depending on your coding tasks. You activate the VBA Editor in Excel by pressing the Alt+F11 keys on your keyboard.
When observing VBA code in the Editor, you will see the code is organized in what are called projects. A VBA project in Excel is defined as a collection of VBA modules, userforms and various other programming elements, there is only one VBA project per workbook file.
VBA projects are automatically created in Excel when a workbook file is created so you do not have to create one. To save a VBA project and its modules, you save the workbook file that contains them as an Excel Macro Enabled Workbook. To open a VBA project, open the workbook that contains it.
Projects are viewed in the Project Explorer window in the VBA Editor. The Project Explorer window is presented in the picture below and is labeled Project -VBAProject. It's default position is in the upper left-hand side of the Editor window and looks like a file tree.
If you have multiple Excel files open at once, then you will see multiple projects in the Explorer window. To display any project item, just double click on its icon in the Project window. When first starting, it is better to have just one workbook file open so you do not get your projects confused.
Click to enlarge, scroll < > if hidden.
The commands to open the Editor windows are found under the View menu in the Editor menu. In particular, to open the Project Explorer window, proceed to the Editor menu and select View / Project Explorer. To close a window, look in the upper right-hand corner of the window and click the black x. You can also move an Editor window around by clicking and holding on its caption bar and dragging it. However it is very hard to get some of Editor windows to redock where you want them. It involves taking an edge of the window you are docking and dragging and moving it against the edge of the Editor window. This better done on the main computer monitor, not the added screens. It is highly recommended when first starting that you leave them alone in the position they come up in.
The Editor toolbars are grouped under Toolbars in the View menu. Just check a toolbar command to make it appear and uncheck it to make it disappear. To dock a toolbar, just click and hold on its caption and drag it over an Editor window edge. You can also double click on the caption bar of a toolbar as well to quickly dock it in its last position. To undock a toolbar from the Editor, find the three vertical dots on the left-hand side of the toolbar, click and hold, and drag it.
When using the toolbars, the author leaves the Standard toolbar docked at the top and the Debug and Edit toolbars floating free in the editor. A note here, if using multiple monitors, a toolbar that is not docked may appear on another screen so look around for them.
A procedure is defined as a named group of statements (a block) that are run as a unit. A VBA procedure is defined by a beginning declaration statement; Sub, Function, or Property; and an ending statement with statements in between. In a procedure, a statement is simply a complete line of code.
A VBA procedure is tracked by its name. Its name may be up to 255 characters long, alphanumeric, and you can use an underscore in the name but no spaces. Try to keep your procedure names short but meaningful as in being able to look at the name and figure out what purpose it generally serves. If you create long names, you will tire of typing them.
You can have many different procedures in the same module > just make sure to name them uniquely. Try to put procedures in the same module that relate to each other in task.
VBA procedures are used to perform many tasks such as automating Excel’s environment, communicating with databases, calculating formulas, analyzing worksheet data, creating charts, inserting and deleting columns...etc.
What you want to do with Excel VBA determines the type of procedure to build. A common mistake is that all the code in a module runs at once. In reality, only one Sub procedure runs at any given time and only when it is specifically executed by clicking a button, call statement,...etc.
Click to enlarge, scroll < > if hidden.
Each line of code in a procedure is called a statement. The block portion of a VBA procedure is constructed from three types of statements: executable, declaration and assignment statements.
The statements are placed between a procedure’s beginning and ending statements (i.e. Sub and End Sub) and perform the procedure's task; what you are trying accomplish.
Click to enlarge, scroll < > if hidden.
VBA procedures are typed and stored in a module that is viewed in the VBA Editor > as previously discussed.
Modules form projects and a project > is stored in a workbook file. There is only one project per workbook file unlike other programming environments. Save the workbook, save the VBA code.
When saving the workbook, make sure it is saved as a macro enabled workbook which is a .xlsm file. Saving it as a .xlsx file will strip the VBA code from it when it is closed. Distribute the workbook and the code goes with it.
Procedures are executed or run to perform their tasks. When a procedure is run >, its statements (i.e. lines) are executed in a top-down line by line fashion performing operations. Think of reading a page in a book.
Note that typing a procedure in a module does not run it. You must do this after typing it by variety of different methods. Until you run it, it is just basically text sitting in a document.
A simple way to run a Sub procedure is to type it in a Standard module, click inside the procedure boundaries with the mouse, and press the F5 function key. You do this while debugging it before assigning it to a button.
Note this technique will not work if the procedure has an argument list, names between the ()'s like format_table( ByVal ws As Worksheet).
Excel VBA has two basic types of procedures that you can create: Sub procedures and Function procedures. A third type can also be made called a Property procedure.
Sub procedures are written when you want to command Excel like creating a chart, analyzing data, coloring cells, copying and pasting data...etc. Sub procedures can be run via shapes, pictures, shortcut keys, events, and the F5 function key in the Editor.
Function procedures are generally created when you want to perform some type of calculation that will be used over and over again in your computer code. The word calculation here has a broad definition meaning anything you want to generate. You can also make your own custom worksheet functions that run from a worksheet cell formula. What makes a Function procedure different from a Sub is that it returns a value through its name as Subs do not.
Click to enlarge, scroll < > if hidden.
VBA code > is typed and viewed in the VBA Editor in what are called modules. A collection of modules along with other key elements is what is called a VBA project >.
In the VBA Editor >, a VBA module resembles and behaves like a Word document when typing. When viewed, a VBA module will appear in its own window within the VBA Editor. Think of modules as organizational units for your code, you add VBA modules as needed to a project to organize and store your code.
VBA modules come in three different flavors: Standard modules, Object modules, and Class modules. A Standard module is where you will be typing most of your code when starting off in Excel VBA. Think of it as the town square, everybody can easily get to you and talk to you. You can assign shape buttons to procedures in these modules to easily run them.
An Object module belongs to an Excel element like a workbook, chart, worksheet or a VBA element like a userform. You create events in them which are just Sub procedures that run when something occurs like pressing enter in a cell, opening a workbook, or clicking on a worksheet tab. These procedures can be used in place of clicking buttons to run code which is really cool but they require a lot of logic and programming know how to bring them under control. They tend to run when you do not want them to run so be careful.
A Class module is used to create a class for an object. Classes are what you will be using to command Excel, Word, PowerPoint..., but these are already made, you will just learn to run them. Object oriented programming involves advanced programming concepts which are better left to investigate till after you learn the fundamentals of programming.
All of the modules just discussed appear in the Project Explorer window as icons. They are organized as follows: Standard modules are found under the Modules folder, Object modules are found under the Excel Objects folder, and Class modules are found under the Class Modules folder.
One note here, they all do look alike so you have to look at the name of the module and then locate it in the Project Explorer tree to tell what type it is.
Click to enlarge, scroll < > if hidden.
As stated earlier, modules are made up of elemental building blocks called procedures >. Procedures are used to organize and run your code in a module. Think of a paragraph in a Word document and you are on the right track.
You type Excel commands, variables, arrays, loops, logic, functions,... in your VBA module procedures and then run the procedure to control Microsoft Excel features and perform various tasks.
Click to enlarge, scroll < > if hidden.
To view a module, just double click on its icon in the Project Explorer window in the VBA Editor. Standard modules are located under the Modules folder, Object modules are located under the Microsoft Excel Objects folder, and Class modules are located under the Classes folder. These folders can be viewed in the first image above.
Note that modules located under the Modules folder or Classes folder can be removed by right-mouse clicking on their icons in the Project Explorer window and selecting Remove. These two module types can also be added by proceeding to the Editor menu and selecting Insert / Module or Insert / Class Module.
Object modules can only be deleted by deleting the worksheet or chart sheet they are associated with and they are automatically added when you add a worksheet or chart sheet.
A common mistake is that VBA code must be stored in the same workbook as it is commanding. Actually your modules containing your VBA code can be stored in any desired workbook. The author generally stores his in a project workbook that a user clicks buttons in to run their procedures.
VBA code can search through Excel for the workbooks it needs or open them as needed, command them, then make new workbooks to store reports, charts, tables...etc. Keeping code in a single project workbook that is not attached to your data, charts, reports and so forth prevents you from making 500 copies of your VBA code thus making version control impossible.
A note here, when creating models in workbooks, you would have one model workbook that could be upgraded as needed and issued and you would upload and download the model parameters to the model using VBA storing them separately in workbooks and text files. This strategy is great for batch processing and trade studies.
How VBA commands Excel is through object expressions (i.e. Command().Command().Command() ) commonly referred to as "paths". If you do not assume things are active in your VBA code and create the proper object expressions to track the Excel elements your are commanding, the code may be housed anywhere as discussed above.
Relying on things being active is what causes most Excel VBA code to run slow and work intermittently. While using the Macro Recorder is a very good research tool for figuring out specific Excel commands and their syntax, it writes horrible code. Learn to track objects in your code using object expressions that do not rely on anything being active and your code will be very robust in execution.
The Set statements you see in the code above are used to lock onto an active sheet initially, but then the variable ws_data is used in the object expressions commanding Excel after that point. You are tracking the sheet and that code will never fail because the sheet becomes inactive because you open another workbook later in the code.
Also making Excel's elements active with VBA code so you can command them like with the way the Macro Recorder records is ultimately self defeating because it requires a lot of code, makes the code slow, is error ridden, and darn near impossible to work with multiple Excel elements in the same procedure.
The following set of instructions demonstrates how to run a simple Sub procedure that commands Excel from the VBA Editor >. The procedure's task will be to remove unwanted rows and column A in a worksheet data table.
1. Close Excel than reopen it. Open a single workbook.
2. In Excel, press the Alt+F11 keys to launch the VBA Editor. When the Editor appears, this is where you type and debug your VBA code.
3. Once the Editor is visible, proceed to the Editor menu bar and select View/Project Explorer. This will launch the Project Explorer window if it is not already visible. This window is where you track your VBA projects and add to them.
Note that VBA projects are attached to workbooks automatically, therefore to save your code just save your workbook. Note that you must save a workbook that stores VBA code as a Macro Enabled Workbook when saving it or Excel will strip the code from it when it is closed.
4. Find the Explorer window on the left-hand side of the Editor window, find the VBAProject(Book1) icon, click on it, proceed to the Editor menu bar and select Insert / Module. A Standard module > will be inserted into the VBA project and will open on the right-hand side. This is where you will type your Sub procedure.
Click in the module Window and type the following code. The green lines are comments which do not have to be typed, press the Tab key to get the indents. Indents make the code more readable and lines up blocks of code with their start and end lines (statements).
Click to enlarge, scroll < > if hidden.
Press Alt+F11 to toggle back to Excel, activate Sheet1, name the worksheet Data so the code above can track it, create the table you see below and make sure you bold the Week column categories. On the same worksheet, insert a drawing shape, a rectangle is fine. Right-mouse click over the shape and select Format Shape from the shortcut menu.
On the Format Shape dialog box, navigate to the Size and properties tab and select Properties / Do not move or size with cells. When the code adjusts the sheet, this prevents the shape from being distorted as rows are deleted.
Right-mouse click again over the shape and select Assign Macro from the shortcut menu. Click on the delete_data procedure name in the Assign Macro dialog box and click OK. You have just made a button to run the Sub procedure code.
Click off the shape onto a worksheet cell, when you click the shape again it will run the code. Click the shape and see if it removes the bolded category rows and Column A from the table. To edit the shape, right-mouse click over it.
Click to enlarge, scroll < > if hidden.
The procedure above uses a loop, For r = 2 to nrows...Next r, to scan rows on the Data worksheet. It is merely a counter but will loop the code inside its boundary n- times. Loops are used to represent among other things the start and stopping rows on a worksheet you want to process.
The Cells(r,1) command actually takes the counter r and uses it as an argument for its row argument. The second argument is the column index. We typed a 1 in the column index argument to represent column A on the worksheet. As the loop counts, the Cells command accesses cell's A2, A3, A4 and so forth down the sheet.
The Font.Bold commands return True/False if it encounters a cell that is bolded, that's the logic trigger, not always a number. The Rows().Delete commands lock onto the row and delete it at row r.
If you want to learn how to really use VBA, you must learn the basics of programming like loops, logic, variables, object expressions, how the code looks (syntax)...etc. Just switching on the Macro Recorder does not provide any engine or brains for your procedure to operate, the code records in an active format which makes it very twitchy and it will crash when data is in other workbooks which it always is.
You also begin to notice that VBA code can command any workbook and its data, the trick is figuring out the paths to the data sheets when you do not know the names of the workbooks. We show you how to do all these this in our beginning Excel VBA class.
Excel Training Services
Excel Classes - Business and Industry
- Beginning Excel for Business and Industry
- Intermediate Excel for Business and Industry
- Advanced Excel for Business and Industry
- Excel Dashboards for Business and Industry
- Beginning Excel VBA for Business and Industry
Excel Classes - Engineers / Scientists
- Microsoft Excel for Engineers for Scientists
- Excel Data Analysis for Engineers and Scientists
- Excel VBA for Engineers and Scientists
- Excel VBA Data Analysis for Engineers and Scientists
- Adv Excel VBA Design for Engineers and Scientists
- Calling Fortran DLLs from Excel VBA
Excel Macro Design/Consulting
Microsoft Excel Manuals
- Microsoft Excel Solutions Handbook
- Creating Advanced Excel VBA Apps
- Excel VBA Handbook for Engineers and Scientists
- Excel VBA App Design for Engineers and Scientists
EMAGENIT Company Information
US Military
Copyright © 2002-2022
EMAGENIT All Rights Reserved
