Étiquette : formulas_functions

Generating a Random Integer from a Range

If you need random numbers not from the interval [0, 1], but from an integer interval (e.g., [1, 6]), you can use the function RndInt(), defined as follows.

Generating a Random Integer

Sub DemoIntegerRnd()
    Dim i As Integer
    For i = 0 To 10
        Debug.Print RndInt(1, 6)
    Next
End Sub

Function RndInt(ByVal lowerbound As Integer, _
                ByVal upperbound As Integer) As Integer
    Randomize
    RndInt = (upperbound - lowerbound) * Rnd() + lowerbound
End Function

Outputting a String Character by Character to the Immediate Window

The Len() function returns the length of a string. For example, the following code outputs a string character by character to the Immediate Window.

Printing a String Character by Character

Sub Lengs()
    Dim i As Integer
    Dim s As String
    s = "Hello, World"
    For i = 1 To Len(s)
        Debug.Print Mid(s, i, 1)
    Next
End Sub

A String Consisting of a Given Number of Spaces

The Space() function returns a string consisting of the specified number of spaces. For example, in the following code, by sequentially adding another string in front of the given string, each time with fewer spaces, a “running line” effect is created.

To test the “running line,” run the procedure LetsGo().

Running Line

Private n As Integer

Sub LetsGo()
    n = 10
    RunString
End Sub

Sub RunString()
    If n >= 0 Then
        Range("Sheet2!A1").Value = Space(n) & "Hello, World!"
        n = n - 1
        Application.OnTime Now + TimeValue("00:00:01"), "RunString"
    End If
End Sub

Determining the Number of Seconds Since Midnight

The Timer() function returns a Single value representing the number of seconds since midnight.

• In Windows, unlike Mac OS X, Timer() returns not just seconds but fractional seconds since midnight.

For example, the following code creates a blinking cell where the background color alternates between red and green for 20 seconds.

To prevent the system from freezing during the loop and allow the application to respond to system events, DoEvents statements are added inside the loops.

Blinking Cell

Sub LetsGo2()
    Dim fl As Boolean
    Dim old As Long
    old = Timer
    Do
        fl = Not fl
        If fl Then
            Range("Sheet3!A1").Interior.Color = RGB(255, 0, 0)
        Else
            Range("Sheet3!A1").Interior.Color = RGB(0, 255, 0)
        End If
        Delay
        DoEvents
    Loop While Timer - old <= 20
    Range("Sheet3!A1").Interior.ColorIndex = xlNone
End Sub

Sub Delay()
    Dim old As Long
    old = Timer
    Do
        DoEvents
    Loop While Timer - old <= 0.5
End Sub

As already mentioned, the capabilities of MS Excel are convenient for solving a wide range of mathematical, physical, economic, and other problems. It is enough to properly arrange the information on the worksheet, i.e., prepare the initial data, determine where the results will be placed, and enter the required formulas.

Excel’s built-in functions can be found on the Formulas tab in the Function Library group. Access to all available functions can also be organized via the Insert Function dialog box.

Excel provides a wide variety of built-in functions for processing both numerical values and other types of data stored in cells. To view available categories and descriptions of functions, use the Function Wizard: click Insert Function in the Function Library group on the Formulas tab.

Let us consider several examples of using functions and formulas to solve specific problems.

Membership of Points in a Plane Region

On a plane, coordinates of points are given. Determine how many of these points belong to the region defined by the system of inequalities:

Steps:

1. Enter the necessary labels into cells A1, A3, A4, B4, D4, A5:B19 (depending on the number of points), A21.
   • Tip: Use + to move to the next line in a cell. Then merge cells A3 and B3 using Merge & Center on the Home tab.

1. According to the inequalities, create a formula that checks whether a point satisfies at least one of the groups of conditions. If so, assign a value of 1.

In C5, enter:

=IF(OR(AND(A5^2+B5^2<=25,A5^2+B5^2>=9),AND(B5<=(-A5)-10,A5>=-10,B5>=-10)),1,0)

Copy this formula down to the range C6:C19.

• For text output, add in D5 (and copy to D5:D19):

=IF(C5=1,"this point belongs to the region","")

Apply the custom number format for cells D5:D19:

;;[White]

• In C21, enter the formula:

=SUM(C5:C19)

• Format the cells using the Styles group on the Home tab as needed.

Example: Solving a System of Linear Equations

In general, the solution of the system AX=BAX = B, where A is the coefficient matrix, B is the column vector of constants, and X is the vector of unknowns, has the form:

X=A⁻¹B.

This follows from the fact that in solving matrix equations, the identity matrix I must remain with X. Multiplying both sides of AX=Bon the left by A⁻¹, we obtain the solution.

Consider solving the system AX=B, where:

The result is shown in Figure.

Steps:

• Enter the values of matrix A into cells A4:B5.
• Enter the values of vector B into cells D4:D5.
• In cell A8, enter the formula:
• =MMULT(MINVERSE(MMULT(A4:B5,A4:B5)),D4:D5)

(It is recommended to use the Function Wizard when entering this formula.)

• To obtain the numerical results, select the range A8:A9, place the cursor in the formula bar, and press Ctrl+Shift+Enter (since this is an array formula).
• Format the task results as required.

Example: Creating a Final Construction Based on a Given Pattern

Suppose, from the available information:

• Full Name
• Phone
• Street, house

you need to create a construction of the form:

Full Name, Phone {format: #00-00-00}, St. Street, House

The result for this example is shown in Figure.

Steps:

• Enter the data in cells A3:C13 according to the specified pattern (see Figure 3.34).
• In cell E3, enter the heading: Full name + phone + address.
• In cell E4, enter the formula:
• =$A4 & « ,  » & TEXT($B4, »\ ???-00-00, ») &  » St.  » & $C4
• Copy the formula to the range E5:E13 (you can use the AutoFill handle).
• Format the data.

Example: Splitting Information Contained in One Cell

Split the following information contained in one cell:

City!Institution!Director!Number of employees

Result should be:

• City
• Institution
• Director
• Number of employees

The result is shown.

Steps:

• In cells B2 and D2, enter the labels: Initial information and Result.
• In cells B3:B6, enter the heading and the necessary data of the initial structure.
• Use the formulas in Table 3.18 for calculations.
• Format the data and results.

Table . Formulas for Parsing Institution Information

Cell	Formula (English)	Description	Copy to
D3	=MID(B3,1,FIND(« ! »,B3)-1)	Extract city	D4:D6
E3	=MID($B3,LEN($D3)+2,FIND(« ! »,$B3,LEN($D3)+2)-LEN($D3)-2)	Extract institution	E4:E6
F3	=MID($B3,LEN($D3)+LEN($E3)+3,FIND(« ! »,$B3,LEN($D3)+LEN($E3)+3)-(LEN($D3)+LEN($E3)+3))	Extract director	F4:F6
G3	=MID($B3,LEN($D3)+LEN($E3)+LEN($F3)+4,LEN($B3)-(LEN($D3)+LEN($E3)+LEN($F3)+3))	Extract number of employees	G4:G6

Example: Creating a Statement for Movie Rental Accounting

Form a statement for movie rental accounting with the following columns:

• №,
• Movie title,
• Last name,
• Date issued,
• Date returned,
• Duration (in hours),
• Duration (in days),
• Payment.

Payment rules:

• If duration ≤ 24 hours → Payment = base rate (chosen arbitrarily).
• If duration > 24 and ≤ 48 hours → Payment = base rate + 0.8 × base rate.
• If duration > 48 hours → for each overdue day, Payment = 3 × base rate.
• If the cassette or CD is lost → Fine = 30 × base rate.

 

Steps:

• Create the header row of the statement and enter the necessary data in the columns: №, Title, Last name, Date issued, Date returned.
• In cell D34, enter the base rate value, e.g., 5.
• In G38, enter the formula for rental time (in hours):
• =YEAR(F38-E38)-1900+MONTH(F38-E38)+DAY(F38-E38)*24-1
• In H38, enter the formula for rental time (in days):
• =YEAR(F38-E38)-1900+MONTH(F38-E38)+DAY(F38-E38)-1
• In I38, enter the formula for payment:
• =IF(G38<=24,$D$34,IF(AND(G38>24,G38<=48),$D$34+$D$34*0.8,IF(G38>48,$D$34+$D$34*0.8+(G38-48)/24*3*$D$34)))

• Copy the formulas into the corresponding ranges G39:G44, H39:H44, I39:I44.
• Format all results on the worksheet.

Apartment Sales Statement

Suppose we need to prepare an apartment sales statement according to the template. Perform the following steps:

• Prepare the necessary apartment data in the form of a list.

• In cell G2, enter the formula:

• For the range G3:G5, either use the AutoFill handle or copy the formula.
• If necessary, format the resulting list of announcements using the options in Format as Table, located in the Styles group on the Home tab of the ribbon.

Statement on Revaluation of Fixed Assets

Next, let’s look at an example of preparing a statement for revaluation of fixed assets according to the template in Figure.

• In cell A1, enter the title: Statement on the Revaluation of Fixed Assets.
• In cells A4:F4, enter the field names: Object Name, Book Value (BV), Depreciation (Dep), Net Book Value (NBV), Replacement Full Value (RFV), Replacement Net Value (RNV).
  • The Object Name field includes the following rows: Management & Marketing Dept., Transport Dept., Assembly Shop, Finishing Shop, Warehouse 1, Warehouse 2, Warehouse 3, Total.
• Formulas for calculations:
  • NBV = BV – Dep
  • RFV = BV * K
  • RNV = NBV * K

where K is a coefficient:

• • 3.3 if BV ≤ 650 million rubles
  • 4.2 if BV > 650 million but < 1000 million rubles
  • 5.1 if BV ≥ 1000 million rubles
• Use the following formulas for automatic calculations:

In D5:

=B5-C5

In E5:

=B5*IF(B5<=600,3.3,IF(AND(B5>600,B5<1000),4.2,5.1))

In F5:

=D5*IF(B5<=600,3.3,IF(AND(B5>600,B5<1000),4.2,5.1))

• For the total row (Итого), for example in cell B12, enter:

=SUM(B5:B11)

Or select cells B12:F12 and use AutoSum (Formulas tab → Function Library → AutoSum).

Note: The dropdown arrow of the AutoSum button allows automatic calculations with other functions (Average, Max, Min, etc.).

• Format the resulting table and the title of the statement.

Reporting Statement for a Computer Club Network

In the next example, we prepare a reporting statement for the operation of a computer club network.

• In cell A1, enter the title: Statement of Computer Club Network Operations.
• In cells A3:H3, enter the field names: Club, January, February, March, Total Revenue, Rank, Average Revenue, Percentage.
  • The Club field includes: Altair, Gruvit, Polygon, Galax, Zvezda, Hexen, Antei, Arsenal, Arena, Bunker, Total.
• The main formulas for calculations (to be copied for other rows) are given.
• Format the resulting statement.

Table. Formulas for Calculation

Cell	Formula	Cell	Formula
E4	=SUM(B4:D4)	G4	=AVERAGE(B4:D4)
B14	=SUM(B4:B13)	G14	=AVERAGE(G4:G13)
F4	=RANK(E4,$E$4:$E$13)	H4	=E4/$E$14

Payroll Statement

Now let’s prepare the payroll statement for the employees of the Scientific and Project Department “Alpha” .

• In cell A2, enter the title of the statement: Payroll Statement of the Employees of the Scientific and Project Department “Alpha”. Align it to the right (for example, using the Align Right button in the Alignment group on the Home tab of the ribbon).

• In cells A3:K3, enter the field names of the statement:
  • № (serial number),
  • Last name & initials,
  • Position,
  • Salary rate,
  • Years of service,
  • k,
  • Service allowance,
  • Total,
  • Tax rate,
  • To withhold,
  • Net pay.
• Add hidden comments to the header (see Figure 3.27):
  • № — employee number;
  • Last name & initials — all employees of the department;
  • Position — job title at the time of filling in the statement;
  • Salary rate — monetary equivalent of the job position;
  • Years of service — integer number of years worked;
  • k — coefficient for years of service;
  • Service allowance — monetary equivalent for years of service;
  • Total — gross salary including base rate and service allowance;
  • Tax rate — percentage of budget deductions;
  • To withhold — monetary equivalent of deductions;
  • Net pay — amount to be issued.

To create comments: go to the Review tab → Comments group → New Comment (other tools in this group allow managing comments in the workbook).

1. Calculations: k, Service allowance, Total, Tax rate, To withhold, Net pay are calculated with formulas using AutoFill or copy.
   • Coefficient k is assigned as follows:
     • 0.1 — up to 5 years (inclusive),
     • 0.2 — from 5 to 10 years (inclusive),
     • 0.25 — from 10 to 15 years (inclusive),
     • 0.3 — more than 15 years.

Formula for cell F4:

=IF(E4<=5,0.1,IF(AND(E4>5,E4<=10),0.2,IF(AND(E4>10,E4<=15),0.25,0.3)))

Formula for cell G4 (service allowance):

=D4*F4

Custom number format for G4:

# ##0.00р.

Formula for cell H4 (Total):

=D4+G4

Custom number format for H4:

# ##0.00р.

Formula for cell I4 (Tax rate):

=IF(H4<=7000,0.02,IF(AND(H4>7000,H4<=10000),0.1,IF(AND(H4>10000,H4<=25000),0.2,0.35)))

Format of I4: Percentage.

Formula for cell J4 (To withhold):

=H4*I4

Custom number format for J4:

# ##0.00р.

Net pay (column K): Total minus To withhold.

1. Requirements for the Years of Service column (E):
   Create a custom conditional formatting style:
   • up to 5 years → yellow,
   • 5–10 years → blue,
   • 10–15 years → green,
   • more than 15 years → red.

Use Conditional Formatting (Home tab → Styles group).

If a negative number of years is entered, a corresponding error message should appear (Figure 3.29). This is set up through Data Validation → Error Alert tab.

1. For the Salary rate field, display a constant message:
   “Salary rate. BE CAREFUL WHEN ENTERING THE SALARY RATE” .

To do this, use Data Validation → Input Message tab.

 

If negative salary values are entered, a warning appears:

This is done using a custom number format:

# ##0.00р.;[Red] »Salary rate cannot be negative! »

 

If a formula contains mistakes, the result in the cell will be an error value. The first character of an error in MS Excel is the symbol #, followed by text. The text of an error value may end with an exclamation point or a question mark. However, not all errors can be identified this way.

To make troubleshooting easier, you can switch the worksheet to display formulas in cells instead of their calculated results.
Go to the File tab → Options → in Excel Options select Advanced → in Display options for this worksheet, check Show formulas in cells instead of their calculated results.
You can also use Formulas tab → Formula Auditing group → Show Formulas.

To trace dependencies—that is, to graphically display the relationships between precedent and dependent cells—use the commands in Formulas tab → Formula Auditing group . A dependent cell contains a formula that refers to the active cell. A precedent cell is one that the formula in the active cell refers to.

If a cell shows an error value, you can try to determine the likely cause using Trace Error, which you can select from the Error Checking dropdown in the Formula Auditing group: arrows will point to the cells involved in the error.

Finding errors can take time. A helpful tool is Home tab → Editing group → Find & Select → Go To Special…. In the Go To Special dialog, you can select parts of the worksheet that meet specific criteria.

To move the active cell within a previously selected range while preserving the selection, use Tab (forward) or Shift+Tab (backward).

TIP
To find errors in formulas:

1. Select the cell that returns an incorrect result or an error value.
2. In the formula bar, select the part of the formula you suspect.
3. Press F9 to evaluate the selected part: if FALSE appears, there is an error.
4. In the same way, evaluate other parts of the formula until you find the error.
5. To revert the formula to its original state (without the literal FALSE or the evaluated part), press Esc or click Cancel in the formula bar.
6. Correct the erroneous part of the formula.

VBA also has a large set of built-in functions and procedures, the use of which greatly simplifies programming. Like worksheet functions, the functions available in VBA can be divided into several main categories.

It should be noted that all the necessary information related to the use of available functions can be found in the VBA Help system:

• Open the Visual Basic for Applications editor window and select the command Help | Visual Basic for Applications Help (or press the F1 key).
• In the Excel Help window, select in the table of contents on the left: Visual Basic for Applications Language Reference | Visual Basic Language Reference | Functions.

From there, you can see the full list of VBA functions and examples of their use.

Creating complex formulas is usually associated with the use of built-in logical functions in MS Excel .

Table . Logical Functions in MS Excel

Function (Russian)	Function (English)	Description
ЕСЛИ(логич_выражение; значение_если_истина; значение_если_ложь)	IF()	Logical branching (up to 64 nestings allowed): logical_expression — any value or expression returning TRUE or FALSE; value_if_true — the value returned if the logical expression is TRUE; value_if_false — the value returned if the logical expression is FALSE.
И(логич_значение1; логич_значение2; …)	AND()	Logical multiplication: returns TRUE if all arguments are TRUE; returns FALSE if at least one argument is FALSE.
ИЛИ(логич_значение1; логич_значение2; …)	OR()	Logical addition: returns TRUE if at least one argument is TRUE; returns FALSE if all arguments are FALSE.
НЕ(логич_значение)	NOT()	Logical negation: reverses the logical value of its argument.

Let’s take a closer look at the logical function IF():

=IF(logical_test, value_if_true, value_if_false)

This expression can be extended by nesting another IF() function inside the last argument:

=IF(condition1, value_if_true1,
   IF(condition2, value_if_true2,
      IF(condition3, value_if_true3, value_if_false)))

NOTE
As mentioned earlier, formulas can be copied and moved. However, keep in mind that relative references contained in formulas will change accordingly. Moving formulas is a rather risky operation, so caution is required.
To avoid issues, it is recommended to assign names to cells, since names always refer to the same values regardless of where or how they are moved.?

 

In the process of calculations, MS Excel uses various formulas, where the argument can be a constant, a cell reference, or the name of a range of cells. MS Excel provides many built-in functions into which these formulas are already embedded. The values to which the function should be applied are specified as the function’s arguments:

=FUNCTION_NAME(Arguments)

There are no restrictions on formulas containing functions compared to other formulas, including copying them (taking into account the type of reference — relative or absolute).

A full list of Excel functions can be found on the Formulas tab in the Function Library group of commands. Alternatively, by clicking the Insert Function button in this group, you open the Function Wizard, where you can also select the required function and obtain relevant help information.

In general, formulas may include various references, operators, and functions. It is possible to specify ranges from other worksheets and workbooks as arguments:

=SUM(C7:C9, Sheet3!D8:D15, [Workbook1]Sheet5!$E$8:$E$23)

When specifying a range of cells as an argument, you can move the Function Wizard window (if it blocks your view) and select the required range with the mouse.

When indicating a range of cells as an argument, both contiguous and noncontiguous ranges can be used. The address of a contiguous range is specified by indicating the first and last cells separated by a colon. Three or more noncontiguous ranges are separated by semicolons.

Sometimes a function itself serves as an argument for another function. Such functions are called nested functions. For example:

=SUM(A1, SUM(A5,A6))

MS Excel allows up to 64 levels of function nesting in worksheet formulas.

A formula may contain functions and mathematical operators, the order of calculation of which corresponds to the standard rules of mathematics. The result of formulas including arithmetic operators is a numeric value, while in the case of comparison operators the result is a logical value — TRUE or FALSE.

Table 1. Mathematical Operators in Excel Formulas

Operator	Meaning	Operator	Meaning
(	Open parenthesis	=	Equal
)	Close parenthesis	<	Less than
*	Multiplication	<=	Less than or equal
/	Division	>	Greater than
+	Addition	>=	Greater or equal
–	Subtraction	<>	Not equal
^	Exponentiation	%	Percentage

NOTE
The percent symbol is an operator which, in MS Excel formulas, divides the preceding number by 100. For example:

• Formula =5% results in 0.05,
• Formula =12781193%%% results in 12.781193.

MS Excel can process not only arithmetic formulas, but also perform operations with text, compare values, and relate various ranges and cells in a workbook.

Operations with Text and Dates

Concatenation — combining text, numbers, and dates within a single cell.
The concatenation operator is the & symbol, which joins text, numbers, and dates into one long text string.

Example. Combine in one cell the data located in different cells of an Excel worksheet.
In Fig, the following formula is entered in cell A3:

=A1 & TEXT(B1," D MMM YYYY ") & C1 & TEXT(D1," # ##0р.")

Here the TEXT() function applies a new date format and a currency format to the contents of cells B1 and D1, converting them to text.

Text, dates, and time are entered into formulas using quotes. For example, the formula:

="Total " & TOTALS

will return the text:

Total 1 500 000 р.

if the named cell TOTALS contains the number 1500000.

Explicit date operations (dates written directly in formulas):

="15/02/11" - "11/02/11"
="24 February 2011" - "26 May 2010"

These formulas return the number of days between the two dates.

Comparison and Reference Operations

Examples of comparison operations in formulas:

• =A1<10 → TRUE if the content of cell A1 is less than 10; FALSE otherwise.
• =B7>=15 → TRUE if the content of cell B7 is greater than or equal to 15; FALSE if less than 15.

Table 2. Reference Operators in Excel

Operator	Example	Operation	Result
:	SUM(A1:A7)	Range	Reference to all cells in the rectangular range between two corners
,	SUM(A1:A7,B8)	Union	Union of two ranges: all cells from both ranges
(space)	SUM(A1:A7 A16:B300)	Intersection	Intersection of two ranges: all cells common to both ranges (if none, returns #NULL!)
(space)	=Y78 Credit	Intersection	Content of the cell at the intersection of the column named Y78 and the row named Credit

Table of Operation Precedence in Excel (Descending)

Table 3. Operation Precedence in MS Excel

Operator	Operation	Operator	Operation
(space)	Intersection	* and /	Multiplication, Division
,	Union	+ and –	Addition, Subtraction
–	Negation	&	Text concatenation
%	Percentage	=, <, <=	Comparisons
^	Exponentiation

Automatic Calculation

MS Excel provides the ability to automatically perform the most common calculations for a selected data range (average value, count of values, count of numbers, maximum, minimum, sum).

To do this, in the status bar under the AutoCalculate area, you can select the required function from the context menu (by right-clicking).

References make formulas more convenient because they allow you to use data from multiple cells, tables, and workbooks. References can identify both individual cells and groups of cells.

Earlier we examined two cell reference styles: A1 and R1C1. When using references in formulas, you can enter their names from the keyboard or select the required cells with the mouse.

The Range and Selection Objects

Regarding the Range and Selection objects, note that in the MS Excel hierarchy the Range object (range) comes immediately after the Worksheet object. The Range object is one of the key objects in VBA.

The Selection object in VBA appears in two ways: either as the result of the Select method, or when calling the Selection property. The type of object returned depends on the type of object selected. Most often, the Selection object belongs to the Range class, and when working with it you can use the properties and methods of the Range object.

The Range object can be returned as an element of the Range or Cells collections, through the properties Range, Cells, and Offset, or by the methods ActiveCell, Intersect, and Union.

Relative, Absolute, and Mixed Referencing

When addressing cells, relative, absolute, and mixed references can be used.

• Relative referencing is based on the position of the cell containing the formula. When the formula is copied to another cell, the references in each copy change to preserve the same relationships as in the original formula.
  Example: A1 or C2 — if the A1 style is used; or R1C1 or R2C3 — if the R1C1 style is used.
• Absolute referencing keeps the reference unchanged when formulas are copied (the reference always points to the same cell).
  A reference in absolute style contains the row number and the column letter preceded by a dollar sign.
  Example: $A10, A$10, and $A$10 fix the column, the row, and both, respectively.

In the R1C1 style, absolute referencing works differently: if the active cell is R2C3, then R[1]C[-1] refers to cell R3C2.

• Mixed referencing is used when you want only the row or only the column to remain unchanged when copying. In this case, the reference contains both absolute and relative parts.

The key while editing formulas cycles through all combinations of relative and absolute references.

References to Other Worksheets or Workbooks

A reference to another sheet in the same workbook is created by including the sheet name in the formula:

Sheet5!A1

Here, the exclamation mark ! is mandatory. If the sheet name contains spaces, the reference must be enclosed in quotation marks.

External references are references to cells located in other workbooks and must include the workbook name enclosed in square brackets:

[Book1]Sheet3!$B$4

Three-dimensional (3D) references consist of a range of sheets (with the first and last specified) and a range of cells:

=SUM(Sheet1:Sheet6!$E$1:$E$6)

In this formula, the values in the range $E$1:$E$6 are summed across all sheets from Sheet1 to Sheet6.

Built-in Functions Supporting 3D References

Three-dimensional references can be used in the following built-in MS Excel functions:

• VAR
• VARP
• MAX
• MIN
• PRODUCT
• AVERAGE
• STDEV
• STDEVP
• SUM
• COUNT
• COUNTA

It is convenient to use names as addresses in formulas (both for individual cells and for ranges of cells).

Assigning Groups of Rows and Columns

If only the names of columns or rows are specified in a range, the Range object defines a range consisting of the specified columns or rows. For example, Range(« A:C ») defines a range consisting of columns A, B, and C, while Range(« 2:2 ») defines the second row.
Another way to work with rows and columns is through the Rows and Columns properties of the worksheet, which return collections of rows and columns. For example, column A is Columns(1), and the second row is Rows(2).

Relationship Between the Range Object and the Cells Property of the Worksheet Object

A cell is a special case of a range, consisting of a single cell. Therefore, it is natural that the Range object can be used both for ranges of cells and for a single cell.
An alternative way to work with a cell is through the Cells property of the Worksheet object. For example, the cell A2 as an object can be described in two equivalent ways: Range(« A2 ») and Cells(1, 2).

In turn, a cell returned by the Cells property, when used as a parameter of the Range object, allows writing a range in an alternative form, which is sometimes more convenient for work. As an example of this form of recording a range, consider the following two instructions, both of which return the same range:

Range("A2:C3")
Range(Cells(1,2), Cells(3,3))

NOTE

A range, like a worksheet, has the Cells property which, when used without parameters, returns the collection of all cells included in the range. If used with parameters, it returns a specific cell from the range. In the following example, the value 2 is entered into cell C3:

Range("B2:D4").Select
Selection.Cells(2, 2).Value = 2

Properties of the Range Object

The Range object combines the flexibility of VBA with the power of the worksheet. The large number of built-in worksheet functions significantly simplifies and makes programming in VBA more intuitive.
The properties of the Range object allow you to control it—from appearance to calculation automation. The main properties of the Range object are the following:

• Address
• AllowEdit
• Areas
• Borders
• Cells
• Characters
• Column
• Columns
• ColumnWidth
• Comment
• Count
• CurrentRegion
• End
• EntireColumn
• EntireRow
• Font
• Formula
• FormulaArray
• FormulaHidden
• FormulaLocal
• FormulaR1C1
• FormulaR1C1Local
• HasFormula
• Height
• Hidden
• HorizontalAlignment
• Hyperlinks
• Interior
• Left
• Locked
• Name
• NumberFormat
• Offset
• Orientation
• Resize
• Row
• RowHeight
• Rows
• ShrinkToFit
• Top
• UseStandardHeight
• UseStandardWidth
• Value
• VerticalAlignment
• Width
• Worksheet
• WrapText

Entering or Reading a Value from a Range

The Value property of the Range object returns or sets the value in the cells of a range. In the first instruction of this example, the variable x is assigned the value from cell C1; in the second, the string « Report » is entered into cell C3; and in the third, the number 1 is entered into each cell of the range A1:B2:

x = Range("C1").Value
Range("C3").Value = "Report"
Range("A1:B2").Value = 1

Entering an Array of Values into a Range

A range can be filled not only cell by cell, but also in one operation, by assigning either a variable of type Variant or directly an array of values (as shown in the second example, Listing 3.16).

Entering an Array of Values into a Range. First Example

Sub DemoInput1()
    Dim s As Variant
    s = Array("1", "2")
    Range("Sheet1!A1:B1").Value = s
End Sub

Entering an Array of Values into a Range. Second Example

Sub DemoInput2()
    Dim t(8, 8) As Integer
    Dim i As Integer
    Dim j As Integer
    For i = 1 To 9
        For j = 1 To 9
            t(i - 1, j - 1) = i * j
        Next
    Next
    Range(Cells(1, 1), Cells(9, 9)).Value = t
End Sub

Searching for Pattern-Matching Values in a Range

Sequential iteration through the cells of a range and comparing the returned Value property with a pattern using the Like operator allows you to implement a search for similar values in a range.

For example, in the following code, all cells of the range A1:A100 are checked in sequence. For those cells containing the substring “MS,” the contents of the cell are replaced with the word “Microsoft,” the cell itself is filled with yellow, while all other cells are filled with white.

Searching for Similar Values in a Range

Dim c As Range
For Each c In [A1:A100]
    If c.Value Like "*MS*" Then
        c.Value = "Microsoft"
        c.Interior.Color = RGB(255, 255, 0)
    Else
        c.Interior.Color = RGB(255, 255, 255)
    End If
Next

Entering or Reading a Formula into a Cell in A1 Format

The Formula property of the Range object returns or sets the formula in a range in A1 format. For example:

Range("C1").Formula = "=$A$1+$B$1"
Range("C2").Formula = "=SIN(A2)^2"

Entering or Reading a Formula into a Cell in R1C1 Format

The FormulaR1C1 property of the Range object returns the formula in R1C1 format. For example:

Range("B1").FormulaR1C1 = "=2*R3C2"

This is equivalent to the formula =2*$B$3 in A1 format.

Entering or Reading a Local Version Formula in A1 Format

The FormulaLocal property of the Range object returns the localized version of the formula in A1 format. For example:

Range("B2").FormulaLocal = "=SUM(C1:C4)"

Entering or Reading a Local Version Formula in R1C1 Format

The FormulaR1C1Local property of the Range object returns the localized version of the formula in R1C1 format. For example:

Range("B2").FormulaR1C1Local = "=SUM(R1C3:R4C3)"

Entering an Array Formula into a Range

The FormulaArray property of the Range object returns the array formula in A1 format. Unlike a regular worksheet formula, an array formula is entered on the worksheet not by pressing <Enter> but by pressing <Ctrl>+<Shift>+<Enter>.

For example:

Range("E1:E3").FormulaArray = "=A1:A3*3"

Entering a Local Version Array Formula into a Range

When entering an array formula with localized worksheet functions, the formula must be presented in R1C1 format, and instead of the localized version, the base version of the function must be used.

For example:

Range("D1").FormulaArray = "=SUM(R1C1:R1C2*3)"

Entering an Array Formula with Relative Cell References

To enter an array formula with relative cell references, you must use relative addressing in R1C1 format.

For example:

Range("D1").FormulaArray = "=SUM(RC[-3]:RC[-1]*3)"

How to Find Out if a Formula is Hidden on a Protected Worksheet

The FormulaHidden property of the Range object returns True if the formula is hidden on a protected worksheet.

How to Find Out if a Cell Contains a Formula

The HasFormula property of the Range object returns:

• True if all cells in the range contain formulas,
• False if none of the cells contain a formula,
• Null in all other cases.

For example, the following code checks whether cell C1 contains a formula, and if not, it inserts the formula =1 into that cell:

If Not Range("C1").HasFormula Then Range("C1").Formula = "=1"

Determining the Address of a Cell

The Address property of the Range object returns the address of a range.

Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)

• RowAbsolute — optional Boolean parameter. If its value is True or omitted, the reference to the row is returned as absolute.
• ColumnAbsolute — optional Boolean parameter. If its value is True or omitted, the reference to the column is returned as absolute.
• ReferenceStyle — optional parameter. Accepts two values: xlA1 and xlR1C1. If omitted, the reference is returned in A1 format.
• External — optional Boolean parameter. Determines whether the reference is external.
• RelativeTo — optional parameter. If both RowAbsolute and ColumnAbsolute are set to False and ReferenceStyle is xlR1C1, this parameter specifies the starting cell relative to which the addressing is performed.

The following code, which handles the SelectionChange event of the Worksheet object, demonstrates the values returned by the Address property with various parameter settings.

For example, if cell A1 is selected on the worksheet, the following message box will appear:

$A$1
$A1
R1C1
R[-1]C[-1]

The Address Property. Worksheet Module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox Target.Address() & vbCr & _
           Target.Address(RowAbsolute:=False) & vbCr & _
           Target.Address(ReferenceStyle:=xlR1C1) & vbCr & _
           Target.Address(ReferenceStyle:=xlR1C1, RowAbsolute:=False, _
           ColumnAbsolute:=False, _
           RelativeTo:=Worksheets(1).Cells(2, 2))
End Sub

Can a Cell Be Edited on a Worksheet?

The read-only AllowEdit property of the Range object returns True if editing of values in the specified range is allowed, even when the worksheet is protected.

Determining the Number of Areas that Make Up a Range

The Areas property of the Range object returns a collection of Areas that make up the range. The elements of this collection are themselves Range objects.
The main property of this collection is Count, which returns the number of elements in the collection.

For example, the following code , which handles the SelectionChange event of the Worksheet object, displays in the status bar the number of selected areas .

Displaying the Number of Selected Areas in the Status Bar. Worksheet Module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.StatusBar = "Number of areas: " & Target.Areas.Count
End Sub

In addition to formatting numeric data, MS Excel provides general formatting options for data in cells or tables—such as text alignment, font selection, borders, background color, etc. For formatting a range of cells, it is convenient to use the commands located in the Styles group on the Home tab of the Ribbon. Of course, individual cells can also be formatted “manually” using various commands on the Home tab.

Automatic Table Reformatting on Data Change
The Change event of the Worksheet object is triggered when values in a worksheet range are changed. This event allows automatic reformatting of a table when new data is entered.

For example, suppose the range B2:B13 contains potato sales data for the company « Rodnye Prostory ». The goal is to:

• Highlight the maximum sales values in bold red.
• Highlight the minimum sales values in blue.
• Keep other values in black.
• Fill cells with sales above the average with yellow.

This automatic reformatting is implemented using the Change event of the Worksheet object.

Managing Range Borders and the Border Object
The Borders property of a Range object returns a collection of Borders objects, each representing one edge or diagonal of the range. Valid constants for the Borders index include: xlDiagonalDown, xlDiagonalUp, xlEdgeBottom, xlEdgeLeft, xlEdgeRight, xlEdgeTop, xlInsideHorizontal, and xlInsideVertical. Each border is a Border object with properties listed in Table 3.8.

Table 1. Properties of the Border Object

Property	Description
Color	Border color specified using the RGB model
ColorIndex	Border color specified by palette index
LineStyle	Border style (xlContinuous, xlDash, xlDashDot, xlDashDotDot, xlDot, xlDouble, xlSlantDashDot, xlLineStyleNone)
Weight	Border thickness (xlHairline, xlThin, xlMedium, xlThick)

For example, the following code sets the top border of range A2:E2 on Sheet3 as a thick red line, and the bottom border as a medium green dashed line:

Sub DemoBorders()
    Dim rgn As Range
    Set rgn = Range("Лист3!A2:E2")
    With rgn.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .Color = RGB(255, 0, 0)
    End With
    With rgn.Borders(xlEdgeBottom)
        .LineStyle = xlDash
        .Weight = xlMedium
        .Color = RGB(0, 255, 0)
    End With
End Sub

If all border components have the same parameters, you can apply formatting to the entire Borders collection:

Selection.Borders.Color = RGB(0, 0, 255)

The RGB() and QBColor() Functions
In VBA, colors are often specified numerically in hexadecimal. Instead of direct hex codes, it is often easier to use the RGB() and QBColor() functions.

The RGB() function creates a color by mixing red, green, and blue components:

RGB(Red, Green, Blue)

• Red – integer 0–255, red component.
• Green – integer 0–255, green component.
• Blue – integer 0–255, blue component.

Table 2. RGB values for standard colors

Color	Red	Green	Blue
Black	0	0	0
Blue	0	0	255
Green	0	255	0
Cyan	0	255	255
Red	255	0	0
Magenta	255	0	255
Yellow	255	255	0
White	255	255	255

The QBColor() function returns 16 basic colors based on the parameter value:

QBColor(color)

The color parameter accepts integers 0–15.

Table 3. QBColor color codes

Number	Color	Number	Color
0	Black	8	Gray
1	Blue	9	Light Blue
2	Green	10	Light Green
3	Cyan	11	Light Cyan
4	Red	12	Light Red
5	Magenta	13	Light Magenta
6	Yellow	14	Light Yellow
7	White	15	Bright White

Characters Object (how to format part of a cell’s content)

The Characters property of the Range object returns a Characters object, which represents a string of the specified length starting from the specified character. It is often used when it is necessary to format not the entire content of a cell, but only part of it.

Characters(Start, Length)

• Start — optional parameter that specifies the number of the first returned character from the given string.
• Length — optional parameter that specifies the number of returned characters.

In the following example, the string « Andrey Garnaev and Lada Rudikova » is displayed in cell A1. The first part of this string (« Andrey Garnaev ») is displayed in bold green font, 16 pt, the second part (« and ») is displayed in italic black font, 12 pt, and the third part (« Lada Rudikova ») is displayed in bold red font, 16 pt.

Formatting part of a cell’s content

Sub CharColor()
    With Range("A1")
        .Value = "Kolo Holo and Mbeu Moyo"        
        .Characters(1, 9).Font.Bold = True
        .Characters(1, 9).Font.Size = 16
        .Characters(1, 9).Font.Color = RGB(0, 255, 0)        
        .Characters(11, 3).Font.Italic = True
        .Characters(11, 3).Font.Size = 12
        .Characters(11, 3).Font.Color = RGB(0, 0, 0)
        .Characters(15, 9).Font.Bold = True
        .Characters(15, 9).Font.Italic = False
        .Characters(15, 9).Font.Size = 16
        .Characters(15, 9).Font.Color = RGB(255, 0, 0)
    End With
End Sub

Font Object (specifying a font)

The Font property of the Range object returns a Font object, which represents the font.

Table 1. Properties of the Font object

Property	Description
Bold	Determines whether the font is bold.
Color	Sets the font color according to the RGB model.
ColorIndex	Sets the indexed color according to the current color palette.
FontStyle	Specifies the font style in textual form. Allowed values: Regular, Bold, Italic, Bold Italic.
Italic	Determines whether the font is italic.
Name	A string specifying the font name, e.g., « Arial Cyr ».
Size	Font size.
Strikethrough	Specifies whether a line runs through the text (strikethrough).
Superscript	Specifies whether the text is used as superscript.
Subscript	Specifies whether the text is used as subscript.
Underline	Specifies the type of underline. Allowed values: xlNone (no underline), xlSingle (single, by value), xlDouble (double, by value), xlSingleAccounting (single, by cell), xlDoubleAccounting (double, by cell).

For example, in the following code, the range A1:B2 is set to bold red font, size 14 pt:

With Range("A1:B2").Font
    .Size = 14
    .Bold = True
    .Color = RGB(255, 0, 0)
End With

Interior Object (range fill)

The Interior property of the Range object returns an Interior object, which encapsulates data about the fill of a range.

Table 2. Properties of the Interior object

Property	Description
Color	Sets the fill color according to the RGB model.
ColorIndex	Sets the indexed fill color according to the current color palette.
Pattern	Sets the fill pattern. Allowed values include: xlPatternAutomatic, xlPatternChecker, xlPatternCrissCross, xlPatternDown, xlPatternGray16, xlPatternGray25, xlPatternGray50, xlPatternGray75, xlPatternGray8, xlPatternGrid, xlPatternHorizontal, xlPatternLightDown, xlPatternLightHorizontal, xlPatternLightUp, xlPatternLightVertical, xlPatternNone, xlPatternSemiGray75, xlPatternSolid, xlPatternUp, xlPatternVertical.
PatternColor	Sets the pattern color according to the RGB model.
PatternColorIndex	Sets the indexed pattern color according to the current color palette.

In the following example, a red fill with a blue checker pattern is set for the range A1:D5 on Sheet2 .

Range fill

Sub Inter()
    With Worksheets("Sheet2").Range("A1:D5").Interior
        .Color = RGB(255, 0, 0)           
        .Pattern = xlPatternChecker       
        .PatternColor = RGB(0, 0, 255)    
    End With
End Sub

Setting the Number Format

The NumberFormat property of the Range object sets the number format.
For example, the following instructions set:

• in cell A1 — the General format,
• in cell A2 — the Number format displaying three digits after the decimal point, e.g., 12.000,
• in cell A3 — the Time format with a colon as the separator and two digits for hours, minutes, and seconds, e.g., 02:12:55,
• in cell A4 — the Date format with two digits for the day, three letters for the month, and four digits for the year, e.g., 01 Feb 2011.

Range("A1").NumberFormat = "General"
Range("A2").NumberFormat = "0.000"
Range("A3").NumberFormat = "hh:mm:ss"
Range("A4").NumberFormat = "d mmm yyyy"

Setting the Orientation of Text in a Range

The Orientation property of the Range object sets the angle at which text is displayed in a range.
Allowed values are either an angle of text rotation in degrees from –90 to 90, or one of the following constants:

• xlDownward — left alignment from top to bottom, corresponds to –90°,
• xlHorizontal — horizontal alignment, corresponds to 0°,
• xlUpward — right alignment from bottom to top, corresponds to 90°,
• xlVertical — vertical alignment, no degree equivalent.

For example, in the following code, in cell A1 the text is displayed at an angle of 45°, and in cell B1 at an angle of –45°.

Setting the text orientation angle

Sub Orient()
    With Worksheets("Sheet1")
        ' Taille police
        .Range("A1:B1").Font.Size = 16
        .Range("A1").Orientation = 45
        .Range("A1").Value = "Kolo"
        .Range("B1").Orientation = -45
        .Range("B1").Value = "Holo"
    End With
End Sub