Michael Alexander - Excel 2019 Power Programming with VBA

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

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.comand search for the words Excel VBA .

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.

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.

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.

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

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 Subprocedure. 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)

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.

VBA procedures need not manipulate any objects. The preceding procedure, for example, doesn't do anything with objects. It simply works with numbers.

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).

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 apostrophe—except when the apostrophe is contained within quotation marks—up 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 Remkeyword to mark a line as a comment. Here's an example: