Pilfer code from the Internet
All the macro syntax you will ever need has likely been documented somewhere on the Internet. In many ways, programming has become less about the code one creates from scratch and more about how to take existing code and apply it creatively to a particular scenario.
If you are stuck trying to create a macro for a particular task, fire up your favorite online search engine and simply describe the task you are trying to accomplish. For the best results, enter Excel VBA before your description.
For example, if you are trying to write a macro that deletes all the blank rows in a worksheet, search for Excel VBA delete blank rows in a worksheet. You can bet two months' salary that someone out there on the Internet has tackled the same problem. Nine times out of 10, you will find some example code that will give you the nugget of information you need to jump-start some ideas for building your own macro.
Leverage user forums
If you find yourself in a bind, you can post your question in a forum to get customized guidance based on your scenario.
User forums are online communities that revolve around a particular topic. In these forums, you can post questions and have experts offer advice on how to solve particular problems. The folks answering the questions are typically volunteers who have a passion for helping the community solve real-world challenges.
There are many forums dedicated to all things Excel. To find an Excel Forum, enter the words Excel Forum in your favorite online search engine.
Here are a few tips for getting the most out of user forums:
Always read and follow the forum rules before you get started. These rules often include advice on posting questions and community etiquette guidelines.
Use concise and accurate subject titles for your questions. Don't create forum questions with abstract titles like Need Advice or Please Help.
Keep the scope of your questions as narrow as possible. Don't ask questions like How do I build an invoicing macro in Excel?
Be patient. Remember that the folks answering your questions are volunteers who typically have day jobs. Give the community some time to answer your question.
Check back often. After posting your question, you may receive requests for more details about your scenario. Do everyone a favor and return to your posting either to review the answers or respond to follow-up questions.
Thank the expert who answered your question. If you receive an answer that helps you, take a moment to post a thank you to the expert who helped you out.
Visit expert blogs
There are a few dedicated Excel gurus who share their knowledge through blogs. These blogs are often treasure troves of tips and tricks, offering nuggets that can help build up your skills. Best of all, they are free!
Although these blogs will not necessarily speak to your particular needs, they offer articles that advance your knowledge of Excel and can even provide general guidance on how to apply Excel in practical business situations.
Here is a starter list of a few of the best Excel blogs on the Internet today:
http://chandoo.org http://www.contextures.com http://www.datapigtechnologies.com/blog http://www.dailydoseofexcel.com http://www.excelguru.ca/blog http://www.mrexcel.com
Mine YouTube for video training
Some of us learn better if we watch a task being done. If you find that you absorb video training better than online articles, consider mining YouTube. There are dozens of channels run by amazing folks who have a passion for sharing knowledge. You'll be surprised at how many free high-quality video tutorials you'll find.
Go to www.youtube.com and search for the words Excel VBA.
Learn from the Microsoft Office Dev Center
The Microsoft Office Dev Center is a site dedicated to helping new developers get a quick start in programming Office products. You can get to the Excel portion of this site by going to
https://msdn.microsoft.com/en-us/library/office/fp179694.aspx
Although the site can be a bit difficult to navigate, it's worth a visit to see all of the free resources, including sample code, tools, step-by-step instructions, and much more.
Dissect the other Excel files in your organization
Like finding gold in your backyard, the existing files in your organization are often a treasure trove for learning. Consider cracking open those Excel files that contain macros and take a look under the covers. See how others in your organization use macros. Try to go through the macros line by line and see if you can spot new techniques. You may even stumble upon entire chunks of useful code that you can copy and implement in your own workbooks.
Ask your local Excel genius
Do you have an Excel genius in your company, department, organization, or community? Make friends with that person today. Most Excel experts love sharing their knowledge. Don't be afraid to approach your local Excel guru to ask questions or seek out advice on how to tackle macro problems.
CHAPTER 3 VBA Programming Fundamentals
IN THIS CHAPTER
Understanding VBA language elements, including variables, data types, constants, and arrays
Using VBA built-in functions
Manipulating objects and collections
Controlling the execution of your procedures
VBA Language Elements: An Overview
If you've used other programming languages, much of the information in this chapter may sound familiar. However, VBA has a few unique wrinkles, so even experienced programmers may find some new information.
This chapter explores the VBA language elements, which are the keywords and control structures that you use to write VBA routines.
To get the ball rolling, take a look at the following VBA Sub procedure. This simple procedure, which is stored in a VBA module, calculates the sum of the first 100 positive integers. When the code finishes executing, the procedure displays a message with the result.
Sub VBA_Demo() ' This is a simple VBA Example Dim Total As Long, i As Long Total = 0 For i = 1 To 100 Total = Total + i Next i MsgBox Total End Sub
This procedure uses some common VBA language elements, including the following:
A comment (the line that begins with an apostrophe)
A variable declaration statement (the line that begins with Dim)
A variable declaration statement (the line that begins with Dim)
Two variables (Total and i)
Two assignment statements (Total = 0 and Total = Total + i)
A looping structure (For - Next)
A VBA function (MsgBox)
You will explore all of these language elements in subsequent sections of this chapter.
NOTE
VBA procedures need not manipulate any objects. The preceding procedure, for example, doesn't do anything with objects. It simply works with numbers.
Entering VBA code
VBA code, which resides in a VBA module, consists of instructions. The accepted practice is to use one instruction per line. This standard isn't a requirement, however; you can use a colon to separate multiple instructions on a single line. The following example combines four instructions on one line:
Sub OneLine() x= 1: y= 2: z= 3: MsgBox x + y + z End Sub
Most programmers agree that code is easier to read if you use one instruction per line.
Sub MultipleLines() x = 1 y = 2 z = 3 MsgBox x + y + z End Sub
Each line can be as long as you like; the VBA module window scrolls to the left when you reach the right side. However, reading very long lines of code while having to scroll is not a particularly pleasant. For lengthy lines, it's considered a best practice to use VBA's line continuation sequence: a space followed by an underscore (_). Here's an example:
Sub LongLine() SummedValue = _ Worksheets("Sheet1").Range("A1").Value + _ Worksheets("Sheet2").Range("A1").Value End Sub
When you record macros, Excel often uses the line continuation sequence to break a long statement into multiple lines.
After you enter an instruction, VBA performs the following actions to improve readability:
It inserts spaces between operators. If you enter Ans=1+2 (without spaces), for example, VBA converts it to the following:
Ans = 1 + 2
It adjusts the case of the letters for keywords, properties, and methods. If you enter the following text:
Result=activesheet.range("a1").value=12
VBA converts it to the following:
Result = ActiveSheet.Range("a1").Value = 12
Notice that text within quotation marks (in this case, "a1") isn't changed.
Because VBA variable names aren't case-sensitive, the VBE, by default, adjusts the names of all variables with the same letters so that their case matches the case of letters that you most recently typed. For example, if you first specify a variable as myvalue (all lowercase) and then enter the variable as MyValue (mixed case), the VBA changes all other occurrences of the variable to MyValue. An exception occurs if you declare the variable with Dim or a similar statement; in this case, the variable name always appears as it was declared.
VBA scans the instruction for syntax errors. If VBA finds an error, it changes the color of the line and might display a message describing the problem. In the Visual Basic Editor, choose the Tools Options command to display the Options dialog box, where you control the error color (use the Editor Format tab) and whether the error message is displayed (use the Auto Syntax Check option in the Editor tab).
Comments
A comment is descriptive text embedded in your code and ignored by VBA. It's a good idea to use comments liberally to describe what you're doing because an instruction's purpose isn't always obvious.
You can use a complete line for your comment, or you can insert a comment after an instruction on the same line. A comment is indicated by an apostrophe. VBA ignores any text that follows an apostropheexcept when the apostrophe is contained within quotation marksup until the end of the line. For example, the following statement doesn't contain a comment, even though it has an apostrophe:
Msg = "Can't continue"
The following example shows a VBA procedure with three comments:
Sub CommentDemo() ' This procedure does nothing of value x = 0 'x represents nothingness ' Display the result MsgBox x End Sub
Although the apostrophe is the preferred comment indicator, you can also use the Rem keyword to mark a line as a comment. Here's an example:
Rem -- The next statement prompts the user for a filename
The Rem keyword (short for Remark) is essentially a holdover from older versions of BASIC, and it is included in VBA for the sake of compatibility. Unlike the apostrophe, Rem can be written only at the beginning of a line, not on the same line as another instruction.
The following are a few general tips on making the best use of comments:
Use comments to describe briefly the purpose of each procedure that you write.
Use comments to describe changes you make to a procedure.
Use comments to indicate you're using functions or constructs in an unusual or a nonstandard manner.
Use comments to describe the purpose of variables so that you and other people can decipher otherwise cryptic names.
Use comments to describe workarounds that you develop to overcome Excel bugs or limitations.
Write comments while you code rather than afterward.
When you've completed all coding, take some time to go back and tidy up your comments, removing any comments that are no longer needed and expanding on comments that may be incomplete or a bit too cryptic.
TIP
In some cases, you may want to test a procedure without including a particular instruction or group of instructions. Instead of deleting the instruction, convert it to a comment by inserting an apostrophe at the beginning. VBA then ignores the instruction when the routine is executed. To convert the comment back to an instruction, just delete the apostrophe.
The Visual Basic Editor offers an Edit toolbar containing tools to assist you in editing your code. In particular, there are two handy buttons that enable you to comment and uncomment entire blocks of code at once.
Note that the Edit toolbar isn't displayed by default. To display this toolbar, choose View Toolbars Edit.
You can select several lines of code at once and then click the Comment Block button on the Edit toolbar to convert the selected lines to comments. The Uncomment Block button converts a group of comments back into uncommented code.
Variables, Data Types, and Constants
VBA's main purpose is to manipulate data. Some data resides in objects, such as worksheet ranges. Other data is stored in variables that you create.
You can think of a variable as a named storage location in your computer's memory. Variables can accommodate a wide variety of data typesfrom simple Boolean values (True or False) to large, double-precision values (see the following section). You assign a value to a variable by using the equal sign operator (more about this process in the upcoming section Assignment Statements).