Data input and output using vba worksheets. Message Box in VBA Excel - MsgBox

In VBA, input and output of information (for user interaction) can be done in dialog boxes. The dialog box for entering values ​​is implemented by the built-in function InputBox. The input window, implemented by the InputBox function, displays a variable value entry field in which the user must enter a specific value. Next, the user must click OK.

Function InputBox() has the following syntax:

VariableName = InputBox(Prompt, , , , , , )

Where the arguments are: Prompt or Message - a required argument that specifies an informational message in the dialog box. All other arguments are optional. Title specifies the title of the window. Figure 1 shows a module in which the InputBox function is used.

Rice. 1.

After completing module 2, the message window “Entering variable values” appears (Fig. 2), in which you need to enter a number and click OK. The dialog window (Fig. 2), implemented by the InputBox function (Fig. 1), displays: Window title - Entering variable values; Message - Enter a number; Buttons (default) - OK and Cancel; A field intended for entering variable values.

Rice. 2.

To display information, message dialog boxes implemented by the operator are used MsgBox or function MsgBox(). MsgBox can be used as an operator. The MsgBox statement displays information in a dialog box and sets the mode to wait for the user to press a button.

The MsgBox operator has the following syntax:
MsgBox Prompt, , , ,

Where the arguments are: Prompt or Message - a required argument that specifies the information message to be displayed in the window. All other arguments are optional. Buttons - Buttons that can be used in the message dialog box. Various buttons can be used in the message window (OK, Cancel, etc.). If you do not specify which buttons to display in the message box, the OK button is displayed by default. In addition, you can use different icons in message output dialog boxes (vbQuestion - question mark icon, vbExclamation - exclamation mark icon, etc.).

A module in which MsgBox is used as an operator is shown in Fig. 3 (operator MsgBox "3", vbOKCancel, "Output values").

Rice. 3

When module 4 is launched for execution, the “Output values” message window is displayed (Fig. 4), in which you must click the OK button. The window (Fig. 4), implemented by the MsgBox operator (Fig. 3), displays: Window title - Displaying values; Message - 3; Buttons - OK and Cancel.

Rice. 4

For example, to evaluate a function like y = 5 x 2 + 7 x + 9, you can use the InputBox function and the MsgBox operator (Fig. 5)

Rice. 5

After completing module 5, an input window is displayed

Rice. 6

After you enter a number, such as 789, and click OK, a message box appears showing the result of the function y = 5 x 2 + 7 x + 9.

Rice. 7

MsgBox can be used as a function. The MsgBox() function has the following syntax: MsgBox(Prompt, , , , ). In this case, several different buttons are used in the dialog window. When a button is clicked in a dialog box, the MsgBox() function returns an Integer value that depends on which button was clicked in the message dialog box.

In this article, you will learn how to create a message box in VBA Excel that can display various information.

Function MsgBox displays a message box and waits for the user to click a button and then the action will be performed based on the button clicked by the user.

Syntax

MsgBox(prompt[,buttons][,title][,helpfile,context])

Parameter Description

• Request is a required parameter. A string that appears as a message in a dialog box. The maximum invitation length is approximately 1024 characters. If a message extends over more than one line, then the lines can be separated by using a carriage return (Chr(13)) or line feed (Chr(10)) character between each line.
• Buttons are an optional parameter. A numeric expression that specifies the type of buttons to display, the icon style to use, the default button ID, and the message box modality. If left blank, the default value for buttons is 0.
• Title is an optional parameter. The string expression appears in the title bar of the dialog box. If the title is left blank, the application name is placed in the title bar.
• Help file is an optional parameter. A String expression that identifies a help file that will be used to provide context-sensitive help for the dialog box.
• Context is an optional parameter. A numeric expression that identifies the help context number assigned to the help author to the corresponding help topic. If context is provided, the help file must also be provided.

The Buttons parameter can take any of the following values:

• 0 vbOKOnly - displays only the OK button.
• 1 vbOKCancel - displays the OK and Cancel buttons.
• 2 vbAbortRetryIgnore - Displays the cancel, retry and ignore buttons.
• 3 vbYesNoCancel - Displays the Yes, No and Cancel buttons.
• 4 vbYesNo - displays the Yes and No buttons.
• 5 vbRetryCancel - displays retry and cancel buttons.
• 16 vbCritical - displays the critical message icon.
• 32 vbQuestion - Displays a warning icon.
• 48 vbExclamation - Displays a warning message icon.
• 64 vbInformation. Displays an information message icon.
• 0 vbDefaultButton1 - the first default button.
• 256 vbDefaultButton2 - the second default button.
• 512 vbDefaultButton3 - The third default button.
• 768 vbDefaultButton4 - The fourth default button.
• 0 vbApplicationModal Application modal - the current application will not work until the user responds to the message.
• 4096 vbSystemModal System modal - all applications will not work until the user responds to the message.

The above values ​​are logically divided into four groups: The first group (0 to 5) indicates the buttons that will be displayed in the message box. The second group (16, 32, 48, 64) describes the style of the icon that will be displayed, the third group (0, 256, 512, 768) indicates which button should be the default, and the fourth group (0, 4096) defines the modality of the message window.

Return values

The MsgBox function can return one of the following values, which can be used to identify the button that the user clicked in the message box.

• 1 - vbOK - OK button pressed.
• 2 - vbCancel - Cancel button pressed
• 3 - vbAbort - Abort button pressed
• 4 - vbRetry - Retry button pressed
• 5 - vbIgnore - Ignore button pressed
• 6 - vbYes - the “Yes” button is pressed
• 7 - vbNo - No click

Sub MessageBox_Demo() "Simple message MsgBox ("Hello") "Message with title and buttons yes, no and cancel a = MsgBox("Do you like the color blue", 3, "Choose an answer") "Code of the pressed button MsgBox ("Value answer " & a) End Sub

MsgBox step by step

Step 1 - The above function can be performed either by clicking the Run button in the VBA window or by clicking on a button that you yourself have added to the Excel sheet.

Step 2 - A simple text box is displayed with a "Welcome" message and an "OK" button

Conditional statement in VBA.

It is often necessary for a part of a program to be executed only if certain conditions are met. The solution to this problem is to use special constructs that use branch operators.

General view of this design:

If<логическое выражение>Then<список операторов>End If

<логическое выражение>- is a simple or complex condition, or a logical constant (true or folse)

A complex condition consists of simple conditions connected by the logical operators AND or OR

For example: (a =b)

Algorithm

2)If the value of the logical expression is true, then the list of operators is executed

3) If the value of the logical expression is folse, then nothing is executed

Additional branch of conditional jump:

If<логическое выражение>Then

<список операторов1>

<список операторов2>

Algorithm

1) The value of a logical expression is calculated

2)If the value of the logical expression is true, then the list of operators 1 is executed

3)If the value of the logical expression is folse, then the list of operators 2 is executed

Several nested statements:

If<логическое выражение1>Then

<список операторов1>

ElseIf<логическое выражение2>Then

<список операторов2>

ElseIf<логическое выражениеN>Then

<список операторовN>

Algorithm

1)The value of logical expression 1 is calculated

2)If the value of logical expression 1 is true, then the list of operators 1 is executed

3)If the value of logical expression 1 is folse, then the list of operators 2 is executed

4)If the value of logical expression 2 is true, then the list of statements 2 is executed

5)If the value of logical expression 2 is folse, then the list of statements 3 is executed

4)If the value of the logical expression N is true, then the list of operators N is executed

5) If the value of the logical expression N is folse, then nothing is executed

a = inputbox("enter A")

Cells – a property that allows you to access specific cells of a worksheet.

Cells(i,j) – cell and its coordinates – row number and column number.

For example:

Cells(1,1) – access to cell A1

To place a value or formula in a cell:

Cells(2,2)=2 – place the value 2 in cell B2.

Cells(2,2).Value =2 Place the value 2 in cell B2.

Cells(3,1) = a+b - in cell C1 place the formula for the sum of numbers a and b.

Cells(3,2) = cells(1,1).value + cells(1,2).value – in cell C2 place the formula for the sum of the values ​​from cells A1 and A2.

Cells(i,j) = InputBox(“”) – the value that we enter in the inputbox will be placed in the cell.

Cells(i,j).Select – select a specific cell

Cells.Select – select all cells on the worksheet.

Cells(i,j).Activate – make the cell active.

To display the value in a cell on the screen:

Cells(1,1) =3 – place the value 3 in cell A1.

Msgbox(cells(1,1)) – the value from the cell is displayed on the screen.

Cells(1,1) = k+2 – in cell A1 we place the formula k+2, where k is the given number

Msgbox(cells(1,1)) – the result of the calculated formula stored in cell A1 is displayed on the screen.

MsgBox() - Displays a Message Box and returns a value depending on which button the user pressed. Can be used to display calculation results on the screen in the form of a message.

MsgBox (prompt, buttons, title, helpfile, context)

MsgBox (tooltip, buttons, title, help file, context)

prompt – a message displayed on the screen.

buttons – a constant that determines which buttons will be contained in this dialog box.

title (title) – the title of the dialog box.

helpfile – the name of the help file.

context – the context for context-sensitive help, described if there is a helpfile.

InputBox - function for inputting source data. Displays a dialog box with a message and a field for the user to enter text.

In this article you will learn how to create an input field in VBA Excel - InputBox, into which you can enter various information for calculations.

Function InputBox prompts users to enter values. After entering values, if the user clicks the OK button or presses ENTER on the keyboard, the InputBox function returns the text to the text box. If the user clicks the Cancel button, the function will return an empty string ("").

Syntax

InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile,context])

Parameter Description

• Request - required parameter. A string that appears as a message in a dialog box. The maximum invitation length is approximately 1024 characters. If a message extends over more than one line, then the lines can be separated by using a carriage return (Chr(13)) or line feed (Chr(10)) character between each line.
• Title is an optional parameter. The string expression appears in the title bar of the dialog box. If the title is left blank, the application name is placed in the title bar.
• Default is an optional parameter. The default text in the text field that the user would like to display.
• XPos is an optional parameter. The X-axis position is the approximate horizontal distance from the left side of the screen. If you leave the field blank, the input field will be positioned horizontally.
• YPos is an optional parameter. The Y-axis position is the approximate vertical distance from the left side of the screen. If left blank, the input field will be vertically centered.
• Help file is an optional parameter. A string expression that identifies a help file that will be used to provide context-sensitive help for the dialog box.
• context - Optional parameter. A numeric expression that identifies the help context number assigned to the help author to the corresponding help topic. If context is provided, the help file must also be provided.

example

Let's calculate the area of ​​the rectangle by getting values ​​from the user at runtime using two input fields (one for length and one for width).

Function findArea() Dim Length As Double Dim Width As Double Length = InputBox("Enter length", "Enter number") Width = InputBox("Enter width", "Enter number") findArea = Length * Width End Function

InputBox step by step

Step 1 − To do the same, call using the function name and press Enter as shown in the following screenshot.

Step 2 - Once executed, the first input field (length) is displayed. Enter a value in the input field.

Like many programming languages, Visual Basic for Application (VBA) allows you to create three types of procedures: Sub, Function, Property.

A procedure is a set of descriptions and instructions grouped together for execution.

Procedure Sub– a set of commands that can be used to solve a specific problem. When it runs, the procedure's commands are executed, and then control is transferred to the application or procedure that called the Sub procedure. Recorded macros are automatically described as Sub procedures, any macro or other VBA code that simply performs a specific set of actions using Office applications, and is typically a Sub procedure.

Procedure Function(or function) is also a set of commands that solves a specific problem. The difference is that procedures of this type must return a value. When you create a Function procedure, you can describe the data type that the function returns. Functions are typically used to perform calculations, operate on text, or return Boolean values.

Procedure Property used to refer to a property of an object. This type of procedure is used to set or get the value of custom properties of forms and modules. Procedures make it easier to store and use information if you use them to first store that information in a property and then read it.

Procedure structure

When recording a procedure, you must follow the rules for describing it. The simplified syntax for Sub procedures is as follows:

Sub name ([arguments]) End Sub instructions

The syntax for describing functions is very similar to the syntax for describing a Sub procedure, however, there are some differences:

Function name ([args]) [As Type] Instructions name = expression End Function

Using Operators

Procedures consist of statements - the smallest units of program code. Typically, statements occupy one line of code, and each line usually contains only one statement, but this is not necessary. There are four types of statements in VBA: declarations, assignment statements, executable statements, and compiler options.

ads

A declaration is a statement that tells the VBA compiler that you intend to use a named object (variable, constant, user-defined data type, or procedure) in your program. In addition, the declaration specifies the type of the object and provides the compiler with additional information about how to use the object. Once you declare an object, you can use it anywhere in the program.

Variables are named values ​​that can change during program execution.

Let's look at an example of a variable declaration.

The Dim operator declares a variable with the name My favourite number and declares that the value it will contain must be an integer:

Dim My Favorite Number As Integer

Constants are named values ​​that do not change.

The Constant operator creates a string constant (text) named ImmutableText, which is a set of characters Eternity:

Constant ImmutableText = "Eternity"

The Type operator declares a custom data type with the name Samodelkin, defining it as a structure containing a string variable named Name and a variable like Date With name Birthday. In this case, the declaration will take several lines:

Type Homemade Name As String Birthday As Date End Type

The Private declaration creates a procedure of type Sub named HiddenProcedure, indicating that this procedure is local in the sense of scope. The End Sub statement that ends the procedure is considered part of the declaration.

Private Sub HiddenProcedure() instructions End Sub

Assignment operator

The assignment operator = assigns specific values ​​to variables or properties of objects. Such an operator always consists of three parts: the name of the variable or property, the equal sign and an expression that specifies the desired value.

The = operator assigns to a variable My favourite number variable sum value OtherNumber and numbers 12 .

My Favorite Number = Other Number + 12

The next line of code states that the property Color object AGraphicShape value is assigned Blue on the assumption that Blue is a named constant:

AGraphicShape.Color = Blue

In the next line to set the value of the variable Square root, for the current value of the variable My favourite number function is called Sqr- built-in VBA square root function:

SquareRoot = Sqr(MyFavoriteNumber)

In VBA, an expression is any piece of program code that specifies a numeric value, a string of text, or an object. An expression can contain any combination of numbers or symbols, constants, variables, object properties, built-in functions, and Function procedures linked together by operator signs (for example, + or *). Some example expressions:

Executed statements

Executable statements do the main work in the program and are used to perform the following tasks:

• procedure call;
• activating a method of some object;
• controlling the order in which other statements should be executed by organizing loops or selecting a piece of program code (from several alternatives) for subsequent execution;
• Execute one of VBA's built-in statements or functions.

Example. A statement that calls the Rotate method of the AGraphicShape object to execute:

AGraphicShape. Rotate(90 )

Recording multiple statements

Typically, each statement takes up one line of code, but VBA does not require you to fit the statement on one line. If the statement is too long, you can place it on two or more lines by adding an underscore (_) to the end of each line (except the last one).

You can do the opposite - place several statements in one line of program code. For example,

Dim A As Integer, B As Integer: A = 3 : B = 5 : A = A +B

This line of code is equivalent to the following four lines:

Dim A As Integer , B As Integer A = 3 B = 5 A = A + B

The simplest dialog boxes are message boxes - these are dialog boxes that present messages to the user and are equipped with one or more selection buttons. In VBA they are created using the MsgBox function.

In its simplest form, MsgBox is used as an operator with one argument - the message to be displayed. For example, the macro below creates the message shown in the figure.

Sub Program() MsgBox "This is a message box" End Sub

MsgBox can be used to display a numeric value.

Sub ShoeValue() Amount = 10 MsgBox Amount End Sub

Variable Amount is assigned the value 10. On the next line to display the value Amount MsgBox is used. Around Amount there are no quotes because it is the value of the variable that needs to be printed to the screen, not the word "Amount".

To use two separate strings together in one message box, you must use the concatenation (&) operator.

Sub SayGoodNight() Name = "Sasha" MsgBox "Say Goodnight"& Name End Sub

Variable Name the string "Sasha" is assigned. The line of code with MsgBox specifies the text string "Say good night" followed by & Name, telling MsgBox to append the value of the variable Name to the previous text line.

MsgBox Options

optional arguments, for example, to insert an icon or change the title.

MsgBox "This is a wonderful message box",_vbExclamation, "Personal window"

There are four icons for message boxes. Each has a specific numeric value that must be passed as an argument to MsgBox. However, instead of a number, you can use constants with special names built into VBA.

Table 1

MsgBox Message Box Icons

Display	Constant	When to use
		for a message that does not require a reply
		to ask a question
		to provide important information
		for warning

MsgBox as a function

MsgBox is a function and can return a value corresponding to the button that the user presses. One of the options available for a message box is to change the buttons it displays. Here is the message box that appears when you exit Excel with unsaved changes to the document. This window has three buttons.

After selecting the appropriate button, Excel receives information about which button was selected.

The general format for the MsgBox function is:

MsgBox(prompt [ , buttons] [ , title] )

Where prompt is the only required argument. For the message window, you should specify a text string with information. if you want to change the title that appears at the top of the window, set the title to ( title) text string. The default title is Microsoft Excel.

Table 2 MsgBox button combinations

Display	Constant	When to use
	vbOKOnly	When the user is not required to make a decision
	vbOKCancel	When a message box explains a possible action. Allows the user to make a selection using a button Cancel
	vbYesNo	Alternative to constant vbOKCancel when it seems like it would make the message box clearer
	vbYesNoCancel	For situations such as exiting or closing files without saving (similar to the situation shown in the image above)
	vbAbortRetryIgnore
	vbRetryCancel	When responding to disk or file error messages

If you do not specify which buttons to display in the message box, the default value corresponding to the button is used Ok .

Examples of problem solving

Here are some examples of solving problems in VBA.

Example 1: Calculate the value of an expression a equal

At x = 3, y = 2.5

Solution.

Sub expression1 () Dim A, x, y x = 3 y = 2.5 A = 2 * x - 3 * y MsgBox (A) End Sub

Explanation of the solution.

The line Dim A, x, y declares variables A, x, y.

Example 2.

Example 2: Calculate the value of an expression a equal

At x = 3, y = 2.5

Note: values x And y entered by the user.

Solution.

Sub expression2 () Dim A, x, y As Double x = InputBox("Enter x=" ) y = InputBox("Enter y=" ) A = 2 * x - 3 * y MsgBox (A) End Sub

Explanation of the solution.

The line Dim A, x, y As Double describes the variables A, x, y as double precision numbers.

When using the string

X = InputBox("Enter x=" )

a window will appear

Example 3

Example 3: Calculate the value of an expression a equal

At x = 3, y = 2.5

Note: values x And y the user enters, the answer is displayed as “a =<значение>».

Solution.

Sub expression3 () Dim A, x, y As Double Dim answer As String x = InputBox("Enter x=" ) y = InputBox("Enter y=" ) A = 2 * x - 3 * y answer = "a= " + Str(A) MsgBox (answer) End Sub

Explanation of the solution.

The Dim response As String describes the variable answer like a string.

The code Str(A) converts the value of a variable A per line.

Example 4

Example 4. Calculate the values ​​of expressions when x = 3, y = 2.5

Solution.

Sub expression4 () Dim A, b, c, d, a1, x, y As Double x = InputBox("Enter x=" ) y = InputBox("Enter y=" ) A = 2 * x - 3 * y b = (2 * x - 3 * y) / 2 c = (2 * x - 3 * y) / 2 * x d = (2 * x - 3 * y) / (2 * x) a1 = (2 * x - 3 * y) / (2 * x) + (5 - x) / (3 + y) MsgBox ("a=" + Str(A) ) MsgBox ("b=" + Str(b) ) MsgBox ("c= " + Str(c) ) MsgBox ("d=" + Str(d) ) MsgBox ("a1=" + Str(a1) ) End Sub

Example 5

Example 5. Execute example 4, in a different way, using auxiliary variables.

Solution.

Sub expression5 () Dim A, b, c, d, a1, a2, b1, c1, c2, x, y As Double x = InputBox("Enter x=" ) y = InputBox("Enter y=" ) A = 2 * x - 3 * y b = (2 * x - 3 * y) / 2 c = (2 * x - 3 * y) / 2 * x d = (2 * x - 3 * y) / (2 * x) a1 = (2 * x - 3 * y) / (2 * x) + (5 - x) / (3 + y) ' new solution b1 = A / 2 c1 = b * x c2 = b / (2 * x ) a2 = d + (5 - x) / (3 + y) MsgBox ("a=" + Str(A) ) MsgBox ("b=" + Str(b) ) MsgBox ("c=" + Str(c ) ) MsgBox ("d=" + Str(d) ) MsgBox ("a1=" + Str(a1) ) MsgBox ("b1=" + Str(b1) ) MsgBox ("c1=" + Str(c1) ) MsgBox ("c2=" + Str(c2) ) MsgBox ("a2=" + Str(a2) ) End Sub

Example 6

Example 6. Calculate the area of ​​a triangle based on three known sides. For example, a = 3 , b = 4 , c = 5 .

Solution.

Sub Heron1 () Dim A, b, c, p, s As Double A = 3 b = 4 c = 5 p = (A + b + c) / 2 s = Sqr(p * (p - A) * (p - b) * (p - c) ) MsgBox ("s=" + Str(s) ) End Sub

Explanation of the solution.

To solve the problem, Heron's formula is used.

Example 7

Example 7. Calculate the area of ​​a triangle based on three known sides.

Solution.

Sub Heron2 () Dim A, b, c, p, s As Double A = Val(InputBox("Enter a=" ) ) b = Val(InputBox("Enter b=" ) ) c = Val(InputBox("Enter c=" ) ) p = (A + b + c) / 2 s = Sqr(p * (p - A) * (p - b) * (p - c) ) MsgBox ("s=" + Str(s ) ) End Sub

Explanation of the solution.

The code Val(InputBox("Enter a=")) converts the value entered through the InputBox to a number, since the InputBox returns a string. If such a transformation is not made, then the program will correctly calculate s will not be.

Example 8

Example 8. Calculate the hypotenuse of a right triangle using two legs.

Solution.

Sub hypotenuse() Dim a, b, c, p, s As Double a = Val(InputBox("Enter a=" ) ) b = Val(InputBox("Enter b=" ) ) c = Sqr(a ^ 2 + b ^ 2 ) MsgBox ("c=" + Str(c) ) End Sub