Étiquette : formulas_functions

In Excel 2010, users are provided with extensive options for conditional formatting (based on numbers, percentages, formulas, percentiles), which allows for visually highlighting entered values using color scales, icon sets, and other formatting features.

The Conditional Formatting Rules Manager window , used to set constraints and manage formatting rules, can be opened as follows: go to the Home tab on the Ribbon, in the Styles group, click the dropdown for Conditional Formatting, and select Manage Rules.

Conditional formatting offers the following capabilities:

• A simplified procedure for creating custom formats.
• A wide selection of formatting elements.
• The ability to specify multiple conditions in one format.
• Conditions can be based on custom formulas that return logical TRUE/FALSE values.
• You can choose whether to check the value in a cell or the result of a formula.
• When comparing with cell values, you can use either numbers or references to other cells.

Conditional formats are especially valuable for error control and data analysis.

In MS Excel, you can define your own custom number format. A custom format can be set as follows:

• Select the desired range of cells.
• Use Format | Format Cells | Number tab, located in the Cells group on the Home tab (or via the context menu of the selected area).
• In the Format Cells dialog, choose All Formats from the list of numeric formats, then enter the desired custom format in the Type field

Custom formats can consist of four sections, separated by semicolons (;):

1. Positive number format (for positive numbers)
2. Negative number format (for negative numbers)
3. Zero format (for zero)
4. Text format (for text)

When defining custom formats, you can use the symbols shown in Table 1.

Table 1. Custom Format Symbols

Symbol	Function
#	Placeholder for digits. Leading zeros are not displayed. Decimal places are rounded to the number of # symbols after the decimal. Example: in #_###,# format displays as 7.8.
0	Placeholder for digits. Displays 0 when no digit is present. Decimal places are rounded to the specified number of zeros.
?	Similar to 0, but non-significant zeros are replaced by spaces to align numbers correctly. Useful in fractions with varying digits. Example: 10.25 in #’’??/?? displays as 10 1/4.
_ (underscore)	Adds a space the width of the following character. Often used with ) to align positive and negative numbers in parentheses.
. or ,	Decimal separator (set in Windows Control Panel, Region settings).
(thousands separator)	Separates digit groups in numbers; usually space or comma, set in Windows Control Panel.
%	Multiplies by 100 and displays as a percentage with %.
E, e	Displays number in exponential notation. The 0 or # after E defines exponent digits.
: р.-+()	Displays these symbols in the formatted number.
/	Fraction separator. Example: 1 1/5 displays as a fraction.
\	Displays the next character literally.
«  »	Displays text in quotes.
*	Fills the remaining width of the cell with the following character.
@	Specifies where text will appear.
[color]	Applies a specific color to the cell content.
[condition]	Sets a numeric condition (e.g., <, >, =, <=, >=, <>) to apply this format.

To hide numbers using a custom format, leave the section between semicolons blank. Hidden numbers are still present and usable in formulas; they appear in the formula bar when the cell is selected.

To hide zeros:

• Create a custom format with white font for zeros;
• Use the IF() function;
• Or hide zeros for the entire sheet: File > Options > Advanced > Display options for this worksheet > uncheck « Show a zero in cells that have zero value ». Example formula:

=IF(A1+B3=0,  » « , A1+B3)

Creating Custom Date and Time Formats (Table 2)

Type/Symbol	Display Result
Days	D: 1–31; DD: 01–31; DDD: short weekday (Mon, …); DDDD: full weekday
Months	M: 1–12; MM: 01–12; MMM: short month name; MMMM: full month name
Years	YY: 00–99; YYYY: full year
Hours	H: 0–24; HH: 00–24
Minutes	m: 0–59; mm: 00–59
Seconds	S: 0–59; SS: 00–59
[ ]	Displays hours >24, minutes >59, or seconds >59
AM/PM, A/P	12-hour format
Separators (-, *, /, 🙂	Placed between date/time elements

Examples:

• DDDD → Monday
• MMMM D, YYYY → August 16, 2004
• [Blue] D MMM, YY → 16 Aug, 04 (in blue)

For headers with current date, concatenate text with TEXT() function:

="Today " & TEXT(TODAY(),"D MMM YYYY")

TODAY() updates the current date whenever the worksheet is opened.

Summary of Custom Format Rules:

• # and 0 define digits; 0 shows non-significant zeros, # omits them.
• Spaces are used for thousands separators.
• Comma , defines digits before/after the decimal.
• – before a number indicates negative numbers.
• Colors: [Black], [Blue], [Cyan], [Purple], [Red], [White], [Yellow] can highlight numbers.
• Symbols like -, /, or space can be used as separators.
• Quotes «  » display text literally.
• Conditions use <, >, =, <=, >=, <> in square brackets.
• Semicolons ; separate format sections.
• Currency formats can also include currency symbols.

Table 3. Examples of Custom Formats

Format	Cell Display
0	Rounded to nearest integer
# ##0	Rounded, thousands separator
0.00	Two decimal places
# ##0.00;[Red]-# ##0.00	Thousands separator, two decimals, negative numbers in red
#,##’C	Display degrees Celsius
« N »####-###; « Minus prohibited »; « Enter a number »	Example: 7893.152 → №7893-152; negative → « Minus prohibited »; zero → « Enter a number »

Custom Formats in VBA
You can also use the symbols from Table 1 in VBA with the Format() function.

Table 4. Examples in VBA

Format	Result
Format(1.2 ^ 2, « ##.### »)	1.44
Format(1.2 ^ 2, « ##.000 »)	1.440
Format(Sin(1) * Exp(5), « #.###e+## »)	1.249e+2
Format(Now, « hh:mm:ss »)	18:57:23
Format(Now, « dd/mm/yyyy »)	20/01/2002

Number Formatting
In VBA, numbers can be formatted using the specialized function FormatNumber().

FormatNumber(Expression[, NumDigitsAfterDecimal [, IncludeLeadingDigit [, UseParensForNegativeNumbers [, GroupDigits]]]])

• Expression — required parameter specifying the numeric expression to format.
• NumDigitsAfterDecimal — optional parameter specifying the number of digits to display after the decimal separator. Valid values: vbTrue, vbFalse, vbUseDefault.
• IncludeLeadingDigit — optional parameter specifying whether to display a leading zero. Valid values: vbTrue, vbFalse, vbUseDefault.
• UseParensForNegativeNumbers — optional parameter specifying whether negative numbers should be displayed in parentheses. Valid values: vbTrue, vbFalse, vbUseDefault.
• GroupDigits — optional parameter specifying whether to group digits. Valid values: vbTrue, vbFalse, vbUseDefault.

Table 5. Examples of Using FormatNumber()

Format	Result
FormatNumber(Sin(4), 3)	-0.757
FormatNumber(Sin(4), 3, vbTrue)	-0.757
Debug.Print (FormatNumber(Sin(4), 3, vbFalse))	-.757
FormatNumber(Sin(4), 3, vbFalse, vbTrue)	(.757)
FormatNumber(Sin(4), 3, vbFalse, vbFalse)	-.757

Percent Formatting
To format percentages in VBA, use the specialized function FormatPercent(), which has the same syntax as FormatNumber().

Example:

x = 0.2342
Debug.Print FormatPercent(x, 2)

Output in the Immediate window: 23.42% — i.e., the number formatted as a percentage with two digits after the decimal point.

Currency Formatting
To display numbers in currency format in VBA, use the specialized function FormatCurrency(), which has the same syntax as FormatNumber().

Example:

FormatCurrency(12312.3453, 2)

Output: 12,312.35

Date and Time Formatting
To format dates and times in VBA, use the specialized function FormatDateTime().

FormatDateTime(Date[, NamedFormat])

• Date — required parameter specifying the date to format.
• NamedFormat — optional parameter specifying the formatting style. Valid values: vbGeneralDate, vbLongDate, vbShortDate, vbLongTime, vbShortTime.

Example: Suppose this line was written on April 15, 2011, at 18:15.

FormatDateTime(Now, vbShortTime) ' Returns 18:15
FormatDateTime(Now, vbShortDate) ' Returns 15.04.2011

When working in Excel, it is useful to use comments, as they simplify viewing text attached to cells. To create and manage comments in MS Excel 2010, there is a Comments group on the Review tab of the ribbon. Alternatively, you can use the DisplayCommentIndicator property of the Application object to work with comments programmatically.

The DisplayCommentIndicator property of the Application object allows you to control the display style of comments. Acceptable values for this property are the following XlCommentDisplayMode constants:

• xlNoIndicator — no indicator;
• xlCommentIndicatorOnly — indicator only;
• xlCommentAndIndicator — both comment and indicator.

In the demonstration example, when the workbook is opened, comments are added to cells A1 and A4. When cell A1 is selected, both the comment and its indicator are displayed; when any other cell is selected, the comments are hidden.

Managing the Display of Comments and Their Indicators. ThisWorkbook Module

Private Sub Workbook_Open()
    Worksheets(1).Range("A1").ClearComments
    Worksheets(1).Range("A1").AddComment
    Worksheets(1).Range("A1").Comment.Visible = True
    Worksheets(1).Range("A1").Comment.Text Text:="This is cell A1"
    Worksheets(1).Range("A4").ClearComments
    Worksheets(1).Range("A4").AddComment
    Worksheets(1).Range("A4").Comment.Visible = True
    Worksheets(1).Range("A4").Comment.Text Text:="This is cell A4"
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
    If Sh.Name = Worksheets(1).Name Then
        If Target.Address = "$A$1" Then
            Application.DisplayCommentIndicator = xlCommentAndIndicator
        Else
            Application.DisplayCommentIndicator = xlNoIndicator
        End If
    End If
End Sub

The Replace method of the Range object performs replacements within a specified range.

Replace(What, Replacement, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat, ReplaceFormat)

• What — required parameter specifying the string to be replaced.
• Replacement — required parameter specifying the string to replace with.
• LookAt — optional parameter indicating how to search. Acceptable XlLookAt constants: xlWhole, xlPart.
• SearchOrder — optional parameter specifying the order in which to search the range. Acceptable XlSearchOrder constants: xlByRows, xlByColumns.
• SearchDirection — optional parameter specifying the search direction. Acceptable XlSearchDirection constants: xlNext, xlPrevious.
• MatchCase — optional parameter indicating whether to consider case in the search.
• MatchByte — optional parameter, rarely used.
• SearchFormat — optional parameter specifying the search format.
• ReplaceFormat — optional parameter specifying the replacement format.

For example, the following code replaces the string « MS » with « Microsoft » in column A:

Columns("A").Replace What:="MS", Replacement:="Microsoft", _
    SearchOrder:=xlByColumns, MatchCase:=True

The FindNext and FindPrevious methods of the Range object allow repeating the Find method to continue a specified search. The first method searches for the next cell, while the second searches for the previous cell that meets the search criteria.

FindNext(After)
FindPrevious(After)

Here, After is an optional parameter indicating the cell after which the search should continue.

As an example, the following code searches for the substring « BHV » case-insensitively in the range A1:A10. All found cells are filled with yellow.

Finding All Occurrences of a Substring in a Range

Sub Find2()
    Dim firstAddress As String
    Dim rng As Range   
    Set rng = Range("A1:A10").Find(What:="BHV", LookIn:=xlValues, _
        LookAt:=xlPart, MatchCase:=False)   
    If Not (rng Is Nothing) Then
        firstAddress = rng.Address
        Do
            rng.Interior.Color = RGB(255, 255, 0)
            Set rng = Range("A1:A10").FindNext(rng)
        Loop While Not (rng Is Nothing) And rng.Address <> firstAddress
    End If
End Sub

Commands from the Find & Select list on the Home tab in the Editing group allow you to quickly find and replace cell content according to specified criteria or simply perform a search. With VBA, you can also specify criteria for searching data within a specific range, perform replacements, etc. Let’s look at some examples.

Finding a Value in a Range
The Find method of the Range object searches for specified information within a given range and returns a reference to the first cell where the value is found. If the data is not found, the method returns Nothing.

Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

• What — required parameter specifying the data to search for.
• After — optional parameter indicating the cell after which to start the search.
• LookIn — optional parameter specifying where to search. Acceptable XlFindLookIn constants: xlComments, xlFormulas, xlValues.
• LookAt — optional parameter specifying how to search. Acceptable XlLookAt constants: xlWhole, xlPart.
• SearchOrder — optional parameter specifying the order of scanning the range. Acceptable XlSearchOrder constants: xlByRows, xlByColumns.
• SearchDirection — optional parameter specifying the search direction. Acceptable XlSearchDirection constants: xlNext, xlPrevious.
• MatchCase — optional parameter indicating whether to consider case.
• MatchByte — optional parameter, rarely used.
• SearchFormat — optional parameter specifying the search format.

For example, the following code  searches for the value 17 in the range A1:A10. If found, a message box displays the address of the first found cell.

Finding a Value

Sub Find1()
    Dim rng As Range
    Set rng = Range("A1:A10").Find(What:=17, LookIn:=xlValues)
    If Not (rng Is Nothing) Then
        MsgBox rng.Address
    Else
        MsgBox "Value not found"
    End If
End Sub

The code searches for the substring « BHV » case-insensitively in the range A1:A20. If found, a message box displays the Value of the found cell.

Finding a Substring Case-Insensitive

Sub DemoFindNoMatchCase()
    Dim rng As Range
    Set rng = Range("A1:A20").Find(What:="BHV", LookIn:=xlValues, _
        LookAt:=xlPart, MatchCase:=False)
    If Not (rng Is Nothing) Then
        MsgBox rng.Value
    Else
        MsgBox "No matching value found"
    End If
End Sub

AutoCorrect allows you to automatically replace certain typed characters (words) or abbreviations that were previously defined in the AutoCorrect dialog box.

The AutoCorrect property of the Application object returns an AutoCorrect object, which allows you to manage auto-correction on the worksheet. The properties of this object configure the parameters set in the AutoCorrect dialog on the AutoCorrect tab in the Replace as you type group: go to the File tab of the ribbon, click Options, in the Excel Options window select Proofing on the left, and in the AutoCorrect options group on the right, click the AutoCorrect Options button next to Correct spelling and formatting as you type.

For example, in the following code, the first procedure handles the Open event of the workbook and adds three new items to the AutoCorrect list. Specifically, спб will automatically be replaced with Санкт-Петербург, мск with Москва, and гр with Гродно. The second procedure handles the BeforeClose event triggered when closing the workbook, removing these three items from the AutoCorrect list.

The AutoFill method can be used to solve the problem of function tabulation, i.e., outputting its values as its parameter changes. For example, we may want to find the values of the function sin(x) for the parameter x changing from 0 to 2 in steps of 0.2.

First, enter the first term of the arithmetic sequence of the required parameter values into cell A1, and then use the DataSeries method to build the entire sequence down column A. Next, define the current range containing these values. The range where the corresponding function values will be placed is in column B, which can be obtained using the Offset property.

Finally, enter the formula =SIN(A1) in cell B1 to calculate the function value for the parameter equal to 0, and then copy this formula across the entire range allocated for the function values.

Function Tabulation

Sub DemoDataSeries()
    Range("A1").Value = 0
    Range("A1").DataSeries Rowcol:=xlColumns, Type:=xlDataSeriesLinear, _
        Step:=0.2, Stop:=2  
    Dim rgn As Range
    Set rgn = Range("A1").CurrentRegion
    Set rgn = rgn.Offset(0, 1)  
    Range("B1").Formula = "=SIN(A1)"
    Range("B1").AutoFill Destination:=rgn, Type:=xlCopy
End Sub

The AutoFill method of the Range object performs auto-filling of a range with sequence elements. The AutoFill method differs from the DataSeries method in that the range in which the progression will be placed is explicitly specified. The AutoFill method simulates the action of copying data to a range when the user places the mouse pointer on the fill handle of the source range and drags it down or to the right, selecting the entire range into which the source data is transferred.

expression.AutoFill(Destination, Type)

• expression — a required element that specifies the range from which filling begins.
• Destination — a required parameter that defines the range to be filled. This range must include the range specified in expression.
• Type — an optional parameter that specifies the type of fill. The permissible values are the following XlAutoFillType constants:
  xlFillDefault, xlFillSeries, xlFillCopy, xlFillFormats, xlFillValues, xlFillDays, xlFillWeekdays, xlFillMonths, xlFillYears, xlLinearTrend, xlGrowthTrend.
  By default, the type of fill that best matches the data in the range specified in expression is used.

For example, the following instructions  fill the range A1:A5 with the terms of an arithmetic progression, where the first two terms are 1 and 3 (i.e., the values that were previously entered into cells A1 and A2) .

Sequences. Arithmetic Sequence

Sub Progr4()
    Range("A1").Value = 1
    Range("A2").Value = 3
    Range("A1:A2").AutoFill Destination:=Range("A1:A5"), Type:=xlLinearTrend
End Sub

Listing demonstrates generating several terms of a geometric progression on a worksheet with the same two initial values in the range B1:B5 .

Listing. Sequences. Geometric Sequence

Sub Progr5()
    Range("B1").Value = 1
    Range("B2").Value = 3
    Range("B1:B2").AutoFill Destination:=Range("B1:B5"), Type:=xlGrowthTrend
End Sub

The following instructionsoutput into the range C1:C3 the sequence of values Summer 2010, Summer 2011, and Summer 2012 with a step of 1, determined by default by the AutoFill method.

Sequences. AutoFill

Sub Progr6()
    Range("C1").Value = "Summer 2010"
    Range("C1").AutoFill Destination:=Range("C1:C3"), Type:=xlFillSeries
End Sub

The following outputs into the range D1:D3 the first three items of a list — month names, starting with January.

Sequences. Months

Sub Progr7()
    Range("D1").Value = "January"
    Range("D1").AutoFill Destination:=Range("D1:D3"), Type:=xlFillSeries
End Sub

The following instructions copy the contents of cell E1 into all cells of the range E1:E3.

Sequences. Copying

Sub Progr8()
    Range("E1").Value = "January"
    Range("E1").AutoFill Destination:=Range("E1:E3"), Type:=xlCopy
End Sub