Excel 2019 Power Programming with VBA. Michael Alexander. Читать онлайн. Newlib. NEWLIB.NET

Автор: Michael Alexander
Издательство: John Wiley & Sons Limited
Серия:
Жанр произведения: Программы
Год издания: 0
isbn: 9781119514916
Скачать книгу

      Dates are always defined using month/day/year format, even if your system is set to display dates in a different format (for example, day/month/year).

      If you use a message box to display a date, it's displayed according to your system's short date format. Similarly, a time is displayed according to your system's time format (either 12- or 24-hour). You can modify these system settings by using the Regional Settings option in the Windows Control Panel.

      An assignment statement is a VBA instruction that evaluates an expression and assigns the result to a variable or an object. Excel's Help system defines expression as “a combination of keywords, operators, variables, and constants that yields a string, number, or object. An expression can perform a calculation, manipulate characters, or test data.”

      Much of the work done in VBA involves developing (and debugging) expressions. If you know how to create formulas in Excel, you'll have no trouble creating expressions in VBA. With a worksheet formula, Excel displays the result in a cell. The result of a VBA expression, on the other hand, can be assigned to a variable or used as a property value.

      VBA uses the equal sign (=) as its assignment operator. The following are examples of assignment statements (the expressions are to the right of the equal sign):

      x = 1 x = x + 1 x = (y * 2) / (z * 2) FileOpen = True FileOpen = Not FileOpen Range("TheYear").Value = 2010

      TIP

      Expressions can be complex. You may want to use the line continuation sequence (space followed by an underscore) to make lengthy expressions easier to read.

      Often, expressions use functions. These functions can be built-in VBA functions, Excel's worksheet functions, or custom functions that you develop in VBA. We discuss built-in VBA functions later in this chapter (see the upcoming section “Built-in Functions”).

      Operators play a major role in VBA. Familiar operators describe mathematical operations, including addition (+), multiplication (*), division (/), subtraction (), exponentiation (^), and string concatenation (&). Less familiar operators are the backslash (\) operator (used in integer division) and the Mod operator (used in modulo arithmetic). The Mod operator returns the remainder of one number divided by another. For example, the following expression returns 2:

      17 Mod 3

      VBA also supports the same comparison operators used in Excel formulas: equal to (=), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>).

Operator Operation Order of Precedence
^ Exponentiation 1
* and / Multiplication and division 2
+ and - Addition and subtraction 3
& Concatenation 4
=, <, >, <=, >=, <> Comparison 5

      CAUTION

      The negation operator (a minus sign) is handled differently in VBA. In Excel, the following formula returns 25:

      =-5^2

      In VBA, x equals –25 after this statement is executed.

      x = -5 ^ 2

      VBA performs the exponentiation operation first and then applies the negation operator. The following statement returns 25:

      x = (-5) ^ 2

      x = 4 + 3 * 2

      To avoid ambiguity, you may prefer to write the statement as follows:

      x = 4 + (3 * 2)

Operator What It Does
Not Performs a logical negation on an expression
And Performs a logical conjunction on two expressions
Or Performs a logical disjunction on two expressions
Xor Performs a logical exclusion on two expressions
Eqv Performs a logical equivalence on two expressions
Imp Performs a logical implication on two expressions

      ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines

      The following expression performs a logical And operation. The MsgBox statement displays True only when Sheet1 is the active sheet and the active cell is in Row 1. If either or both of these conditions aren't true, the MsgBox statement displays False.

      MsgBox ActiveSheet.Name = "Sheet1" And ActiveCell.Row = 1

      The following expression performs a logical Or operation. The MsgBox statement displays True when either Sheet1 or Sheet2 is the active sheet.

      MsgBox ActiveSheet.Name = "Sheet1" Or ActiveSheet.Name