Excel VBA Cheat Sheets » Find it hard to write Excel VBA Macros? Think there’s too much to remember? You’re not alone! Check out my convenient one-page guides. They’ll help you write Excel VBA Macros better and faster. Click here to find out more. Previous Post VBA Code Cheat Sheet: Paste Special: Values only (remove formulas, retain value) Next Post VBA Code Cheat Sheet: Merge Data from all Worksheets to 1 Blog at WordPress.com. Math functions Excel Formulas Cheat Sheet =SUM – add the total of a series of numbers =AVERAGE – calculates the average of a series of numbers =MEDIAN – returns the median average number of a series. Sheet Before Current Sheet: ActiveWorkbook.Sheets.Add After Current sheet: Worksheets.Add After:=ActiveSheet Add more than 1 worksheet: Worksheets.Add Count:=4 Columns Insert 1 empty column: (Assuming that you have selected cell A1) Before Column A: Columns('A').Insert After Column A: Columns('B').Insert (I know the above looks redundant but you will see why there is need to include this when. VBA CHEAT SHEETS Description VBA Code Activate by Tab Name Sheets(“Input”).Activate Activate by VBA Code Name Sheet1.Activate Activate by Index Position Sheets(1).Activate Next Sheet ActiveSheet.Next.Activate Get ActiveSheet MsgBox ActiveSheet.Name Select Sheet Sheets(“Input”).Select Set to Variable Dim ws as Worksheet Set ws = ActiveSheet.

1. Vba Cheat Sheet Access
2. Vba Userform Code Cheat Sheet

You can use most Microsoft Excel worksheet functions in your Visual Basic statements. For a list of the worksheet functions you can use, see List of worksheet functions available to Visual Basic.

Note

Some worksheet functions are not useful in Visual Basic. For example, the Concatenate function is not needed because in Visual Basic you can use the & operator to join multiple text values.

Calling a worksheet function from Visual Basic

In Visual Basic, the Excel worksheet functions are available through the WorksheetFunction object.

The following Sub procedure uses the Min worksheet function to determine the smallest value in a range of cells. First, the variable myRange is declared as a Range object, and then it is set to range A1:C10 on Sheet1. Another variable, answer, is assigned the result of applying the Min function to myRange. Finally, the value of answer is displayed in a message box.

If you use a worksheet function that requires a range reference as an argument, you must specify a Range object. For example, you can use the Match worksheet function to search a range of cells. In a worksheet cell, you would enter a formula such as =MATCH(9,A1:A10,0). However, in a Visual Basic procedure, you would specify a Range object to get the same result.

Note

Visual Basic functions do not use the WorksheetFunction qualifier. A function may have the same name as a Microsoft Excel function and yet work differently. For example, Application.WorksheetFunction.Log and Log will return different values.

Inserting a worksheet function into a cell

To insert a worksheet function into a cell, you specify the function as the value of the Formula property of the corresponding Range object. In the following example, the RAND worksheet function (which generates a random number) is assigned to the Formula property of range A1:B3 on Sheet1 in the active workbook.

Example

This example uses the worksheet function Pmt to calculate a home mortgage loan payment. Notice that this example uses the InputBox method instead of the InputBox function so that the method can perform type checking. The Static statements cause Visual Basic to retain the values of the three variables; these are displayed as default values the next time you run the program.

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.

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.

The Toolkit consists of:

• XLSM file with examples and all modules included
• Folder including BAS files – separate module in each to make it easy to include or exclude the right modules

What does it contain?

Ever got stuck trying to remind yourself what was the name of that VBA function? Googling again for a commonly used VBA code snippet? Struggling to remember the syntax of the Do Until loop or Select Case conditional statements? The VBA Time Saver Kit is just what most VBA beginner and advanced developers needs.

VBA Code Module Snippets

The VBA Timer Saver is a collection of code snippets / assets that have been built over time on AnalystCave.com. VBA Code Snippets have been groups into over 11 unique Code Modules with Code Snippets you use on a Daily Basis when programming in Excel VBA.

Code Modules have been separated to allow you to import them (*.bas files) to your project at ease on demand whenever you are struggling with a particular tasks e.g. Reading/Writing to Files, wanting to present Macro execution progress via a Progress Bar and much more.

Click on the images below to learn more what is contained in VBA Timer Saver package:

Who will find it useful?

The VBA Time Saver Kit is dedicated equally to VBA Beginners (easy VBA Function reference, code examples) as well as Advanced VBA Coders (ready useful code snippets).

VBA Beginners will find the VBA Time Saver Kit especially useful when trying to reference available VBA Functions. The Kit groups most available VBA Functions into 4 categories: Dates, Strings, Files and Conversions – all linking out to function definitions here at AnalystCave. It contains ready easy to reference examples of VBA Loops and Conditional statements.

Advanced VBA codes will appreciate the collection of VBA time saving code modules, often complex functions that prove useful during mundane coding tasks. Cool examples include an advanced progress bar (userform), functions for measuring code execution (up to milliseconds), creating arrays from strings and even a function allowing you to create an additional VBA thread!

Installation Notes

Be aware the VBA Time Saver contains a variety of VBA Code Snippets, some of which may not work on Mac OS due to a lack of system libraries to support these features.

Be aware some Antivirus (at least Avast, ClamAV and Rising) software may incorrectly assess that the Kit contains a Virus. This is not the case. If you get a notification like this please reach out to me directly at analystcave(at)gmail.com to receive the Kit in another format.

Vba Cheat Sheet Access

Download the VBA Time Saver Kit

To receive a one-time download link to the provided email, proceed to payment by clicking on the Buy Now button below. Thank you in advance for purchasing the Kit and supporting new exciting posts and tools from AnalystCave!

Vba Userform Code Cheat Sheet

Have questions or issues with purchasing/downloading the AddIn? Write directly to me at analystcave(at)gmail.com!