Excel 2019 Power Programming with VBA - Michael Alexander

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

not the same as an End Sub statement.

      You declare static variables by using the Static keyword.

      Sub MySub() Static Counter as Long '- [Code goes here] - End Sub

      Working with constants

      A variable's value may change while a procedure is executing (that's why it's called a variable). Sometimes, you need to refer to a named value or string that never changes: a constant.

      Using constants throughout your code in place of hard-coded values or strings is an excellent programming practice. For example, if your procedure needs to refer to a specific value (such as an interest rate) several times, it's better to declare the value as a constant and use the constant's name rather than its value in your expressions. Not only does this technique make your code more readable, it also makes it easier to change should the need arise—you have to change only one instruction rather than several.

      Declaring constants

      You declare constants with the Const statement. Here are some examples:

      Const NumQuarters as Integer = 4 Const Rate = .0725, Period = 12 Const ModName as String = "Budget Macros" Public Const AppName as String = "Budget Application"

      The second example doesn't declare a data type. Consequently, VBA determines the data type from the value. The Rate variable is a Double, and the Period variable is an Integer. Because a constant never changes its value, you normally want to declare your constants as a specific data type.

      Like variables, constants have a scope. If you want a constant to be available within a single procedure only, declare it after the Sub or Function statement to make it a local constant. To make a constant available to all procedures in a module, declare it before the first procedure in the module. To make a constant available to all modules in the workbook, use the Public keyword and declare the constant before the first procedure in a module. Here's an example:

      Public Const InterestRate As Double = 0.0725

      NOTE

      If your VBA code attempts to change the value of a constant, you get an error (Assignment to constant not permitted). This message is what you would expect. A constant is a constant, not a variable.

      Using predefined constants

      Excel and VBA make available many predefined constants, which you can use without declaring. In fact, you don't even need to know the value of these constants to use them. The macro recorder generally uses constants rather than actual values. The following procedure uses a built-in constant (xlLandscape) to set the page orientation to landscape for the active sheet:

      Sub SetToLandscape() ActiveSheet.PageSetup.Orientation = xlLandscape End Sub

Screenshot of Visual Basic Editor that displays a list of constants that can be assigned to a property.

      NOTE

      The Object Browser can display a list of all Excel and VBA constants. In the VBE, press F2 to bring up the Object Browser.

      Working with strings

      Like Excel, VBA can manipulate both numbers and text (strings). There are two types of strings in VBA.

       Fixed-length strings are declared with a specified number of characters. The maximum length is 65,535 characters.

       Variable-length strings theoretically can hold up to 2 billion characters.

      Each character in a string requires 1 byte of storage, plus a small amount of storage for the header of each string. When you declare a variable with a Dim statement as data type String, you can specify the length if you know it (that is, a fixed-length string), or you can let VBA handle it dynamically (a variable-length string).

      In the following example, the MyString variable is declared to be a string with a maximum length of 50 characters. YourString is also declared as a string; but it's a variable-length string, so its length is not fixed.

      Dim MyString As String * 50 Dim YourString As String

      Working with dates

      You can use a string variable to store a date, but if you do, it's not a real date (meaning that you can't perform date calculations with it). Using the Date data type is a better way to work with dates.

      NOTE

      The range of dates that VBA can handle is much larger than Excel's own date range, which begins with January 1, 1900, and extends through December 31, 9999. Therefore, be careful that you don't attempt to use a date in a worksheet that is outside Excel's acceptable date range.

      

In Chapter 5, “Creating Function Procedures,” we describe some relatively simple VBA functions that enable you to create formulas that work with pre-1900 dates in a worksheet.

      About Excel's date bug

      It is commonly known that Excel has a date bug: it incorrectly assumes that the year 1900 is a leap year. Even though there was no February 29, 1900, Excel accepts the following formula and displays the result as the 29th day of February 1900:

       =Date(1900,2,29)

      VBA doesn't have this date bug. The VBA equivalent of Excel's DATE function is DateSerial. The following expression (correctly) returns March 1, 1900:

       DateSerial(1900,2,29)

      Therefore, Excel's date serial number system doesn't correspond exactly to the VBA date serial number system. These two systems return different values for dates between January 1, 1900, and February 28, 1900.

      Here are some examples of declaring variables and constants as Date data types:

      Dim Today As Date Dim StartTime As Date Const FirstDay As Date = #1/1/2019# Const Noon = #12:00:00#

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