Microsoft, Microsoft Press, ActiveX, Calibri, excel, Groove, infoPath, internet explorer, Ms-dOs,. OneNote, Outlook, PivotTable, PowerPoint. P R E M I E R. Microsoft. Excel VBA (Macros). Premier Training Limited. 4 Ravey Street conjunction with the Excel reference manual. Premier. tions (VBA) to extend the power of Microsoft Excel You should have some knowledge of or experience using the VBA or classic Visual Basic programming.
|Language:||English, Spanish, German|
|Distribution:||Free* [*Registration needed]|
With the introduction of Excel Microsoft made a number of changes to the wanting to create macros and work with VBA and for those migrating to Excel. Excel Level 6: VBA Intermediate). Contents. IMPORTANT NOTE. Unless otherwise stated, screenshots in this lesson were taken using Excel running . Excel® VBA Programming For Dummies®, 3rd Edition. Published by. John Wiley & Sons, Inc. River Street. Hoboken, NJ mtn-i.info
Whenever PageSetup. Zoom is set to False, the properties that determine how Excel scales the Excel worksheet are PageSetup. At the same time, if the PageSetup. This leads us to… Row FitToPagesTall property for purposes of setting the number of pages tall to which the relevant Excel worksheet is scaled to when printing or, in the case of the example in this tutorial, converted to PDF.
This is done in… Row In other words, you use the FitToPagesWide property to specify the number of pages wide the relevant Excel worksheet is scaled to when printing or converting to PDF as in this example. As I show in this example, you can make several changes or adjustments that influence the resulting PDF file by using the PageSetup object. CenterHeader property in row 2 above. The printed area doesn't include the Power Spreadsheets logo, the preliminary title of this blog post or the hyperlinked text to this page which appear in the first 3 rows of the Excel worksheet.
This is a direct consequence of using the PageSetup. PrintArea property to specify the range from cells B5 to F as the print area, as done in row 4 above. The header row of the Excel table is repeated in all of the pages within the converted PDF file. This is achieved by the appropriate use of the PageSetup. PrintTitleRows property in row 5 above. All of the columns in the original Excel table appear in an appropriate manner.
In other words, the table that appears in the PDF file has been scaled in such a way that it is 1 page wide. If you go back up, you'll notice that both previous examples macro 1 and macro 2 didn't fit the print area appropriately to the page. The fifth column Favorite Food Ingredient was left out in macro example 2 or published by itself in the last pages of the PDF file in macro example 1. You can go back to rows 6, 7 and 8 above to see how the VBA code looks like.
You can use this parameter to set the filename of the resulting PDF file. Visual Basic for Applications allows you to do either of the following: Specify the full path and filename of the file.
Specify just the filename. In this case, the file is saved in the current default folder. In the previous macro examples, I've not done much with the Filename parameter. More precisely: In the first macro example, I didn't even include the Filename parameter it's optional.
This resulted in the converted PDF files being saved in the default folder the Documents folder, in my case. The use of the Filename that I make in these first 3 examples may work for you in certain circumstances.
After all, the material structure of those macros is enough to help you save Excel worksheets, workbooks, ranges or charts as PDF files. However, in several situations, you'll want to use the Filename parameter in a slightly different way for purposes of creating more sophisticated macros that save Excel files as PDF. Notice, however, how I've specified the full path of the file using the Filename argument.
The consequence of this change is that the resulting PDF file is no longer saved in the default folder. Instead, the PDF is saved in the folder that you specify with the Filename argument. In the example above, the file is saved to a OneDrive folder called Power Spreadsheets. You don't necessarily need to hard-code all of the details in the Filename parameter. You can, for example, concatenate different items for purposes of building a more flexible filename structure.
In other words, if you use this Filename structure, the new PDF file is saved in the same folder as the source Excel workbook. The reason for this is that the Workbook. Path property returns a string with the complete path to the relevant workbook. In the case of the example above, the relevant workbook is the active Excel workbook returned by the Application.
ActiveWorkbook property. You can take this flexibility even further. Name property using the Application. ActiveSheet property to return the name of the active worksheet as the actual filename within the Filename parameter.
The file path continues to be provided by the Workbook. Path property. In this particular case, the macro uses i the Application.
But what's even better is to build your macro into your spreadsheet, and the best tool for that is buttons. So first, before we start coding, let's add a button to run our macro. Now, when you click the shape which we just turned into a button, Excel will run the macro without having to open the code each time.
There's one other thing to note before we get started: saving your spreadsheet with Macros. By default, Excel spreadsheet files with an. Go ahead and do that to save your spreadsheet before we start coding. Copying and pasting is the simplest way to move data around, but it's still tedious. What if your spreadsheet could do that for you? With a macro, it could. Let's see how to code a macro that will copy data and move it around in a spreadsheet. This is a sample employee database with the names, departments, and salaries of some employees.
The macro had Sub Nameofmacro and End sub at the top and bottom line of the code. These lines must always be included. Excel makes that easy, too: When you type in "Sub" followed by the macro name in the beginning of the code, the End sub is automatically inserted at the bottom line.
Cutting is quite easy and follows the exact same logic as copying. Therefore, you need these lines to paste your cells with VBA: Range "Insert where you want to paste". Select ActiveSheet. Select - ActiveSheet. Paste Copying, cutting, and pasting are simple actions that can be done manually without breaking a sweat.
But when you copy and paste the same cells several times a day, a button that does it for you can save a bunch of time. Additionally, you can combine copying and pasting in VBA with some other cool code to do even more in your spreadsheet automatically.
Adding Loops to VBA I just showed you how to take a simple action copying and pasting and attach it to a button, so you can do it with a mouse click.
That's just one automated action. When you have the code to repeat itself, though, it can do longer and more complex automation tasks in seconds. This type of faulty data structure is not unusual when exporting data from older programs.
This can take a lot of time to fix manually, especially if the spreadsheet includes thousands of rows instead of the small sample data in this project file. Enter this code in a module, then look at the explanations below the picture: This line makes sure the loop starts at the top-left cell in the sheet and not accidentally messes the data up by starting somewhere else.
This means that the loop will run times. The number of times the loop should run depends on the actions you want it to do. Use your good sense here. This line recognizes the active cell and tells Excel to move 3 rows down and select that cell, which then becomes the new active cell.
If it was every fourth row that was misplaced in our data, instead of every third, we could just replace the 3 with a 4 in this line. This line tells Excel what to do with this newly selected cell. In this case, we want to delete the cell in such manner that the cells to the right of the cell are moved left.
That is achieved with this line. If we wanted to do something else with the misplaced rows, this is the place to do it.
This line tells Excel that there are no more actions within the loop. In this case, 2 and 5 are the frame of the loop and 3 and 4 is the actions within the loop. When we run this macro, it will result in a neat dataset without any misplaced rows. In this section, I explain how you can add the Developer tab to the Ribbon.
At the end of the step-by-step explanation, there's an image showing the whole process. Note that you only need to ask Excel to display the Developer tab once. Assuming the set up is not reversed later, Excel continues to display the tab in future opportunities.
Once you are in the Excel Options dialog, ensure that you are on the Customize Ribbon tab by clicking on this tab on the navigation bar located on the left side. In this case, the Developer tab is not be shown in the Ribbon.
If this box has a check mark, the Developer tab appears in the Ribbon. If box already has a checkmark, you don't need to do anything you should already have the Developer tab in the Ribbon. Excel takes you back to the worksheet you were working on and the Developer tab appears in the Ribbon. The second option which requires programming is more complex than the first, particularly if you are a newcomer to the world of macros and you have no programming experience.
Since this guide is aimed at beginners, I explain below how to record an Excel macro using the recorder. If your objective is to only record and play macros, this tutorial likely covers most of the knowledge you require to achieve your goal. As explained by John Walkenbach one of the foremost authorities in Microsoft Excel in the Excel Bible , if your objective is to only recording and playing macros:. However, if you want to benefit from Excel macros to the maximum and use their power fully, you will eventually need to learn VBA.
As Mr. Excel Bill Jelen another one of the foremost Excel wizards and Tracy Syrstad an Excel and Access consultant say in Excel VBA and Macros , recording a macro is helpful when you are beginner and have no experience in macro programming but…. Therefore, I cover some of topics related to Visual Basic for Applications more deeply in other tutorials. By now you have added the Developer tab to the Ribbon and you are aware that there are two different tools you can use to produce a macro, including the recorder.
You're ready to make your first Excel macro. To do it, simply follow the 7 easy steps which I explain below. If relative reference recording is turned on, as in the case of the screenshot below, you don't need to click anything.
I may explain the use of relative and absolute references further in future tutorials. However, for the moment, ensure you have turned on relative reference recording.
If, at the time of recording the active cell is A3 and you fail to turn on relative recording, the macro records that it must:. As you may imagine, this macro does not work very well if, when using it, you are in any cell other than A3. The following image shows how this would look like if you are working in cell H1 and activate the macro with absolute references explained above. The main advantage of saving macros in the Personal Macro Workbook is that those macros can later be used in future Excel files because all those macros are available when you use Excel in the same computer where you saved them, regardless of whether you are working on a new or different Excel file from the one you created the macro on.
Create a macro description. Having a macro description is optional. It is a good idea to get in the habit of recording this information every time you build a new macro so that you and your co-workers can always know what to expect from the macro when any of you run it. Harvey also suggests that you include the date in which the macro was saved and who created the macro. Step 5. Once you have assigned a name, set the location where you want to store the macro and if you wanted assigned a keyboard shortcut and created a macro description, click on the OK button to close the Record Macro dialog.
If you follow the 7 easy steps explained above, you're already able to start creating basic macros. Therefore, in this section, we set-up a macro that does the following three things:. I have already explained how you can get the Developer tab to show up in Excel. Since you only need to ask Excel one time to display the Developer tab, the image below only shows the actual recording of the macro. For this particular example, I have used the parameters described above when working with the Record Macro dialog.
More precisely: I hope you have found it easy to create your first Excel macro. At the very least, I hope that you realize that the basics of Excel macros are not as complicated as they may seem at first sight. I know that the macro we have recorded above is a very basic example and, in other posts about VBA and macros, I dig deeper in more complicated topics that allow you to set up more complex and powerful macros. However, it is true that the information in the previous sections of this Macro Tutorial for Beginners is enough to set up a relatively wide variety of macros.
In the Excel Bible , John Walkenbach explains that:. In most cases, you can record your actions as a macro and then simply replay the macro; you don't need to look at the code that's automatically generated.
I have quoted twice how John Walkenbach, one of the most prolific authors on the topic of spreadsheets, implies that casual users of Excel macros do not necessarily need to learn programming. However, this doesn't mean that you shouldn't learn programming.
If you are committed to unleashing the power of Excel macros, you will have to learn Visual Basic for Applications. Programming Excel macros using VBA is more powerful than simply recording the macros for several reasons, the main one being that using VBA code allows you to carry tasks that can't be recorded using the Macro Recorder.
If I had the space, I could go continue to create a really long list of examples of how programming with VBA is a superior way to create macros than using the Macro Recorder.
In fact, Mr. You have already learned how to set up a macro in Excel and, as you saw in the most recent sections, the macro is working.
In order to start learning how to program macros, it is useful to take a look at the actual instructions or code behind that you have produced when recording the macro. In order to do this, you need to activate the Visual Basic Editor.
The VBE window is customizable so it is quite possible that the window that is displayed in your computer looks slightly different from the above screenshot.