Video instructions and help with filling out and completing Are Form 944 Pr Fractions

Instructions and Help about Are Form 944 Pr Fractions

Hi my name is Stephanie Krieger and I'm a Microsoft Office MVP in this video I'm going to be demonstrating a few tasks from the article titled using VBA to format long documents in the 2021 office system in a fraction of the time and you can find that article on the MSDN office developer center well most of what I'm going to show you can apply to your work with VBA in many office programs but since this demo is about formatting long documents let's go ahead and jump over to word if you record macros you know that those macros can save you a lot of time on repetitive tasks like formatting the sixty-eight tables that I copied from Excel into the sample report for example but you also know that recorded macros have limits so in this example I would need to run the macro separately for each table right I'd need a different macro for each type of table that requires unique formatting and I'd probably still have to take some actions manually for things that I can't record all right well in this demo we're going to look at a couple of different types of VBA constructs that you can use to save a lot more time than that first we're going to look at a loop which is a type of statement that enables you to loop through multiple instances of an object such as to apply your formatting to all tables in the document at once and next we're going to look at conditional statements and that lets you see how you can execute different actions in the same macro based on conditions you specify like when you need different formatting based on the type of table okay so take a look at this document I've got two different types of tables so right here you see products by region and then below it there's companies by quarter and I'm gonna go ahead and scroll through this document you can see those two types of tables are repeated many times throughout the document now I want those tables to all have some formatting in common for consistency right like I want them to be horizontally centered on the page and I don't want them to all have table Styles that can have some nice formatting and look clean but maybe I want them to also be a little bit different so that if someone is looking for products by region tables they can find them easily by a different color maybe a different size than the companies by quarter tables alright let's start by recording a macro for this first type of table click into the table start the macro recorder we'll just use the defaults there on the table tools design tab apply the style I need on layout tab go to table properties I'm gonna measure in percent because I want this table to take up 80% of the width of the page and then center it horizontally on the page click OK and the macro recorder I'm going to use alt f11 to get into the VB editor so we can look at that recorded macro you can use the Developer tab as well you can find the VB editor on the Developer tab now when you look at this recorded macro you immediately see the limitation of recorded macros and that is the fact that you have to act on the active selection right in this case it is the selected or active table let's try writing this macro instead the first thing that I'm going to do in my macro is I'm going to declare an object variable for tables and I'm going to do that because I'm going to use a loop a for each next loop in this case to loop through the collection of all tables in the active document now I'll go ahead and put the end part of the statement in right away so that I don't forget later because you cannot have for each without next you will get an error when you try to run the macro repeating the object variable here is optional but I really recommend doing it when your macros get more complex you want to easily be able to see which start and end parts go together keep your code organized and let's go ahead and copy the code from the recorded macro I don't need to retype it I can just edit it so I'm going to replace selection dot tables one with the object variable in this case and I can repeat that on each row but you know what I can also go that even one better notice how the object variable is repeated on each line just like selection tables one in the recorded macro well whether you're using an object name or a variable you often don't have to repeat the same code over and over in fact anytime you see the same code repeated in your code should be assigned to you that you might be able to do less work if I group these statements together since they're all acting on the same object I could type less code and I can give the code less work to do as well I'm going to go ahead and add a with end with statement and notice what I can do here is delete the repeated instances of the variable name again a paired statement with requires end with and what this grouping statement does is it helps to organize your code and save you typing but when you start writing longer code it also can make your macros run more efficiently so look for example at the recorded macro at the top of the screen well vba has to hit that table four times right select the table and perform that first action select the table again and perform that.