Excel VBA declare Global Variable and variable scope

This Excel VBA tutorial explains how to declare private variable, global variable / public variable in VBA, and explain the scope of variable. You may also want to read: Function, Sub, Method, Property Difference between Dim and Set Scope of local variable / global variable / private variable Procedure Level When you declare a variable inside a Sub Procedure or Function (local variable), the variable can only be used within them. For example, in Sub Procedure1, you have declared...
More

Access Excel VBA Array

This VBA tutorial explains how to create and use VBA Array in Access and Excel VBA. You may also want to read: Store unique array item with Scripting Dictionary Access Excel VBA Array In Excel worksheet, you can convert a formula arguments to Array by pressing CTRL+SHIFT+ENTER, click here to see example. In VBA, Array is a variable that can store multiple data of the same data type. There are two types of Array in VBA One-dimensional Array Two-dimensional Array For most...
More

Excel VBA select multiple worksheets

This Excel tutorial explains how to select multiple worksheets using Excel VBA Worksheet.Select Method and using Array. You may also want to read: Excel export Excel to PDF with and without VBA Select multiple worksheets in Excel In Excel worksheet, there are two ways to select multiple worksheets Method 1. Hold down Ctrl and then left click the mouse on each worksheet tab   Method 2. Right click on one of the sheet, and then click Select All Sheets One practical use...
More

Excel VBA Inputbox and Application.Inputbox

This Excel tutorial explains difference between VBA Inputbox and  Application.Inputbox and how to use them. You may also want to read: Excel VBA Msgbox and handling of return value Excel VBA use Inputbox to select options Excel VBA Inputbox and Application.Inputbox Inputbox is a pop up dialog box that displays a message and provides a text box for users to input free text. It is very similar to Msgbox except that Msgbox does not have a text box for user input. There are two kinds o...
More

VBA Difference between Dim Set

This Access/Excel VBA tutorial shows the difference between Dim and Set keyword in variable declaration. You may also want to read: Excel VBA Option Explicit to force declaration of variables Difference between ByRef ByVal Function, Sub, Method, Property Differences VBA Difference between Dim and Set In VBA, we can always see the use of Dim and Set, but in some occasions we don't. Example 1: Dim rng as Range 'Optional Set rng = Range("A1") Example 2: Dim s as String 'Op...
More

Difference between ByRef ByVal in Access Excel VBA

This tutorial explains difference between ByRef ByVal in Access Excel VBA. You may also want to read: Difference between Dim and Set Excel VBA Option Explicit to force declaration of variables Function, Sub, Method, Property Differences Difference between ByRef ByVal in Access Excel VBA ByRef means to pass a variable or Object by Reference (memory address). ByVal means to pass a variable or Object by Value. When you define a Function with arguments, you can define the variable ...
More

Excel VBA Msgbox and handling of return value

This Excel tutorial explains how to use Excel VBA Msgbox with carriage return such as vbCr, vbNewLine, and handling of return value such as vbOK, vbCancel, vbRetry You may also want to read: Excel VBA Inputbox and Application.Inputbox What is Excel VBA MsgBox? Excel VBA MsgBox is a prompt box that is used to prompt user for important information, usually provide user with OK and Cancel buttons. Note that there is another prompt box called InputBox which receives text input from user but ...
More

Excel VBA Indentation and Auto Indent

What is Excel VBA Indentation? Indentation represents some spaces at the beginning of a new row of sentence. Indentation is not a word reserved for VBA, it is a word that also appears in Microsoft Word, Excel spreadsheet and even Powerpoint. Indentation is also used for all kinds of computer programming. In VBA coding, indentation is not mandatory, which means it does not cause compile error if you miss it, but it is definitely a good practice for you or for others to follow your code easily...
More

Function, Sub, Method, Property Differences

This tutorial explains differences among Function, Sub, Method, Property Excel / Access VBA. You may also want to read: Difference between Dim and Set Excel VBA Option Explicit to force declaration of variables Difference between ByRef ByVal Function, Sub, Method, Property Differences In my blog, I have categorized different VBA topics by their properties. If you don't understand Function, Sub, Method, Property Differences, you will find my tips confusing and difficult to use. Thos...
More

Writing your first Excel Macro by recording Macro

This Excel tutorial guides you to create your first Excel Macro by Record Macro function, and explain how to modify Macro in Visual Basic Editor You may also want to read: Create Excel Add-in and add in Ribbon Excel Record Macro Writing your first Excel Macro by recording Macro, why? First of all, you should face the fact that you cannot know everything. You can use Microsoft Windows does not mean you can write your own Windows. Even if you are a Windows Administrator, it only means...
More