This part will go through some practical ways to share the information to the coder or user without using the worksheet.
There are 3 ways common ways in order of popularity.
Message boxes are the most common ways to share some information from the coder to the user or coder himself/herself. It is exactly what it sounds like. A small window pop up displaying some information and halting the code from being run at the same time. Typical placement could be after the script has finished to let the user know he/she can proceed. We have a few variants of message boxes and style for them that could be useful to display for example the urgency of a script, or catching some user input on something.
The following is the set up for a message box where the hard brackets are optional customization: Personally I have only used Prompt, Buttons, and Title argument as I believe those are the needed ones. Helpfile and context creates a popup of the help functions and context to a specific place. MsgBox( prompt [, buttons ] [, title ] [, helpfile, context ] )
Prompt: "This is your text with these annoying quotation marks" Buttons: This argument allows you to customize the style of the box as well as what buttons it should have to catch any user input needed for the script. Ok button is always default if this argument is omitted. Note that a Style and a Button option can be paired up together for the messagebox with a simple plus sign '+'.
vbOK = OK button vbCancel = Cancel button vbAbort = Abort button vbRetry = Retry button vbIgnore = Ignore button vbYes = Yes button vbNo = No button
Message style options
vbCritical = Critical message icon vbQuestion = Question icon vbExclamation = Waning message icon vbInformation = Information icon
This argument allows you to customize the title of the message box. If omitted it will just show the standard “Microsoft Excel”.
Standard Message box
Sub Standard_msg() MsgBox "Hello Analyst!" End Sub
Ok + Cancel Message box
Sub Cancel_msg() 'Shows Ok and cancel. UserInput = MsgBox("Do you want to run the code?", vbOKCancel) If UserInput = vbCancel Then Debug.Print "Don't run" Else Debug.Print "run code" End If End Sub
Ok + Retry Message box
Sub Retry_msg() 'Shows Ok and Retry. UserInput = MsgBox("Do you want to run the code?", vbRetryCancel) If UserInput = vbRetry Then Debug.Print "Don't run" Else Debug.Print "run code" End If End Sub
Information style Message box with Title
Sub Info_types() 'Title is Your Boss 'Information symbol is being shown on the message box. MsgBox "Please analyze faster", vbInformation, "Your Boss" End Sub
Critical style, YesNo Message box with Title
Sub Critical_types() 'Just add '+' sign to configure YesNo as well as the style of message box to be displayed. MsgBox "The CFA needs to be on my desk by 8am", vbCritical + vbYesNo, "Your Boss" End Sub
Exclamation style Message box with Title
Sub Exclamation_types() MsgBox "Get me coffe", vbExclamation, "Your Boss" End Sub
Question style Message box with Title
Sub Question_types() MsgBox "What WACC did you use?", vbQuestion, "Your Boss" End Sub
Question YesNo style Message box with Title
Sub MsgBoxInformationIcon() 'Display a title do your msbox. MsgBox "Do you want to continue?", vbYesNo + vbQuestion, "Step 1 of 3" End Sub
The Immediate window is a popular way for the coder to see so that the code runs as it should. Whilst a message box often serves this function as well, using the immediate window is a lot more comfortable when displaying a lot more information. Not having to press the Enter key to get rid of the message box when your code is in a loop of 100 times to print out an array or testing an IF statement.. (yeahh we've all been there. don't worry).
Input boxes are a great way to allow the user to input values or text to customize the code run depending on the input. Alternatively you could dedicate specific cells in your worksheet that are being picked up during the code run to allow the end user to have some input in what is being run. Utilizing input boxes however, makes your code less dependent of specific cells and thus more stable for manipulation.
InputBoxes can be used via the function InputBox() or via the Application.InputBox. InputBox Function: Text input from the User. Application Inputbox: Here you can specify the type of required input that is necessary in terms of variables (string, number, range etc. InputBox Function example:
Sub Inputs() 'Input from User that is being stored in MyInput variable MyInput = InputBox("Please Input your WACC as a percentage", "Wacc Simulation", 5) 'Prints out the answer from the user to the immediate window. Debug.Print MyInput End Sub
Application Inputbox example: The below specifies what to of input the sought after.
0 = A Formula 1 = A Number 2 = Text (a string) 4 = A logical value (True or False) 8 = A cell reference, as a Range object 16 = An error value, such as #N/A 64 = An array of values
Sub InputBoxRange() Set myCell = Application.InputBox(prompt:="Select a cell", Type:=8) End Sub
Sub InputBoxNr() Dim MyInput As Double MyInput = Application.InputBox(prompt:="Enter a number", Type:=1) 'Prints out the result to the immediate window. Debug.Print MyInput End Sub
Sub InputBoxText() 'Note that this converts everything to a text meaning characters and decimals comes in as text. 'Need to create a loop for only textcharacters Dim MyInput As String MyInput = Application.InputBox(prompt:="Enter a text", Type:=2) Debug.Print MyInput End Sub
If you only need a textinput the Inputboxfunction works just fine. If you need something more refined it could be good to use the Applicaiton.Inputbox function. However, test your code with your inputs to make sure it is bulletproof. For example if you are using the type Boolean, note that all text converts to a True value. Not maybe what you are after. Hence, unless requiring for example a Number or Range which are great functions, confirm that the user input the information as you wish with for example a loop that validates the answer. I set up something quick to illustrate below, however, you will probably use a while loop instead.
Sub InputBoxNrLoop() For i = 1 To 10 'Assigns the input value to MyInput MyInput = Application.InputBox(prompt:="Enter a number") 'Testing the input variable "MyInput" to make sure it is of numeric type. 'If the supplier value is numeric, the immediate window will print it out. 'Otherwise a "Canceled" text is printed out instead. 'The syntax Exit For is a great way to exit a for loop rather than using errorhandling measures. If IsNumeric(MyInput) Then Debug.Print MyInput Exit For 'exits the loop ElseIf MyInput = "False" Then Debug.Print "Canceled" Exit For 'exits the loop End If Next i End Sub