In Microsoft Excel, repetitive and lengthy tasks significantly affect office productivity. For example, if you are assigned to do a spreadsheet analysis that requires manually performing repetitive operations such as copy and paste, sort, etc., this can take up a lot of your time. Fortunately, Excel macros can cut that time significantly.
Contributed by Codex-M Rating: / 7 August 20, 2009
Macros are sets of instructions you give to the computer. Instead of doing the task yourself, you are asking Excel to do that job for you. To create macros, you need to provide Excel with instructions.
With macros, very long and complicated jobs can appear to be very simple and easy. This increases your efficiency as an office worker, engineer or even data analyst.
Security Issues and Requirements
To make this tutorial appealing to the majority of Excel users around the world, I am focusing on MS Excel 2002. However, if you are using a more advanced version such as Excel 2007, this tutorial also applies to you, since they have very similar features.
Excel macros deal with Visual Basic scripting, which has a history of security issues such as viruses. To make your computing environment secure before we start this tutorial, you need to take two very important precautionary measures.
First, use only trusted macros. You can't just use any macros from persons you do not know or download anything from the Internet. Make sure you double check it, and that it is coming only from your trusted macro developers.
Second, there will be times when security errors get displayed when you open the Excel workbook. This is due to the security level being used by the workbook. In order to adjust this, go to Tools -> Options -> Macro Security, and then under Security Level, set it to either “Medium” or “Low.”
Note that running a “Low” level of security is advisable only if you use files locally and are not mixing any macros from other untrusted developers. You should also make sure you have anti-virus software installed on your computer. However, to completely ignore all macro-related security errors, set it to “Low,” save the workbook, close Excel and open it again. Or if you happen to reopen your workbook with macros in it, just click “Enable Macros,” assuming you have already set the Macro Security Level to “Medium.”
In order to provide instructions for Excel to automate the job for us, we will be providing a set of instructions to what is called the “Macro Recorder.”
See the screen shot below:
To properly monitor all the macros that you have created, it is sensible to give a meaningful macro name, such as the one used in the screen shot above. You should also provide the short cut key (example Ctrl – o) which will be used to give Excel a go signal to start executing the instructions.
Important: do not use Macro shortcut keys which will conflict with Windows short cut keys, such as Control Z, Control A, Control X, Control C, Control V, etc.
Also, do not change the “Store macro in” entry; leave it at the default setting, which is “This workbook.”
Along with the macro name, it is also recommended that you provide a short description of the macro for traceability purposes.
To illustrate the power of the macro, let us say we have two sheets in a workbook which we have prepared. The first sheet is called “Raw URLs” and the second sheet is called “Summary URLs.”
This job will accept raw URLs for a certain website crawled by Xenu sleuth link crawler, and then extract URLs which are of the text/HTML type (this is the valid text page). The extracted URLs will then be copied to the summary URLs sheet. Only the following information will be shown in the summary URL sheet:
Address
Status-Text
Size
Title
The next step after this will be to sort the file size column by ascending order. And finally we will prepare it for printing (with print preview) for the first 100 URLs only.
There is a link pointing to the sample Excel workbook, which can be found at the end of this tutorial.
To do this manually, it takes me one minute, and 30 minutes for this whole process to complete. If we use a macro, it is obvious we will save a lot of time (we will compute this later).
We will develop an Excel Macro command for the example described on the previous page. This is the exact procedure (you can customize it on your own for your different application):
Step 1: Open the macro recorder. Go to Tools -> Macro -> Record New Macro.
Step 2: Fill in the fields using the information provided in the previous screen shot.
Step 3: When you press OK, YOU ARE ABOUT TO BEGIN GIVING INSTRUCTIONS TO EXCEL, so don’t do any unrelated activities because they will be recorded. The application will then record your action on the Excel spreadsheet and turn it into a Visual Basic script.
You will know this if you see this symbol (with stop button, see screen shot below) on the Excel spreadsheet. Let’s begin with the first sheet: “Raw URLs.”
Once you see this, start recording your instructions for Excel. Let’s start in the succeeding steps. Refer to the sample Excel workbook to assist in guiding you through this tutorial (found at the end of this article).
Step 4. We will begin by filtering column D (Type), by going to cell D1 and clicking Data -> Filter -> Auto filter
Click the Drop down arrow on Column D, and then filter/select “text/html.” This will filter and only show data for URLs belonging to that type.
Step 5. Select A1 to A500 (assuming that we will only be processing up to 500 URLs), and then copy and paste that data to the “Summary URLs” sheet, starting in Cell A1. This data contains the address.
Step 6. We will select C1 to C500 in the raw data sheet, and then copy and paste that data to the “Summary URLs” sheet starting in cell B1. The pasted data is the “Status-Text.”
Step 7. The next set of selected data will be the “size.” In this case, select E1 to E500 in the raw data sheet, and then copy and paste that to the summary URLs sheet starting in Cell C1.
Step 8. The last data to be transferred to the summary URL sheet is the title column. Copy and paste the data in Cell F1 to Cell F500 (in the raw data sheet) and transfer it to the summary sheet starting in Cell D1.
Step 9. Select A2:D500, and then click on: Data -> Sort -> Check No Header Row -> Sort by Column C and then select “ascending.” Click OK. This should sort the file size column in ascending order.
Step 10. Now that we have completed the sorting process, we will select area A1:D100 as our print area. This also assumes that we will only print the first 100 results.
Step 11. After selecting, go to File -> Print Area -> Set Print Area.
Step 12. After that, select File -> Print Preview, and then click “Close.”
Step 13. This is the last step. Click the Stop button of the macro recorder. This should stop Excel from recording your further actions.
The only thing that the human using this macro needs to do is finalize the printing setup or click Print, which is pretty straightforward.
Now that we have developed a macro command that will automate the segregation of raw URLs into a summary sheet, it is time to use it.
Kindly remove the auto filters left on the previous “Raw URL” worksheet and clear all contents in the “Summary sheet.”
Now press Control – o. What happened? It should automatically copy and paste and generate a summary data in less than two seconds. You will be saving a lot of time.
For example, if you are doing this 100 times in a day (because your job demands it), 100 x 1.5 minutes = 150 minutes you spend on it in a single day. In one month, without the macro, you have spent: 150 minutes x 30 = 4500 minutes or 4500/60 = 75 hours doing that work. Or, if we eliminate weekends, on average, that's still 150 minutes x 22 = 3300/60 = 55 hours, or the equivalent of more than one week of work.
Using the macro, it only takes a maximum of five seconds (including human intervention to print it manually at the end of the process) each time to do the entire job. This means 100 x 5 seconds = 500 seconds per day. Then in one month, you will be spending 500 seconds per day x 30 days = 15000 seconds per month or 15,000/3600 = 4.2 hours. Again, if we eliminate weekends, we're talking about 500 x 22 = 11,000/3600 = a little over 3 hours.
Thus the total number of hours you save per month is: 75 hours – 4.2 hours = 70.8 hours using macro. Or, eliminating weekends again, it's 55 hours - 3 hours = 52 hours. Either way, this is a lot, and you can better spend this amount of time with other productive jobs, thus optimizing your productivity.
Example workbook: (to activate the macro included in this workbook, press Control – o), set the security level first of your Excel to medium and Enable macros during opening:
ASP.NET Export to Excel and Word using VB.NET and C#
