How to Read VBA Tutorials & Use Visual Basic for Applications to Quickly Create Custom Functions

Read and learn VBA concepts and techniques in VBA tutorials by running and memorizing small VBA programs that accompany the tutorials. Quickly create custom functions in Excel VBA by using the VBA "Function" statement. VBA has many statements and virtual blueprints for use in custom programs that run inside Office applications. To prevent information overload as you learn, work to understand one small sample program at a time. Do this by running such a program one statement at a time, and by making small changes to the program.

Instructions

Read Tutorials

1. Read the tutorial's text to get an overview of the VBA concept or procedure the author is describing, and help you understand any VBA code samples that accompany the article.

2. Copy the code sample from the Web page to the clipboard. Open the Office application the sample was written for, then enter the VBA programming environment by pressing "Alt" and "F11" simultaneously. For example, paste the first program listing in the MSDN article "Editing Text" into the Word VBA programming environment.

3. Click in the environment's central window, then press "Ctrl" and "V" simultaneously to paste the code sample into the VBA programming environment. Press "F5" to run the sample.

4. Press "Ctrl" and "A" simultaneously to select the code sample, then press "Delete" to delete all its statements. Retype the sample by hand and from memory. Refer to the original sample's listing in the article only as needed. Repeat this step several times until you no longer need to read the original program listing. This helps you understand how the sample program works.

Create Custom Excel Functions

1. Open Excel and enter the VBA programming environment by clicking the "Visual Basic" button under the "Developer" tab.

2. Click the "Insert" menu, then click the "Module" item to insert a new programming window for your custom function.

3. Type your function's statements in the new window. A quick way of writing a custom function is to write a few statements that modify the value of a function built into Excel. For example, to trim input text of whitespace and find the location of a special character like "@," use two of Excel's built-in functions to write your function, then paste the listing into the Excel VBA programming window:

   Public Function findAmpersand(arg As String) As Integer
   findAmpersand = WorksheetFunction.Search("@", WorksheetFunction.Trim(arg))
   End Function

4. Press "Alt" and "F11" simultaneously to return to Excel, then click the "Fx" button to the left of the formula bar above the worksheet grid. Click your function's name from the list of functions, then click "OK" to insert it into the worksheet.

References

• Mastering VBA for Office 2010; Richard Mansfield

Resources

• Microsoft MSDN: Editing Text in Word VBA