Excel VBA declare Global Variable and variable scope

Posted on January 31, 2017 by Wyman W

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...

Access Excel VBA Array

Posted on October 24, 2016May 6, 2018 by Wyman W

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...

Excel VBA select multiple worksheets

Posted on December 14, 2015October 24, 2016 by Wyman W

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...

Excel VBA Inputbox and Application.Inputbox

Posted on June 21, 2015April 13, 2022 by Wyman W

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 of Inputbox...

VBA Difference between Dim Set

Posted on April 21, 2015April 21, 2015 by Wyman W

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...

Difference between ByRef ByVal in Access Excel VBA

Posted on March 25, 2015April 21, 2015 by Wyman W

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 ...

Excel VBA Msgbox and handling of return value

Posted on February 9, 2015August 22, 2015 by Wyman W

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 ...

Excel VBA Indentation and Auto Indent

Posted on February 7, 2015March 29, 2015 by Wyman W

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...

Function, Sub, Method, Property Differences

Posted on January 22, 2015April 21, 2015 by Wyman W

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...

Writing your first Excel Macro by recording Macro

Posted on January 5, 2015June 22, 2015 by Wyman W

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...