Presented below is a brief introduction on how to type and run a VBA Sub procedure in Excel VBA.
Sub procedures form the backbone of the programs written to command Microsoft Excel. Sub procedures are capable of doing a week's worth of work in Microsoft Excel in mere seconds.
Macros or procedures as they are known in VBA, are blocks that your type in a module that when run, perform some task in Excel like processing data. When you type a procedure in a module, it is just like typing in Microsoft Word. However, merely typing a procedure in a module does nothing, it is just text, you must run it to carry out its instructions. Procedures can be run from the VBA Editor directly; by clicking buttons or pictures on a worksheet; by event like when a user types in a worksheet cell and so on.
As just stated, you type procedures in a module to perform your automated Excel tasks. The next question is which procedure to use, a Sub procedure or Function procedure? This is actually very simply, Sub procedures are created when you want to automate Excel like process data, build charts, copy and paste, sort...etc. Function procedures are created when you want to perform a calculation like the Sum worksheet function does. Function procedures can be run directly from a worksheet formula allowing you to build your own custom worksheet functions. Be aware that Sub procedures can also perform the same calculations as functions.
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 spaces and non-printable ASCII's from text in column A on a worksheet. Non-printable ASCII's are invisible characters that mess up all sorts of data processing tasks in Excel and are usually seen in download data.
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 (you use the Tab key to get the indents):
Sub clean_text( )
Set wf = WorksheetFunction
For r = 1 To 10
Cells(r,1).Value = wf.Clean( wf.Trim( Cells(r,1).Value ) )
Press Alt+F11 to toggle back to Excel, put some text in column A between rows 1 and 10 with spaces before and after the text. If there is double spacing between words, the Trim worksheet function will eliminate the spacing down to 1 space.
Press Alt+F11 to toggle back to the Editor. To run the procedure, click on the first line of the procedure and press the F5 key. Now toggle back to Excel and see if the spacing was removed.
The procedure uses a loop, For r = 1 to 10...Next r, to scan rows 1-10 on the active worksheet. It is merely a counter but will loop the code inside its boundary 10 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 worksheet functions Clean and Trim are used to remove unwanted spaces and non-printable ASCII's from the text in the code. The Value command means you are going after the value of the cell. If you want to learn how to really use VBA, you must learn the basics of programming like loops, logic. variables, how the code looks (syntax)...etc. Just switching on the Macro recorder does not provide any engine or brains for you procedure to operate with and the code is usually very twitchy and crashes.
Now sit back and think about processing 30,000 or 100,000 rows of data, reconciling on another worksheet then charting automatically in about 1 min. Now think about doing it manually and the days or weeks it would take if the data is complex. This example is why you want to learn Excel VBA and is only a small fraction of its power. What is shown above is what we introduce you to in the first hour of our Beginning VBA workshops.