Excel 2019 Power Programming with VBA - Michael Alexander

Скачать книгу

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

      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.

      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

      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)

       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

Скачать книгу