#msgbox

Join 400000+ professionals in our courses here Dive into the world of Excel VBA with … source

今回はファイル名が被ったときに名前の入力ができるようにしました。

ちょっと動画をここにのせるのがうまくいかなかったので、もしよろしければTwitterをどうぞ→https://twitter.com/Kotabrog

ということで、コードはかなり長くなっています。

   If fso.FileExists(file_fullname) Then        result = MsgBox( _            "同じ年月のバックアップファイルがあります。上書きしますか？", _            Buttons:=vbYesNo)        If result = vbYes Then            flag = True        Else            result = MsgBox( _                "ファイル名を変更して保存しますか？", _                Buttons:=vbYesNo)            If result = vbYes Then                file_name = InputBox("ファイル名を入力してください", _                    , file_name)                If file_name = "" Then Exit Sub                file_fullname = folder_path & "/" & file_name

               Do While Not flag2                    If fso.FileExists(file_fullname) Then                        result = MsgBox( _                            "同じファイル名のファイルがあります。上書きしますか？", _                            Buttons:=vbYesNo)                        If result = vbYes Then                            flag = True                            flag2 = True                        Else                            result = MsgBox( _                                "ファイル名を変更しますか？", _                                Buttons:=vbYesNo)                            If result = vbYes Then                                file_name = InputBox("ファイル名を入力してください", _                                    , file_name)                                If file_name = "" Then Exit Sub                                file_fullname = folder_path & "/" & file_name                            Else                                Exit Sub                            End If                        End If                    Else                        flag2 = True                    End If                Loop

           Else                Exit Sub            End If        End If    End If

途中から受け答えによってはループするようになっています。

まぁ動画を見れば何をしたかすぐわかると思うので、ぜひ。

あっ、ちなみにInputBoxを使っていて、もしキャンセルを押すと「””」が返ってくることなどを利用しています。

売上表にファイルのバックアップをとる機能がありましたが、このときすでにファイルがあると、「上書きしますか？」という問いかけがでてきます。

もしここで「No」を選ぶとマクロが止まって、VBEが開かれてしまい、マクロを使った人は「なんだこの画面は……」と困惑してしまうかもしれません。

それを防ぐために、あらかじめMsgBoxを出して、上書きするかどうか聞いておき、それに応じて処理を変えるようにします。

それではコードです。

   If fso.FileExists(file_fullname) Then        result = MsgBox( _            "同じ年月のバックアップファイルがあります。上書きしますか？", _            Buttons:=vbYesNo)        If result = vbYes Then            flag = True        Else            Exit Sub        End If    End If

まずはファイルが存在するかどうかを確かめ、存在したらMsgBoxを出します。

今日は前回の下準備を経て、いよいよコードを実行します（といってもとてつもなく基本的なものですが）。

現在の状態

msgbox "Hello VBA"

とEnterを入力

msgboxがMsgBoxになります。

ここで次のボタンを押すと…

このようにメッセージボックスが出ます。

マクロを手動で実行する方法は主に３種類。

いずれも実行したいコードの中に入力カーソルがある状態にします。

ツールバーのボタンを押す（上記）

メニューバーから（下の画像）

F5キー

ちなみに…

コードを小文字で書いておくと、正しくコードが書かれていれば勝手に大文字にしてくれます。逆に言えば、間違っていると大文字にならないので、間違いに気づけます。

また、MsgBoxはメッセージボックスを表示してくれる関数です。

New Post has been published on http://analystcave.com/vba-status-bar-progress-bar-sounds-emails-alerts-vba/

New Post has been published on http://analystcave.com/excel-vba-tutorial/

Excel VBA Tutorial

Welcome to the ultimate VBA Tutorial (Visual Basic for Applications)! You will walk the journey from being an VBA Zero to becoming an VBA Hero. This VBA tutorial is not aimed only at teaching you how to write VBA macros in Excel – it’s a general introduction to VBA. However, some sections as you will notice are indeed aimed at Excel (e.g. the Workbooks and Worksheets section). Click on the images below to skip to the various steps of this tutorial.

VBA Basics

Basic VBA syntax, loops, variables. Excel VBA basics like working with Worksheet/Workbooks/Cells and Ranges etc.

Hello World!

Recording Macros

Debugging VBA

Procedures vs. Functions

Variables

Conditions (If-Else, Select)

Loops

Ranges & Cells

Worksheets & Workbooks

Formatting

MessageBox & UserForms

Objects

Classes

Arrays, Dictionaries, other structures

How to open the VBA Editor?

The Developer tab in the Excel ribbon is required to work with Excel VBA. Apart from enabling you to access your VBA Project, it also contains other useful features and shortcuts which we will dive into later e.g. Macro recording, Form Controls (buttons, checkboxes etc.).

STEP 1Add the Developer tab to your Excel ribbon

To open the options window go to: File->Options->Customize Ribbon Next select the Developer tab to add it to the Excel ribbon as shown below:

Select the “Developer” tab to add it to the Excel ribbon

Click Ok. You should now see a new tab in your Excel ribbon called Developer as shown below:

The Developer tab in the Excel ribbon

STEP 2Click the Visual Basic button on the Develop ribbon

Click the button as shown below:

Click the Visual Basic button

This will open the Visual Basic editor as shown below:

The VBA editor window

Congratulations! Now you can begin your journey with Excel VBA!

STEP 3Add a new module for your VBA code

To add a new module click on any item (Worbook/Worksheet/Module etc.) in your VBA project Project-VBAProject panel and select: Insert->Module as shown below:

Add VBA module to your project

Great! You have are now ready to add VBA to your Excel VBA project! Go to the next step of this tutorial.

Hello World!

In this step you will learn how to create a simple VBA procedure to open a MessageBox with a “Hello World!” message.

STEP 1Insert the code below to any module (or Worksheet)

As mentioned copy the code below to your module (or Worksheet):

Sub HelloWorld() Call MsgBox("Hello World") End Sub

STEP 2Hit the F5 button or Run Sub/UserForm (F5) button

To run the procedure hit the F5 button or click the Run Sub/UserForm (F5) button to run the procedure:

” button to run the procedure” class /> The menu ribbon – hit F5 or the “>” button to run the procedure

Congratulations! You should see the following pop-up MessageBox with your message:

Your first VBA procedure: Hello World!

STEP 3Understand the code

Let pause for a second to understand the code. The code that is actually executed is the MsgBox command as highlighted below. This command executes and shows the MessageBox.

Sub HelloWorld() Call MsgBox("Hello World") End Sub

Now notice that the MessageBox command is wrapped in a procedure Sub and End Sub clause. You will learn more about procedures in the Procedures vs. Functions section of this Excel VBA Tutorial. For now it is enough you understand that any code you develop that can be executed needs to be wrapped in a Sub and End Sub clause.

Sub HelloWorld() Call MsgBox("Hello World") End Sub

STEP 4Add a comment to your code

Comments are one of many important things you need to learn. Comments are preceded with a ‘ apostrophe. Comments are not compiled nor executed and are meant just to describe or explain your code. See below an example comment:

Sub HelloWorld() 'This is a comment! Hello! Call MsgBox("Hello World") End Sub

Now proceed to learning how you can boost your journey with Excel VBA by recording macros instead of creating them from scratch.

Recording Macros

The best way to learn Excel VBA is record macros. This is especially useful if you want to automate mundane Excel tasks e.g. formatting selected cells etc. In this case you can record you series of steps/clicks/typed text etc. and Excel will translate that into VBA code creating a new module into your VBA project.

STEP 1Go to your Developer tab and click Record Macro

To start recording go to the Developer tab:

Click the Record Macro button

Now hit the Record Macro button to start recording:

STEP 2Complete the Record Macro Form and click OK

Now you will see the following Form appear. Provide a Name for your recorded procedure (no spaces). You can also associate a Excel Shortcut with your Recorded Macro. To do that input a letter or number in the Shortcut Key textbox e.g. type “T” which should generate the following shortcut: CTRL+SHIFT+T Next hit OK.

Record Macro Form

STEP 3Do something in Excel that you would like to record

Now almost every mouseclick and keyboard press will be recorded and translated to an Excel VBA macro. For this tutorial I would recommend testing a typical useful scenario:

Click on a single cell

Change the background color

Change the font to Bold / Italic etc.

STEP 4Stop recording the Macro

Hit the Stop Recording button to stop recording the macro:

Stop Recording VBA Macro

STEP 5Execute the recorded macro

Assuming the recorded macro can now be reused, let’s try executing it. You can execute the macro in 3 ways:

Use the shortcut CTRL+SHIFT+T

Go to the Developer tab on the Excel ribbon and click Macros:

Click on Macros

Next select your Macro and hit Run.

Execute Excel Macro

Go to your VBA project, click on the Macro procedure and hit F5

STEP 6View the generated code recorded by the Record Macro button

Let’s now open up our VBA Project and take a look into the recorded macro. Open your VBA Project by leveraging the Visual Basic button on the Developer tab.

Click on Visual Basic

Now look for a new module in your VBA Project and click on that item:

Click on the newly created module

The code generated by the Record Macro button should look similarly as below:

Sub Macro1() ' ' Macro1 Macro ' ' Keyboard Shortcut: Ctrl+Shift+T ' Selection.Font.Bold = True Selection.Font.Italic = True Selection.Font.Underline = xlUnderlineStyleSingle With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With End Sub

Debugging VBA

First we need to introduce some basic terms to facilitate the remaining part of this post:

Executing/Running code – the process of running a macro

Debugging code– the process of finding and fixing bugs/defects

Breakpoint – a line of code at which the execution of the macro will pause

Running / Breaking / Reseting

Let’s start with the tool bar at the top of the VBA Project Viewer window. You should find 3 buttons as shown below: The buttons allow you to do the following:

Run – run your macro (Sub) or UserForm. This is equivalent to the key shortcut F5

Break – pause a running macro CTRL+Break. You can also stop a running macro by hitting the Esc button

Reset – reset a running/paused macro

These are the basic commands for running macros.

Break points

Breakpoints specify lines of code at which the execution of your macro should pause. They are convenient when you want to be sure your code does run through a certain loop of If statement. To add/remove a breakpoint simply left-click on the left gray bar in your VBA Project View next to your code. A red dot should appear indicating that you have specified a new breakpoint. Click on the dot again to remove the breakpoint.

Assertions – the right way to breakpoint errors

Often breakpoints are specified in places where error might occur. This may be cumbersome when you have loop running and are not sure when the error will occur or if you are aware of a condition that causes the error but are unable to catch it at the right moment. This is where you will want to use Debug.Assert.

How does Debug.Assert work? Say you are dividing to numbers and want to make sure the denominator is non-zero. Otherwise you want the code to pause. Consider the example below. In the first example the code will continue to execute normally, in the second example however the macro will immediately pause at the assertion as if a breakpoint was defined!

x = 100 y = 10 Debug.Assert y <> 0 'Condition met: Continue! x = 120 y = 0 Debug.Assert y <> 0 'Condition false!: Pause!

Stepping through code

The key to debugging is to skillfully step through your code either by line or an entire function/procedure. Here are the basic commands found in the Debug menu toolbar:

Step Into F8 – step into each procedure/function

Step Over SHIFT+F8 – step over every procedure/function (run just the current procedure)

Step Out CTRL+SHIFT+F8 – step out of the current running procedure

Run to Cursor CTRL+F8 – execute and break at the line pointed by the cursor

Usually this is enough although you might want to get familiar with the other commands in the Debug menu toolbar.

The Immediate window and Debug.Print

In the bottom left corner of VBA editor you should find the Immediate window. This panel can be used to execute immediately pieces of code (even your code is paused). Simply start typing and hit ENTER! Additionally the Immediate window is the default output of the Debug.Print VBA command which prints a certain provided string (similarly like the MsgBox but does not display any pop-up). The Debug.Print command is very convenient for outputting VBA execution messages / statuses or execution progress (e.g. number of processed items).

Debug.Print "Hello there!"

The output:

The Immediate window

Want to know everything about VBA Debugging? Read my post on: How to debug VBA?

Procedures vs. Functions

Definitions

Let’s start by defining what is a Procedure and a Function:

What is a Procedure? A procedure is a block of VBA statements enclosed by a declaration statement (Function, Sub, Operator, Get, Set) and a matching End declaration. All executable statements in Visual Basic must be within some procedure. Source: Link

What is a Function? A Function procedure is a series of VBA statements enclosed by the Function and End Function statements. The Function procedure performs a task and then returns control to the calling code. When it returns control, it also returns a value to the calling code. Source: Link

As you probably see now Functions are actually a type of Procedure. However, in real life most often you will see the usage of Sub procedures and Function procedures.

What is a Sub procedure? A Sub procedure is a series of VBA statements enclosed by the Sub and End Sub statements. The Sub procedure performs a task and then returns control to the calling code, but it does not return a value to the calling code. Source: Link

Structure of Procedures & Functions

Now let’s look into the structure and differences between the 2 main various types of procedures (Functions and Sub procedures):

Sub procedure

Sub SubProcedure() Call MsgBox("Hello!") End Sub

Sub procedures DO NOT return any values and are used only to execute code.

Function procedure

Function FunctionProcedure() as String Call MsgBox("Hello!") FunctionProcedure = "Hello!" End Function

Function procedures (or just Functions) DO RETURN VALUES as opposed to Sub procedures. I will omit for now Get and Set procedures as they are only used in VBA Classes.

The structure of a Procedures & Function

Structure of VBA Sub procedure

Structure of a VBA Function

Calling Procedures & Functions

First let’s clarify that Function Procedures cannot be executed directly. This is because they are meant to return values. If you want to call a Procedure directly use a Sub Procedure instead of a Function Procedure. Similarly, you cannot execute directly a Sub Procedure with argument. See some useful examples of Calling Procedures & Functions below:

Example 1: How to call a Sub and Function

Function GetMessage() as String GetMessage = "Hello!" End Function Sub ShowMessage() Call MsgBox("Hello!") End Sub Sub Run() Call ShowMessage 'Run the ShowMessage Sub Procedure msg = GetMessage 'Get the result of the GetMessage Function End Sub

Example 2: Calling Procedures

Sub ShowMessage() Call MsgBox("Hello!") End Sub Sub Run() Call ShowMessage ShowMessage 'Same as Call ShowMessage End Sub

Example 3: Calling Procedures with arguments

Sub ShowMessage(msg as String) Call MsgBox(msg) End Sub Sub Run() Call ShowMessage("Hello") 'If "Call" then wrapped in "()" ShowMessage "Hello" 'If no "Call" then no "()" End Sub

Example 4: Calling Procedures & Functions with multiple arguments

Sub ShowMessage(msg1 as String, msg2 as String) Call MsgBox(msg1 & msg2) End Sub Sub Run() 'Output: "Hello World" Call ShowMessage("Hello ","World") ShowMessage "Hello ", "World" End Sub

Variables

First let’s start with a simple example of using a variable:

Dim myVar As Integer myVar = 10

So what is a variable? Is it basically a symbolic name for a storage location, for storing data or pointers to data. Without variables you would be able to process any data. When using variables there are 2 steps you need to complete:

Declare the variable – declare the symbolic variable name (and sometimes data type)

Define the variable – set a value to the variable

Let’s distinguish the two steps:

Dim myVar As Integer 'Declaration myVar = 10 'Definition

VBA usually (w/o the Option Explicit macro) allows you to simply define a new variable with any explicit declaration. This means that this is equally valid:

myVar = 10 'Declaration & Definition

When declaring variables remember precede them with the Dim statement.

Declaring multiple variables You can also use the Dim statement to declare multiple variables in one line.

Dim myVar1 As Integer, myVar2 as String, someDate as Date

Data Types

Below the list of data types available in VBA. For a more detailed list see here (MSDN).

Data Type Bytes Value Range Boolean Depends on implementing platform True or False Byte 1 byte 0 through 255 (unsigned) Date 8 bytes 0:00:00 (midnight) on January 1, 0001 through 11:59:59 PM on December 31, 9999 Double (double-precision floating-point) 8 bytes -1.79769313486231570E+308 through -4.94065645841246544E-324 † for negative values;4.94065645841246544E-324 through 1.79769313486231570E+308 † for positive values Integer 4 bytes -2,147,483,648 through 2,147,483,647 (signed) Long (long integer) 8 bytes -9,223,372,036,854,775,808 through 9,223,372,036,854,775,807 (9.2…E+18 †) (signed) Object 4 bytes on 32-bit platform; 8 bytes on 64-bit platform Any type can be stored in a variable of type Object Single (single-precision floating-point) 4 bytes -3.4028235E+38 through -1.401298E-45 † for negative values;1.401298E-45 through 3.4028235E+38 † for positive values String (variable-length) Depends on implementing platform 0 to approximately 2 billion Unicode characters Type variable A custom data type containing one or more elements (variables). Read more here. Can’t contain Subs or Functions like the Class data type, however, is useful in Binary Writing/Reading files. User-Defined(structure) Depends on implementing platform Each member of the structure has a range determined by its data type and independent of the ranges of the other members

Option Explicit

I personally prefer explicit variable declaration as you will learn this will help reduce any errors resulting from mistyping variables names. Let me give you an example:

myVar = 10 'myVar = 10 res = myVa 'res = 0

As you can see above, I have defined a variable named myVar with value 10. In the second line I defined a new variable res with the value of myVa – mistyping the name of my myVar variable. Now as myVa is a new variable without a defined value, VBA will assign a default null value to this new variable equal to 0.

To prevent such errors use the Option Explicit statement in the first line of you Module as show below:

Option Explicit Sub Run() myVar = 10 'ERROR! myVar not defined! End Sub

Option Explicit Sub Run() Dim myVar As Integer myVar = 10 'OK! End Sub

As you can see adding the Option Explicit statement will generate and error when you use/define a variable that was not previously declared.

Constants

In VBA you can also declare constants – values that can be defined only once and cannot be modified (will throw an exception). This is an example of a constant declaration:

Const myVar As Integer = 10 Dim x as Integer x = myVar + 1 'OK! myVar = 11 'ERROR! Constants cannot be redefined

Private and Public variables

Private A variable that is not accessible only within the scope in which it was declared. Below are some examples of Private variables. In VBA additionally Prviate variables can be declared in ALL scopes except for Subs and Functions (use the Dim statement instead – which is the equivalent).

Private var as String Sub SomeSub() 'var is Private within this module var = "Hello there!" 'These variables are also Private in scope of the Sub Dim subVar as String subVar = "Hello!" End Sub Sub TestSub() SomeSub 'Will show a message "Hello There!" MsgBox var 'Will show an empty MsgBox - as subVar is not available in this scope MsgBox subVar End Sub

Public A variable that is accessible within ALL scopes in contrast to Private variables. Below are some examples of Public variables. In VBA additionally Public variables can be declared in ALL scopes except for Subs and Functions.

'---Module 1--- Public var as String Sub SomeSub() 'var is Public within this module var = "Hello there!" End Sub '---Module 2--- Sub TestSub() SomeSub 'Will show a message "Hello There!" MsgBox var End Sub

Global Global variables are basically equivalent to Public variables. The difference is that Global variables can only be used in modules, whereas Public can be used in all contexts/scopes e.g. modules, classes, forms etc. Global variables are most likely kept for backwards compatibility (older versions of Excel) so I recommend you stick to using only Public variables.

Conditions

Conditions allow you to make a decision based on the evaluation of a boolean expression (logical expression).

VBA identifies the IF-THEN-ELSE-STATEMENT. See the structure of an IF-THEN-ELSE-STATEMENT below:

If [CONDITION] Then 'Code if true Else 'Code if false End If

This is an example usage:

Dim val1 as Integer, val2 as Integer val1 = 1 val2 = 10 If val1 > val2 Then Debug.Print "Val1 is bigger than Val2" Else Debug.Print "Val2 is bigger or equal than Val1" End If

The IF-THEN in the IF-THEN-ELSE-STATEMENT does not necessary need to be followed by an ELSE. This example below is totally acceptable:

If val1 > val2 Then Debug.Print "Val1 is bigger than Val2" End If

Before we jump into the different boolean operators we need to focus on a moment to explain what a boolean value actually is.

Boolean value are either TRUE or FALSE. Similarly a condition can either evaluate to being TRUE (as in met) or FALSE (as in the condition is not met). VBA as other languages has a boolean variable type which you can equally use if your IF-THEN-ELSE-STATEMENT.

Boolean operators

Operator Example Description AND [CONDITION_1] AND [CONDITION_2] Return TRUE if both CONDITION_1 and CONDITION_2 are met (if they both evaluate to TRUE) OR [CONDITION_1] OR [CONDITION_2] Return TRUE if EITHER CONDITION_1 or CONDITION_2 are met (if either one evaluates to TRUE) NOT NOT([CONDITION]) Return TRUE if CONDITION is NOT met (if it evaluates to false)

Now let’s focus on the conditions.

Operator Description = is equal to (val1 = val2) <> is not equal to (val1 <> val2) < is less than (val1 < val2) > is greater than (val1 < val2) <= is less than or equal (val1 <= val2) >= is greater than or equal (val1 >= val2)

The Iif function

Something that very often comes in handy, especially when wanting to reduce the amount of code is the IIF function. If works similarly as the IF function in Excel formulas. Based on the condition it will return either one of 2 values. See the structure of the IIF statement below:

Iif([CONDITION] , [VALUE1], [VALUE2]) 'Return [VALUE1] if [CONDITION] is met 'Return [VALUE2] if [CONDITION] is NOT met

This is an example usage:

Dim val1 as Integer, val2 as Integer, maxOfTwo as Integer val1 = 1 val2 = 10 maxOfTwo = Iif(val1 > val2, val1, val2)

The IIF statement is often forgotten although it is very powerful and will make your code so much more clear.

ElseIf

The ElseIf statement enhances the IF-THEN-ELSE-STATEMENT statement by adding the possibility of specifying additional conditional IFs in between. See an example below:

Dim val1 as Integer, val2 as Integer val1 = 1 val2 = 10 If val1 > val2 Then Debug.Print "Val1 is bigger than Val2" ElseIf val1 < val2 then Debug.Print "Val2 is bigger or equal than Val1" Else Debug.Print "Val1 is equal to Val2" End If

Select – Case

In some situation rather than create conditions you simply want to enumerate a set of values and carry out different tasks. In these cases the IF-THEN-ELSE-STATEMENT statement seems very excessive.

Select – Case with specific values

You can enumerate each case of a value.

Dim intVal as Integer intVal = 10 Select Case intVal Case 6 Debug.Print "The value is 6" Case 10 Debug.Print "The value is 10" Case 11 Debug.Print "The value is 11" Case Else Debug.Print "Another value" End Select

Select – Case with ranges of values

Alternatively you can express certain ranges of values.

Dim intVal as Integer intVal = 10 Select Case intVal Case 1 to 5 Debug.Print "The value is between 1 and 5" Case 6 to 10 Debug.Print "The value is between 1 and 10" Case 11, 12, 13 Debug.Print "The value is either 11, 12 or 13" Case Else Debug.Print "Another value" End Select

Select – Case with conditions

Lastly you can also use the Select-Case statement with conditions by using the Is operator:

Dim intVal as Integer intVal = 10 Select Case intVal Case Is <= 10 Debug.Print "The value is less than or equal 10" Case Is <= 20, Debug.Print "The value is less than or equal 20" Case Is <= 30, Is > 50 Debug.Print "The value is less than or equal 30 or higher than 50" Case Else Debug.Print "Another value" End Select

Loops

First let’s explain what a loop is. Loops are a common programming concept allowing you to repeat a set of code until you exit the loop. A common example could be looping through a list of grocery items or employees etc.

Types of loops in VBA

VBA supports the following types of loops:

Type Description Example For Loop The basic type of loop. Executes the loop, increments the index until the index does not exceed the maximum value. You can modify the increment by adding the Step statement

'Will display 1,2,3,4,5,6,7,8,9,10 For i = 1 to 10 MsgBox i Next i

With Step:

'Will display 1,3,5,7,9 For i = 1 to 10 Step 2 MsgBox i Next i

For Each Loop Similar to the standard For Loop but works only for arrays of variables. Does not permit the Step statement

'An array of Long values Dim arr(0 To 2) As Long arr(0) = 0 arr(1) = 1 arr(2) = 2 'Will display 0,1,2 For Each longVar In arr MsgBox longVar Next longVar

Do Until Loop Will loop until the condition in the Until statement is met (true)

i = 0 'Will display 0,1,2,3,4,5,6,7,8,9,10 Do Until i > 10 MsgBox i i = i + 1 Loop

Or you can push the Until statement to the end of the loop:

i = 0 'Will display 0,1,2,3,4,5,6,7,8,9,10,11 Do MsgBox i i = i + 1 Loop Until i > 10

Do While Loop Will loop while the condition in the While statement is true

i = 0 'Will display 0,1,2,3,4,5,6,7,8,9 Do While i < 10 MsgBox i i = i + 1 Loop

Or you can push the While statement to the end of the loop:

i = 0 'Will display 0,1,2,3,4,5,6,7,8,9 Do MsgBox i i = i + 1 Loop While i < 10

The For loop and For Each loop should be used in different situations! Be sure to read this post

Ranges & Cells

To work with data in Excel files you need to know how to work with Excel cells. When referencing You can reference Excel cells in two ways:

Using the Range object – by the name of the range e.g. “A1:B10” (string argument)

Using the Cells object – by the coordinates of the cells e.g. 10,5 – as in 10th row and 5th column

The distinction between the 2 may seem obvious nevertheless: An Excel Cell is a single cell in an Excel worksheet assigned with a row and column number. An Excel Range is a range of Excel Cells in an Excel worksheet that do not have to be necessarily adjacent e.g. “A1;A10” When you use the Range and Cell object to obtain a reference to a cell or a range of cells these will always return a Range object. Important!: Remember that the Ranges and Cells are objects therefore when defining a variable with a Range or Cell you need to precede the definition with the “Set” statement e.g.

Set rng = Range("A1:A2")

Ranges vs. Cells

The Range object can:

Reference cells by their name or the name of the range e.g. Range(“A1:B10”)

Reference 1 or multiple cells within your worksheet

The Cell object is in fact represented by a Range object with the difference that it can:

Reference only a singe cell by it’s row and column number e.g. Cells(1,2)

Examples

Below are some examples of how to use the Range and Cell objects:

Referencing a single cell

Set rng = Range("B1") Set cll = Cells(1,2) rng.Value = 2 'Set B1 to 2 cll.Value = 3 'Sets B1 to 3

Referencing a range of cells

Set rng = Range("A1:A2") 'Get the range of cells A1 and A2 rng.Value = 10 'Set cells A1 and A2 to "10"

Referencing Named Ranges

Set rng = Range("CELL_NAME") 'Get the range of a Named Range named "CELL_NAME" rng.Value = "Hello!" 'Set named range's value to "Hello!"

Worksheets & Workbooks

To work with data residing on other Worksheets and Workbooks you need to know your way around them. Now we will focus on navigating between various Worksheets and Workbooks in Excel VBA.

The current (Active) Workbook and Worksheet

This is how to refer to the active / current / top-most Workbook or Worksheet you are working on:

'Refer to the first worksheet in the ActiveWorkbook ActiveWorkbook.Worksheets(1).Range("A1").Value = 10 'Refer to your active / current / top-most Worksheet within the ActiveWorkbook ActiveSheet.Range("A2").Value = 20

Want to learn the difference between ActiveWorkbook and ThisWorkbook? Read this post.

How to reference a specific Worksheet or Workbook by name or index

dim wb as Workbook, ws as Worksheet '---Workbooks--- Set wb = Application.Workbooks(1) 'Get first Workbook Set wb = Application.Workbooks("Book1") 'Get Workbook by name "Book1" '---Worksheets--- Set ws = wb.Worksheets(1) 'Get first Worksheet Set ws = wb.Worksheets("Sheet1") 'Get Worksheet by name "Sheet1"

Formatting

Let’s now focus on formatting Excel cells. Below find multiple examples of different formatting options:

Formatting Code Interior

'Alternative ways to set the cell background color With Range("A1").Interior 'Sets the background color to an RGB value (Red,Green,Blue) .Color = RGB(255,0,0) 'Green 'Sets the background color to the Theme color accent 1 color .ThemeColor = xlThemeColorAccent1 'Sets the background color to the ColorIndex .ColorIndex = 1 '1=Black, 2=White, 3=Red ..etc. Check link below for more End With

Read more on the ColorIndex here: link

Font

'Alternative ways to set the cell background color With Range("A1").Font 'Sets the font color to RGB value. See Interior row above for more color options .Color = RGB(0,255,0) 'Sets font as Italic .Italic = true 'Sets font as bold .Bold = true 'Sets the name of the font e.g. Arial, Times New Roman .Name = "Agency FB" 'Set the size of the font .Size = 11 'Sets strikethrough .Strikethrough = True 'Sets as either super or subscript (mutually exclusive) .Superscript = False .Subscript = True End With

Borders

'Set all borders to continuous and thin With Range("A1").Borders .LineStyle = xlContinuous .Weight = xlThin 'Sets the border color to RGB value. See Interior row above for more color options .Color = RGB(0,255,0) End With 'Set only top border as continuos and thin With Range("A1").Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin End With

Possible LineStyles:

xlContinuous

xlDash

xlDashDot

xlDashDotDot

xlDot

xlDouble

xlSlantDashDot

xlLineStyleNone

Possible borders (Index):

xlDiagonalDown

xlDiagonalUp

xlEdgeBottom

xlEdgeLeft

xlEdgeRight

xlEdgeTop

xlInsideHorizontal

xlInsideVertical

Alignment

'Increase or decrease cell indent Range("A1").InsertIndent 1 Range("A1").InsertIndent -1 'Align to left, bottom With Range("A1"). .HorizontalAlignment = xlLeft 'or xlRight .VerticalAlignment = xlBottom 'or xlTop End With 'Set wrap text to true Range("A1").WrapText = True 'Set cell orientation to 45 degrees Range("A1").Orientation = 45

MessageBox and User Forms

In this section we will focus on using MessageBoxes and UserForms to interact with users.

MsgBox: Messages, Notifications, Questions

The common VBA message box can be well used to interact with users to show messages (information, errors, notifications etc.) or just as well ask simple questions (Ok/Cancel, Retry/Cancel). Let us start by analyzing the parameters of the MsgBox function.

Function: MsgBox Parameters:

Prompt – the message you want to show in the message box

Buttons – Optional. One of the following set of buttons below. You can sum them up to get different kinds of message boxes like this vbRetryCancel + vbCritical :

vbOKCancel – Display OK and Cancel buttons

vbAbortRetryIgnore – Display Abort, Retry, and Ignore buttons

vbYesNoCancel – Display Yes, No, and Cancel buttons

vbYesNo – Display Yes and No buttons

vbRetryCancel – Display Retry and Cancel buttons

vbCritical – Display Critical Message icon

vbQuestion – Display Warning Query icon

vbExclamation – Display Warning Message icon

vbInformation – Display Information Message icon

vbDefaultButton1 – First button is default

vbDefaultButton2 – Second button is default

vbDefaultButton3 – Third button is default

vbDefaultButton4 – Fourth button is default

vbApplicationModal – Application modal; the user must respond to the message box before continuing work in the current application

vbSystemModal – System modal; all applications are suspended until the user responds to the message box

vbMsgBoxHelpButton – Adds Help button to the message box

vbMsgBoxSetForeground – Specifies the message box window as the foreground window

vbMsgBoxRight – Text is right aligned

vbMsgBoxRtlReading – Specifies text should appear as right-to-left reading on Hebrew and Arabic systems

Title – Optional. The title of the Message Box

Help – Optional. String expression that identifies the Help file to use to provide context-sensitive Help for the dialog box. If helpfile is provided, context must also be provided.

Context – Optional. Numeric expression that is the Help context number assigned to the appropriate Help topic by the Help author. If context is provided, helpfile must also be provided.

Now some examples:

Retry with Critical Error Icon

Call MsgBox("Try to run the VBA code again?", vbRetryCancel + vbCritical, "Error")

MsgBox example: Critical Error and Retry

Align message and Question Icon

Call MsgBox("Align this to the right hand side", vbQuestion + vbMsgBoxRight, "Some Message")

MsgBox example: Information and align text

User interaction with the MsgBox The message box is a great tool for asking simple Yes/No questions within your VBA code. See some examples below:

If MsgBox("Do you agree?", vbYesNo, "Question") = vbYes Then 'If Yes...put some code here Else 'If No...then do this End If

This will show the following question and pause the code until the user clicks on one of the buttons:

MsgBox example: Yes/No question

Similarly you can create a retry question which is convenient for error handling:

On Error Goto ErrorHandl: Retry: 'Some code here.. ErrorHandl: If MsgBox("Retry?", vbYesNo, "Question") = vbRetry Then Goto Retry End If

Custom User Forms

User Forms fill the gap where a Message Box won’t do. UserForms allow you to create custom forms with Buttons, Combo-boxes, Scrollbars and many more controls.

STEP 1Open the VBA Project to add a new UserForm

Inserting a new UserForm

A new UserForm will be inserted into your VBA Project under a new branch called Forms.

STEP 2Open the new UserForm and the Toolbox to view controls

Let’s create a simple form. First we need to see what form controls can we add to it. To view the Toolbox go to the View menu and select Toolbox:

Open the UserForm Toolbox

STEP 3Add a Button

Now let’s add a simple button to our new form. To do that we simply need to drag it from the Toolbox. Hit the Button icon and drag it to the new form:

Add a Button to your UserForm

STEP 4Add code to your new Button

To add code to your Button – double-click the button on the UserForm. This will navigate you to the code section of the UserForm. You can also go to the View code section as shown below:

View code on the UserForm

This should create the following code stub:

Private Sub CommandButton1_Click() End Sub

Let’s just add a simple MessageBox to the new form to see that the button works:

Private Sub CommandButton1_Click() MsgBox "Hello this is CommandButton1!" End Sub

STEP 5Using the new User Form

Now as we have created our new UserForm let’s put it to the test. Below a simple example of how to execute your UserForm:

Sub TestUserForm() Dim uf as UserForm1 Set uf = New UserForm1 uf.Show End Sub

Try clicking on the button to see that it works!

Objects

To explain it simply Objects are complex data types that can contain multiple properties and procedures. Some typical examples of Objects in Excel are e.g. Workbooks, Ranges, Classes.

Objects need special treating! To create a new Object you need to use the New statement like this:

Dim dict As New Scripting.Dictionary

To use the New statement you need to be sure that the object is defined within your VBA Project or the library in which it is defined is referenced like shown below:

STEP 1Go to Tools->References

Excel VBA Tutorial: Add reference to a object library

STEP 2Reference the object library you want to include

Excel VBA Project: Add the Microsoft Scripting Runtime library

If you don’t want to include a reference to a object library in your project you may also use the CreateObject procedure. See example below:

Dim dict Set dict = CreateObject("Scripting.Dictionary")

This is equivalent to the New operator, however, the object will only be created if the library is available in Excel’s Available References.

When defining your object variables you need to remember to use the Set statement.

Dim dict As Scripting.Dictionary, dict2 as Scripting.Dictionary Set dict = Scripting.Dictionary 'Defining a new Dictionary object Set dict2 = dict 'Set dict2 to reference object dict

Classes

This section has been moved to a separate post. Go to my VBA Class Tutorial

Arrays, Dictionaries and other structures

This section is split into 3 parts: VBA Array Tutorial – learn all about VBA Arrays VBA Collection Tutorial – learn all about VBA Collections VBA Dictionary and other Data Structures – learn about the VBA Dictionary and other data structures like the Stack, Queue, ArrayList and more

What next?

Are you Sure? Get user’s consent before running a Macro in #Excel #VBA for Beginners

I have wasted many hours loading huge amounts of data into spreadsheet models, only to inadvertently click the ‘Reset’ button at the last moment, because it was the day my colleague decided to bump into my chair. Excel is not capable of undoing actions performed by a macro, making it very important to confirm whether a user meant to click a button, before performing certain operations. In this…

New Post has been published on http://analystcave.com/vba-msgbox-vs-inputbox-vs-userform/

VBA: MsgBox vs InputBox vs UserForm

One of the most common mistakes in VBA is using incorrect forms/dialogs for usually simple tasks. The VBA UserForm is one of the most exploited VBA modules I have ever seen (as opposed to VBA Class modules which are underutilized ;)). VBA developers and Excel users seem to think often that every dialog box and form needs to have that personal touch. The effect is often the opposite – with users being confused or experiencing errors with custom UserForms without proper error handling.

Today I want to make it easier for all those VBA users out there.

The form/dialog decision tree

Use the guide below when making your decision on whether you need that UserForm or if you are using that Messsage Box or InputBox in the right case: VBA Message Box is

Decision Tree: MsgBox vs InputBox vs UserForm

When to use Message Boxes?

Use the VBA MsgBox in the following situations:

Short message / information / warning – you want to present the user with a simple message be it an error, message or a warning

Yes or No decisions – ask the user if he/she agrees to your questions/ask or if the macro is to proceed etc.

Don’t use the VBA MsgBox in the following situations:

Long text messages – when wanting to display long messages or text

When to use the InputBox?

Use the VBA InputBox in the following situations:

Simple single variable input – you want the user to input a simple variable such as age, length etc.

Don’t use the VBA InputBox in the following situations:

Yes or No / Boolean variables – don’t ask the user to type in yes or no, 1 or 0 or similar boolean variables. Use a MsgBox instead.

Enumerations – don’t ask the user to input an item from a discrete list of values. Use a UserForm and a dropdown or listbox instead

When to use the UserForm?

Use the VBA UserForm in the following situations:

Complex input / select / interaction – use the UserForm when other options fail and simply a MsgBox or InputBox won’t do the trick

Don’t use the VBA UserForm in the following situations:

Simple single variable input – need the user to input an age or length value? Why not use the InputBox instead?

Yes or No / Boolean variables – use the MsgBox instead

Agree or disagree? Let me know in your comments!

Related Post

VBA: How to Debug VBA

VBA Reference – Functions

POWERPOINT: Easy PowerPoint Timer

VBA: Dir function

VBA: Len function