Collaborate on Invoice Word for Operations with Ease Using airSlate SignNow

Invoice word for Operations

In this video you will learn how to create your own Word macro from scratch. In the previous videos we made an invoice with fillable fields, a date picker, an automatic date field and so on. Now we will create a macro which will fully automate the calculations inside the invoice table. Such a macro will not only save the user a lot of time but it will also make sure that the calculation is always correct. Alright, so let’s start. We go to the “Developer” tab and click on “Macros”. Here we enter a macro name – let’s call it “table_calculation” – and in the dropdown field we select the current document, so that the macro is only available in this document. Then we hit “Create” and the Visual Basic window will open. You should now see a new module in your current document under “Modules”. And as you can see, macros often have to do with coding, but bear with me since this one will be fairly simple and I will explain everything step by step, so that you will understand it. Before we start writing the code, let’s clarify how we want the code to work. Obviously, we only need the table from our document, the rest of the document is not important for the calculation. We can split the table up in 3 different areas. The first area contains the header line and the empty line which we don’t need for the calculation. The second area contains all the product lines. Here, for each line, we will want to multiply the price with the quantity and place the result into the amount column. Note, that this whole second area can change in size, as in some invoice we might have only 1 line and in the next invoice it might be 8 lines. The third area of our table has a fixed size again and there we will do some more calculations in the last table column. With knowing this rough procedure, we can now start with the coding. Whenever we later on run the macro, it will execute all the code between the “Sub” and the “End Sub” lines. This means we have to write our code in between these lines. When we start writing code, we first of all need to declare some variables and constants. Let’s start with some constants. The syntax for constants goes as follows. We write “Const” so that the program knows it is a constant. Then we write down a name for our constant – in this example I want to create a constant for the column number of the price column. Afterwards we need to assign a value to the constant by typing the equal sign and then giving it the value. So by writing this line, we basically created a name for the fixed value 2, since we know that price column is always the second column of our table. Then we proceed in the same way for the quantity and the amount column. As we discussed earlier, we’ve already split our table into 3 areas, and since the top and bottom area have a fixed size, we also create constants for them. Next, we are going to declare some variables. For that, we always start with “Dim”, then then variable name, then the word “As” and then we define the data type of the variable. In this example we’ve declared the new variable t1 as a table. One cool thing about variables is, that we can declare multiple variables in a row. We simply separate their names with a comma and then at the end again define their data type. So, for declaring these variables, please just follow along – later on you will understand what we need these variables for, that we created here. By the way, the data type “Integer” is used for whole numbers and the data type “Double” is used for decimal numbers. Now we start with the actual code. Since we want our program to work with the table from our document, we assign that table to our t1 variable. We do that by writing “Set t1 = ActiveDocument.Tables(1)”. So, this line of code searches in our current document for the first table there is and then assigns that table to the variable t1. As our table can have a variable amount of product rows, we first of all have to find out how many rows our table has in total. For that, we use our noOfRows variable that we created and say that this variable equals to t1.Rows.Count. “t1” here is our table object, “Rows” is a property of that object and “Count” is a predefined method that our table object can perform. So, this line of code counts the number of rows in our table and assigns that value to our variable. What we will want to do now is, to find out at what row our product lines starts and at what row they end. Calculating the starting point is very easy. This is simply one row AFTER all our header rows. And the number of the last product row is the total number of rows (that our code already figured out) minus fixed the number of bottom rows. By knowing these numbers, we know in which line we need to start with the multiplication calculation and we will continue doing this calculation for EACH row until we reach the last product row. To perform such a repeating task, we will use a so called “For” loop. So, how this works is, that the variable “i" starts out as the number of the first product row. Then some operations will be performed within the loop and at the end of the loop, the code jumps back to the beginning of the loop, increases the “i" variable by 1 and again runs the code within the loop. And this will go on until “i" reaches the number of the last product row. This concept will become clearer in a second. And by the way guys, if you like this video so far, please hit the thumbs up button to support me on this channel. It is only one click for you but helps a lot in growing an audience and in turn I am able to create more free tutorials for you. Thank you for your support. Alright, so to calculate the amount of every row, we will need to multiple the price with the quantity of that row. Let’s start with the quantity, as this one is a bit easier. We have the qty variable, that we declared at the beginning and now we need to extract the quantity value from the current table row to assign it to our variable. We know that we have to work with the table object. This table object has a “Cell” parameter to access each cell in that table. For the program to know, which exact cell we want, we will have to give it a row number and a column number. The row number for the first product row is 3. But remember that we are in a for loop and later on, we will want to do the calculation for row number 4, 5, and so on. So, instead of using the fixed number 3, we will use the variable “i” as it will be incremented with each cycle through the for loop. Now for the quantity column, we simply insert the constant, that we already created earlier. At this point the program knows which cell we are looking for. To get the text out of this cell, we need to add “.Range.Text”. And like that, we have now extracted the content from the quantity column of the current row. But since the code will extract the value as a text, we still need to convert this value to a number. And we can do that by using the “Val” function. So, whatever string is inside the parentheses of the function, will be converted into a number, IF the string contains a number. And therefore we place the whole code that we wrote into the parentheses of this function. Then we continue with the price variable. We again select the cell as we did before, but this time we use the constant for the price column. Since our price always has a dollar sign at the beginning, we need to get rid of this sign. And for that, we can use the “Replace” function. Within the parentheses of the function, we give a value to this function, which in our case is the whole cell content. Then we tell the function what symbol we want to replace (and we have to put this symbol in quotation marks) and at the end we need to specify what we want to replace it WITH. So if the quotation marks are empty, then it will be replaced with nothing – it will be removed. This line of code gives us the content of the cell without the “$” sign and now we have to convert the content into a number again by using the “Val” function. By now we have the numerical values of the quantity and the price of the current row. With this information we can continue with the mathematical operations. The amount will equal to the quantity multiplied with the price. To place this amount value into the table, we need to access the content of the correct cell again and then we assign to this cell the amount value. But since we want to show the amount value in a proper formatting with the “$” sign in front and 2 decimal places, we put the amount value into the Format function and give it the correct formatting rule. Within our For loop, we will also calculate the subtotal. The subtotal will be the CURRENT value of the subtotal (which at the beginning is 0) plus the amount value. When we are in the next cycle of the loop, the subtotal will then already have a value and then the amount of the next row will be added to this overall value. And with this, we have now finished the calculations within the product lines. The only thing left is the tax calculation and the total calculation. Since some of you might be creating invoices for different states or even different countries, we will make this tax rate variable by implementing an input box. And this is fairly simple. We say that the tax rate equals to the value that you enter into the InputBox. In the parentheses we can also enter some instructions that we want to show within that InputBox. By knowing the tax rate, we can now calculate the overall taxes of our invoice. This is done by multiplying our subtotal value with the tax rate. And of course we have to divide the tax rate by 100. Then the total value is the sum of the subtotal and the tax. Same as with the amount value, we have to insert our calculated values into the cells of the table. Since the number of product rows can vary for every invoice, we cannot use a fixed row number. But rather we will use the lastProductRow variable as a reference and then increment this value by 1, so that we end up in the next row after the last product row. For the column, we again use the amount column which is the last one in our table. Then we assign the subtotal to this cell content and format it properly again. We repeat this also for the tax and the total, but here we have to increment the row numbers further. Now, of course we also want to show the customer which tax rate we used for the calculation. And therefore, we access the cell which contain the words “Sales Tax” and we give this cell the content “Sales Tax” together with the value of the tax rate and the “%” symbol at the end. So, you can see that multiple texts or strings can be joined by using the “&” symbol. With that, we finished the code for the macro. Whenever you want to execute it, you simply go into the “Developer” tab, click on “Macros” and hit the “Run” button. As you can see, everything works perfectly and all the numbers in the invoice are being calculated automatically. You will find a download link for this invoice template in the video description. When you download my template and check the code, you will see that it is a bit more advanced, as I implemented some error handling into the code. For example, when you accidently enter a character into the InputBox, then you will get a warning. And with this, we have finished the 3-part video series about invoices in Word. If you learned something from these videos, please support me by liking the videos and subscribing to the channel. With your support I will hopefully be able to create more helpful videos in the future and share my knowledge on these topics completely for free. Thank you very much and I see you in the next one.