After creating the constant, you can use its name wherever
its value would have been used. Some of the constants you will use in your expressions have already been created.
We will mention them when necessary.
An expression is one or more symbols combined with one or
more values to create another value. For example, +16 is an expression that
creates the positive value 16. Most expressions that we know are made of
arithmetic calculations. An example is 422.82 * 15.55.
To add an expression to a selected cell, assign it to the
ActiveCell object. Here is an example:
Sub Exercise()
ActiveCell = 422.82 * 15.5
End Sub
A formula is another name for an expression. It combines one
or more values, one or more variables, to an operator, to produce a new value.
This also means that you use the same approach or building an expression when
creating a formula.
To assist you with assigning the result of a formula to a
cell or a group of cells, the Range class is equipped with a property named Formula.
This property is of type Variant, which means its value can be anything, not
necessarily a number. After accessing the Formula property, you can assign
whatever value, expression, or formula you want to it. Here are examples:
Sub Exercise()
Rem Using the Formula property to assign a string to the active cell
ActiveCell.Formula = "Weekly Salary:"
Rem Using the Formula property to assign an expression to cell B2
Range("B2").Formula = 24.5 * 42.5
Rem Using the Formula property to assign
Rem the same string to a group of cells
Range("C2:F5, B8:D12").Formula = "Antoinette"
End Sub
If you are creating a worksheet that would be used on
computers of different languages, use FormulaLocal instead. The FormulaLocal
property is equipped to adapt to a different language-based version of Microsoft
Excel when necessary.
Besides Formula, the Range class is also
equipped with a property named FormulaR1C1. Its functionality is
primarily the same as Formula. Here are examples:
Sub Exercise()
Rem Using the Formula property to assign a string to the active cell
ActiveCell.FormulaR1C1 = "Weekly Salary:"
Rem Using the Formula property to assign an expression to cell B2
Range("B2").FormulaR1C1 = 24.5 * 42.5
Rem Using the Formula property to assign
Rem the same string to a group of cells
Range("C2:F5, B8:D12").FormulaR1C1 = "Antoinette"
End Sub
If you are creating the worksheet for various languages, use
FormulaR1C1Local instead.
Instead
of creating your own function, you can use one of those that ship with the VBA
language. This language provides a very extensive library of functions so that,
before creating your own, check whether the function exists already. If so, use
it instead.
To use a VBA built-in function, simply use as you would an
expression. That is, assign its returned value to a cell. Here is an example:
Sub Exercise()
Range("B2:B2") = Len("Paul Bertrand Yamaguchi")
End Sub
To assist you with developing smart worksheets, Microsoft
Excel provides a very large library of functions.
To use a Microsoft Excel built-in function in your code, you have many
functions.
In double-quotes, you can include the assignment operator followed by the function's whole expression. Here is an example:
Sub Exercise()
Range("B5:B5") = "=SomeFunction(B2, B3, B4)"
End Sub
As an alternative, the Application class is equipped with a
property named WorksheetFunction. This property represents all functions of the
Microsoft Excel library. Therefore, to access a function, type Application,
followed by a period, followed by a period. Then type (or select the name of the
function you want to use:
After specifying the function you want to use, because it is
a function, you must include the parentheses. In the parentheses, type the
necessary argument(s). Here is an example:
Private Sub cmdCreate_Click()
txtValue.Text = Application.WorksheetFunction.Sum(Range("D4:D8"))
End Sub
You may recall that when studying data types, we saw
that each had a corresponding function used to convert a string value or
an expression to that type. As a reminder, the general syntax of the conversion functions is:
ReturnType = FunctionName(Expression)
The Expression could be of any kind.
For example, it
could be a string or expression that would produce a value such
as the result of a calculation. The conversion function would take such a
value, string, or
expression and attempt to convert it. If the conversion is
successful,
the function would return a new value that is of the type
specified by
the ReturnType in our syntax.
The conversion functions are as follows:
These functions allow you to convert a known value to a
another type.
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Practical
Learning: Introducing Expressions
Both Microsoft Excel and the Visual Basic language provide each an
extensive library of functions. We refer to some functions as accessories
because you almost cannot anything about them or at least they are very
useful.
To assist you with specifying the color of anything, the VBA
is equipped with a function named RGB. Its syntax is:
Function RGB(RedValue As Byte, GreenValue As Byte, BlueValue As Byte) As long
This function takes three arguments and each must hold a
value between 0 and 255. The first argument represents the ratio of red of the
color. The second argument represents the green ratio of the color. The last
argument represents the blue of the color. After the function has been called,
it produces a number whose maximum value can be 255 * 255 * 255 = 16,581,375,
which represents a color.
The Microsoft Excel's SUM function is used to add the
numeric values of various cells. The result can be displayed in another cell or used in an expression.
Like all functions of the Microsoft Excel library, you can use SUM visually or
programmatically.
To use the SUM() function visually, on the Ribbon, in the Home tab, the Editing section is equipped with a button called the AutoSum
The absolute value of a number x is x if the
number is (already) positive. If the number is negative,
then its absolute value is its positive equivalent. For example, the
absolute value of 12 is 12, while the absolute value of –12 is 12.
To get the absolute value of a number, you can use either
the Microsoft Excel's ABS() or the VBA's Abs() function. Their syntaxes
are:
Function ABS(number) As Number Function Abs(number) As Number
This function takes one argument. The argument must be a
number or an expression convertible to a number:
If you have a decimal number but are interested only in the
integral part, to assist you with retrieving that part, the Visual Basic
language provides the Int() and the Fix() functions. In the same
way, the Microsoft Excel library provides the INT() function to perform a
similar operation. Their
syntaxes are:
Function Int(ByVal Number As { Number | Expression } ) As Integer
Function Fix(ByVal Number As { Number | Expression } ) As Integer
Function ABS(ByVal Number As { Number | Expression } ) As Integer
Each function must take one argument. The value of the
argument must be number-based. This means it can be an integer or a
floating-point number. If the value of the argument is integer-based, the
function returns the (whole) number. Here is an example
Sub Exercise()
Dim Number As Integer
Number = 28635
ActiveCell = MsgBox(Int(Number), vbOKOnly, "Exercise")
End Sub
This would produce:
If the value of the argument is a decimal number, the
function returns only the integral part. Here is an example
Sub Exercise()
Dim Number As Double
Number = 7942.225 * 202.46
ActiveCell = MsgBox(Int(Number), vbOKOnly, "Exercise")
End Sub
This would produce:
This function always returns the integral part only, even if
you ask it to return a floating-point-based value. Here is an example:
Sub Exercise()
Dim Number As Single
Number = 286345.9924
ActiveCell = MsgBox(Int(Number), vbOKOnly, "Exercise")
End Sub
This would produce:
When it receive values for its cells, by default, Microsoft Excel displays text
left aligned and numbers right aligned.
In some situations, you will want to treat numbers as text.
Although Microsoft Excel displays all numbers right
aligned, as a smart financial and business application,
it can distinguish between different types of numbers. It can
recognize a date, a currency, or a percentage
values, but the computer wants you to specify the way numbers
should be displayed, giving you the ability to decide what a particular
number represents, not because the software cannot recognize a number, but
because a value can represent different things to different people in
different scenarios. For example 1.5 might represent a half teaspoon in
one spreadsheet while the same 1.5 would represent somebody's age,
another spreadsheet's percentage, or etc.
When it comes to displaying items, Microsoft Excel
uses various default configurations. The computer's Regional
Options or Regional Settings govern how dates, numbers, and time, etc get displayed on your
computer.
Microsoft Excel recognizes numbers in various formats: accounting,
scientific, fractions, and currency. As the software
product can recognize a number, you still have the ability to
display the number with a format that suits a particular scenario.
To visually control how a cell should display its number, on the Ribbon, click
Home and use the Number section.
To assist you with programmatically specifying how a cell should display its
number, the Range class is equipped with a property named Style.
To further assist with number formatting, the Visual Basic language provides a function named Format. This function can be used for
different types of values The most basic technique consists of passing it an
expression that holds the value to display. The syntax of this function is:
Function Format(ByVal Expression As Variant, _ Optional ByVal Style As String = "" _ ) As String
The first argument is the value that must be formatted. Here
is an example:
Sub Exercise()
Dim Number As Double
Number = 20502.48
ActiveCell = Format(Number)
End Sub
The second argument is optionally. It specifies the type of
format you want to apply. We will see various examples.
To visually specify that you want a cell to display its
numeric value with the comma delimiter, click the cell to give it focus. Then,
in the Home tab of the Ribbon, in the Number section, click the Comma Style button
 .
The thousand numbers would display with a comma sign which makes it easier to read.
To visually control the number of decimal values on the
right side of the comma, in the Number section of the Ribbon:
To programmatically specify that you want a cell to display
the comma style of number, assign the "Comma" string to the Style
property of the Range class. Here is an example:
Sub SpecifyComma()
ActiveCell.Style = "Comma"
End Sub
Alternatively, to
programmatically control how the number should display, you
can pass the second argument to the Format() function. To produce the number in a general format, you can pass the
second argument as "g", "G", "f",
or "F" .
To display the number with a decimal separator, pass the
second argument as "n", "N", or "Standard".
Here is an example:
Sub Exercise()
Dim Number As Double
Number = 20502.48
ActiveCell = Format(Number, "STANDARD")
End Sub
An alternative to get this format is to call a function
named FormatNumber. Its syntax is:
Function FormatNumber( ByVal Expression As Variant, Optional ByVal NumDigitsAfterDecimal As Integer = -1, Optional ByVal IncludeLeadingDigit As Integer, Optional ByVal UseParensForNegativeNumbers As Integer, Optional ByVal GroupDigits As Integer ) As String
Only the first argument is required and it represents the
value to display. If you pass only this argument, you get the same format as the
Format() function called with the Standard option. Here is an
example:
Sub Exercise()
Dim Number As Double
Number = 20502.48
ActiveCell = FormatNumber(Number)
End Sub
This would produce the same result as above.
If you call the Format() function with the Standard
option, it would consider only the number of digits on the right side of the
decimal separator. If you want to display more digits than the number actually
has, call the FormatNumber() function and pass a second argument with the
desired number. Here is an example:
Sub Exercise()
Dim Number As Double
Number = 20502.48
ActiveCell = FormatNumber(Number, 4)
End Sub
In the same way, if you want the number to display with less
numbers on the right side of the decimal separator, specify that number.
We saw that you could click the Decrease Decimal
button
on the Ribbon to visually control the number of decimal values on the right side
of the comma and you could continuously click that button to decrease the number
of digits. Of course, you can also exercise this control programmatically.
You can call the Format()
function to format the number with many more options. To represent the integral part of a number, you use the # sign. To
specify the number of digits to display on the right side of the decimal
separator, type a period on the right side of # followed by the number of 0s
representing each decimal place. Here is an example:
Sub Exercise()
Dim Number As Double
Number = 20502.48
ActiveCell = Format(Number, "#.00000")
End Sub
The five 0s on the right side of the period indicate that
you want to display 5 digits on the right side of the period. You can enter as many # signs as you want; it would not
change anything. Here is an example:
Sub Exercise()
Dim Number As Double
Number = 20502.48
ActiveCell = Format(Number, "##########.00000")
End Sub
This would produce the same result as above. To specify that
you want to display the decimal separator, include its character between the #
signs. Here is an example:
Sub Exercise()
Dim Number As Double
Number = 20502.48
ActiveCell = Format(Number, "###,#######.00000")
End Sub
You can include any other character or symbol you want in
the string to be part of the result, but you should include such a character
only at the beginning or the end of the string, otherwise the interpreter might
give you an unexpected result.
Another regular type of number used in applications and
finances is the currency. A currency value uses a special character specified in
the Control Panel. In US English, this character would be the $ sign:
To visually that a cell should
display its number as currency, in the Number section of the Ribbon, click the Currency Style button
 .
To programmatically specify that you want a cell to display
its value with the currency style, assign the "Currency" string to the
Style property of the Range class. Here is an example:
Sub SpecifyComma()
ActiveCell.Style = "Currency"
End Sub
Alternatively, to programmatically display the currency symbol in the
result of a cell or a text box of a form, you can simply
add it as part of the second argument to the Format() function. Here is
an example:
Sub Exercise()
Dim Number As Double
Number = 205.5
ActiveCell = Format(Number, "$###,#######.00")
End Sub
Fortunately, there are more professional options. Besides
the Format() function, to support currency formatting of a number, the Visual
Basic language provides the FormatCurrency() function. Its syntax is:
Function FormatCurrency( ByVal Expression As Variant, Optional ByVal NumDigitsAfterDecimal As Integer = -1, Optional ByVal IncludeLeadingDigit As Integer = -2, Optional ByVal UseParensForNegativeNumbers As Integer = -2, Optional ByVal GroupDigits As Integer = -2 ) As String
Only the first argument is required. It is the value that
needs to be formatted. Here is an example:
Sub Exercise()
Dim UnitPrice As Double
UnitPrice = 1450.5
ActiveCell = FormatCurrency(UnitPrice)
End Sub
Notice that, by default, the FormatCurrency()
function is equipped to display the currency symbol (which, in US English is, the
$ sign), the decimal separator (which in US English is the comma), and two
decimal digits. If you want to control how many decimal digits are given to the
result, pass a second argument as an integer. Here is an example:
Sub Exercise()
Dim UnitPrice As Double
UnitPrice = 1450.5
ActiveCell = FormatCurrency(UnitPrice, 4)
End Sub
Instead of calling the FormatCurrency() function to
format a number to currency, you can use the Format() function. If you do, pass
it a second argument as "Currency", "c", or "C".
Here is an example:
Sub Exercise()
Dim CarPrice As Double
CarPrice = 42790
ActiveCell = Format(CarPrice, "Currency")
End Sub
A percentage of a number represents its rate on a scale,
usually of 100 (or more). The number is expressed using digits accompanied by
the % sign. To visually specify that a number in a cell should be treated a
percentage value, in the Number section of the Ribbon, click the Percent Style
button
 .
To programmatically use a percentage number in a cell or the
control of a form, you can use the Format() function. Besides the Format() function, to support percent values, the Visual Basic language provides a function named FormatPercent.
Its syntax is:
Function FormatPercent( ByVal Expression As Variant, Optional ByVal NumDigitsAfterDecimal As Integer = -1, Optional ByVal IncludeLeadingDigit As Integer = -2, Optional ByVal UseParensForNegativeNumbers As Integer = -2, Optional ByVal GroupDigits As Integer = -2 ) As String
Only the first argument is required and it is the number
that needs to be formatted. When calling this function, pay attention to the
number you provide as argument. If the number represents a percentage value as a
fraction of 0 to 1, make sure you provide it as such. An example would be 0.25.
In this case, the Visual Basic interpreter would multiply the value by 100 to
give the result. Here is an example:
Sub Exercise()
Dim DiscountRate As Double
DiscountRate = 0.25
ActiveCell = FormatPercent(DiscountRate)
End Sub
If you pass the value in the hundreds,
the interpreter would still multiply it by 100. Although it is not impossible to
get a percentage value in the hundreds or thousands, you should make sure that's
the type of value you mean to get.
Besides the FormatPercent() function, to format a
number to its percentage equivalent, you can call the Format() function
and pass the second argument as "Percent", "p", or
"P". Here is an example:
Sub Exercise()
Dim DiscountRate As Double
DiscountRate = 0.25
ActiveCell = MsgBox("Discount Rate: " & _
Format(DiscountRate, "Percent"), _
vbOKOnly, "Exercise")
End Sub
Although you can do most of cells configurations using
the Ribbon, Microsoft Excel provides the Format Cells dialog
box. This dialog box presents more options and more precision.
To display the Format Cells dialog box:
|
to increase the number of digits
No comments:
Post a Comment