How to reference cells dynamically using VBA

The best way to go through cells dynamically is using the Cells object inside a loop. This way you can refer to a different cell on each pass of the loop. Use a simple counter variable (you can name it whatever you want), which is increased or decreased on each loop pass. Here are some [...]

How to create a Pivot Table using VBA

You can generate a Pivot Table report with a mouse click. You can do it using Excel VBA and macros! Let’s suppose you want to generate a Pivot Table report based on a given Excel table created; see below… Figure 1: Source data for Pivot Table report This would be the resulting PT report (a [...]

How to run a macro from a button

You can run an Excel macro by clicking any Excel object you can imagine: text box, shape, picture, chart, etc. See below the main Excel objects you can use... After storing your Excel macro for future use, you can attach your macro to any button inside any workbook. Here's the 3-step process to do it... Go [...]

How to store Excel macros for future use

If you acquire a brand new car and ignore the location of its key, you can't do anything with it, neither open the doors to get in, nor drive it. In a sense, when you don't know the location of your recorded or written Excel macros you cannot do anything with them, neither edit nor [...]

How to find the code of a VBA macro in a workbook

Are you asking where the macros in a workbook are? Well, you should know that Excel stores VBA stuff: macros, modules, user forms, etc. as well as spreadsheet data inside workbooks. How does this work? Here's a quick explanation... To access your macros VBA code you need to open the workbook compartment that contains VBA data, this is [...]

How to record a dynamic Excel macro

You want to create dynamic Excel Macros that work on variable-size arrays, right? Maybe you recorded/created an Excel VBA Macro and worked awesome this time, but you are wondering if it will work the same next time. In just a moment... You will learn how to record a dynamic Macro using relative references. Don't feel [...]

Quick tip to make your recorded Excel macros super fast

Here's the tip. Turn off screen updating. Doing this is like jumping to the desired movie scene instead of watching a chain of quick-motion sequences before you watch it. When you do this, your macro will not spend computing resources to update the screen at every action it performs. Shifting sheets (this does not occur if you [...]

How to communicate with other applications using VBA?

There are a lot of benefits of interacting with other applications using VBA language... You can use data generated somewhere in other application, for example: creating new PowerPoint slides using Excel charts and tables You can perform commands that don't exist in your application, e.g. inserting Excel calculations in a Word document You can expand the functionality [...]

Excel VBA object model demystified

I want you do me a favor, please identify the fruit #2 and the fish #1 in the picture below... You now are thinking, well, I can't tell you until you specify to me: from what tree you want the fruit (tree 1 or 2) and from what river you want the fish (river 1 or 2). [...]

Excel VBA downloads, sample files, cheat sheets and more

Here are all the downloads about Excel VBA macros... {wpdm_category=excel-vba}