-->

The syntax in a Visual Basic Help topic for a method, function, or statement shows all the elements necessary to use the method, function, or statement correctly. The examples in this topic explain how to interpret the most common syntax elements.

Activate method syntax

Excel VBA Programming Functions Visual Basic for Applications (VBA) gives experienced Excel users a wide range of options for creating Excel spreadsheets and customizing how they look and function. Speaking of functions, the table following shows Excel VBA functions and what they accomplish. Function What It Does. The VBA Time Saver Kit is a need-to-have kit for beginner and advanced VBA coders. It makes your life easier as a VBA developer and makes it the first place to go for VBA code snippets and examples. Folder including BAS files – separate module in each to make it easy to include or exclude the right modules. Be sure to bookmark this page as your Excel VBA cheat sheet! Visual Basic for Applications makes automation possible in Excel and other Office applications. The below Excel VBA Cheatsheet is your one stop shop for a variety of useful VBA automations. If you are new to VBA start with my Excel VBA Tutorial.

object.Activate

In the Activate method syntax, the italic word 'object' is a placeholder for information you supply—in this case, code that returns an object. Words that are bold should be typed exactly as they appear. For example, the following procedure activates the second window in the active document.

MsgBox function syntax

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

In the MsgBox function syntax, the italic words are named arguments of the function. Arguments enclosed in brackets are optional. (Do not type the brackets in your Visual Basic code.) For the MsgBox function, the only argument you must provide is the text for the prompt.

Arguments for functions and methods can be specified in code either by position or by name. To specify arguments by position, follow the order presented in the syntax, separating each argument with a comma, for example:

To specify an argument by name, use the argument name followed by a colon and an equal sign (:=), and the argument's value. You can specify named arguments in any order, for example:

The syntax for functions and some methods shows the arguments enclosed in parentheses. These functions and methods return values, so you must enclose the arguments in parentheses to assign the value to a variable. If you ignore the return value or if you don't pass arguments at all, don't include the parentheses. Methods that don't return values do not need their arguments enclosed in parentheses. These guidelines apply whether you are using positional arguments or named arguments.

In the following example, the return value from the MsgBox function is a number indicating the selected button that is stored in the variable myVar. Because the return value is used, parentheses are required. Another message box then displays the value of the variable.

Option Compare statement syntax

Option Compare { Binary | Text | Database }

In the Option Compare statement syntax, the braces and vertical bar indicate a mandatory choice between three items. (Do not type the braces in the Visual Basic statement). For example, the following statement specifies that within the module, strings will be compared in a sort order that is not case-sensitive.

Dim statement syntax

Dimvarname [([ subscripts ])] [ Astype, ] [ varname [([ subscripts ])] [ Astype ]] . . .

In the Dim statement syntax, the word Dim is a required keyword. The only required element is varname (the variable name).

For example, the following statement creates three variables: myVar, nextVar, and thirdVar. These are automatically declared as Variant variables.

The following example declares a variable as a String. Including a data type saves memory and can help you find errors in your code.

To declare several variables in one statement, include the data type for each variable. Variables declared without a data type are automatically declared as Variant.

In the following statement, x and y are assigned the Variant data type. Only z is assigned the Integer data type.

The shorthand to declare x and y as Integer in the statement above is:

The shorthand for the types is: % -integer; & -long; @ -currency; # -double; ! -single; $ -string

If you are declaring an array variable, you must include parentheses. The subscripts are optional. The following statement dimensions a dynamic array, myArray.

See also

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Home ➜ VBA Tutorial ➜

If you want to create a sheet, want to delete it, or move or copy it, there’s one thing that you need to know that if that sheet exists or not.

To write code to check the sheet exists or not you need a loop that loops through each sheet in the workbook and matches the name you have provided. But here’s the thing, you can use two different loops for this (For Next and For Each) and today we will use both.

In this tutorial, we will look at different ways to do that, so, make sure to have the developer tab on your ribbon and open the VBA editor to write this code.

Check IF a Sheet Exists in the Current Workbook

With this loop, you can refer to all the sheets in the workbook and loop through each one by one to match the name of the sheet with the sheet name that you want to search.

Follow these steps:

1. First, declare a variable to use for the sheet while performing the loop and to store the sheet name that you want to search.
   
2. Next, write a line of code for an input box to enter the name of the sheet that you wish to search.
   
3. After that, start your loop with the For Each keyword. And use the variable to refer to each worksheet in the workbook.
   
4. From here, you need to write an IF THEN ELSE statement to match the name of the sheet with the name that you have entered in the input box, and then show a message box if match found and exit the procedure.
   
5. In the end, a message box to inform you if there’s no match found.
   

Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook

Full Code:

Let me explain how this works: When you run this code, it shows you a message where you need to enter the sheet name that you wish to find.

After that, it loops through each sheet matches the name with the name you have entered, and if the name matches with a sheet, it shows you a message and another message if there’s no match.

Excel Vba Syntax Cheat Sheet Pdf

Here another code to check if a sheet exists or not.

This code uses the FOR NEXT loop and uses the total count of sheets in the workbook, and based on that, perform a loop in each sheet matches the name with the name you have entered.

Check IF Sheet Exists in Closed Workbook

Excel Vba Syntax Cheat Sheet Free

In the following code, you have a loop that searches for the sheet name in a closed workbook. To refer to the file, we used the file address.

Microsoft Vba Cheat Sheet

When you run this macro, it opens the file at the back end as you have turned OFF the screen updating, and once it loops through all the sheets, you have code to turn ON the screen updating.

Note: As you can see, in the file location address, we have the file extension that means you need to have the correct extension of the file to refer to it.

Vba Code Basics Cheat Sheet

More Tutorials on VBA Worksheets