The VBA objects are organized in a hierarchy, which makes them easier to reference. At the top of this hierarchy is the Excel Application. All the objects within Excel are members or sub-members of the Application object.
Visual Basic for Applications (VBA) is the programming language we use when writing macros in Excel (and other Office programs). It allows us to program Excel so we can save time with boring repetitive tasks, and focus on the fun stuff. We can use VBA for everything from automating a simple task to developing robust applications and add-ins.
Bottom line: Learn how to find the last row, column, or cell in a worksheet using three different VBA methods. The method used depends on the layout of your data, and if the sheet contains blank cells.
Skill level: Intermediate
Bottom line: Learn a few different ways to unhide (show) multiple sheets at the same time with a VBA macro or add-in.
Skill Level: Intermediate
Bottom line: Learn two different ways to quickly (join) concatenate a range of cells. This includes the Ctrl+left-click method, and a free VBA Macro that makes it quick & easy to create the concatenate or ampersand formulas.
Skill level: Intermediate
This post contains a free VBA Macro that will convert any pivot table into a report containing SUMIFS, COUNTIFS, or AVERAGEIFS formulas. If you want a very quick way to create a long SUMIFS formula, this is your ticket. 🙂
The VBA Immediate Window is an awesome tool that allows you to get immediate answers about your Excel files, and quickly execute code. It is built into the Visual Basic Editor, and has many different uses that can be very helpful when writing macros, debugging code, and displaying the results of your code.
An add-in file is an Excel file that contains macros (code) and has the extension “.xla” or “.xlam” (2007 and later). When opened, the worksheets in the file are hidden and the macros are typically accessed through buttons that are added to the toolbar or ribbon. Once installed, an add-in will automatically open every time Excel is opened.
Here is a video that walks through how to install the add-in.
Please see my video below on how to add the add-in’s folder location to the Trusted Locations list. This is now a critical step due to a July 2016 Office Security update.
Installing the add-in is pretty easy, and should only take a few minutes.
Important Note: There is one additional step to this installation due to an Office Security Update released in July 2016. Here is a video that explains it in more detail.
The folder that the add-in file is saved in needs to be added as a Trusted Location in Excel. The instructions on how to trust the folder location are below. I also have an article and video that describes this issue in more detail.
Some users still have issues with the add-in’s ribbon disappearing after trusting the folder location. If this happens to you, you will need to Unblock the file by changing a file property.
You will only need to do this unblock one time. However, if you download an updated version of the file then you will have to repeat the steps above to unblock it.
Hopefully these additional security steps will be fixed in a future update to Office. If you completed all the steps above then you should see the add-ins ribbon tab load every time you open Excel.
Installing An Excel Add-in - Guide (672.2 KB)
Paste Buddy – Create custom keyboard shortcuts for the paste special commands like paste values.
Formatting Shortcuts Add-in – Keyboard shortcuts to change font & fill color and cell styles.
When you have a dashboard with small panel charts it is nice to be able to zoom in on the charts to see the trends better. The ‘Zoom_Chart’ macro included in the workbook allows you to add a zoom button (shape) over the top left corner of the chart to zoom in on the chart. It’s available for free download below. The macro actually resizes the chart to enlarge it, then returns it to it’s original size when the zoom button is pressed again. See the animated screen capture below.
You can control the zoom amount by changing the percentage values for Zoom Width and Zoom Height in cells P5:P6 on the worksheet. These can also be hardcoded in the code if you don’t want the user to change the size of the zoom.
The video above contains a detailed tutorial on how to add the zoom buttons to your own workbook. It’s really a matter of copying and pasting the macro (VBA code) and buttons into your workbook, and then assigning the macro to the buttons. You should still be able to implement this even if you aren’t familiar with VBA or macros. It is important to line up the buttons on the chart correctly and give each button a unique name. So it’s best to watch the video to make sure you don’t miss anything. The steps covered in the video are listed below.
The zoom feature works really well with dashboards and reports where your screen area is limited. If you would like to learn more about charting and dashboards I highly recommend the dashboard course from My Online Training Hub. Checkout my full video review of the course and learn how to become an Excel Superhero! 🙂
The VBA can be further enhanced to add data labels, legends, axis labels, and any additional chart components when the zoom in button is pressed. Please leave a comment with some of the enhancements you made, or would like to see.
Zoom on Excel Charts.xls (82.9 KB)
Zoom on Excel Charts - Bottom Right.xls (84.5 KB)
Zoom On Excel Charts - Top Right.xls (85.0 KB)
The “Bottom Right” file contains modified code that allows you to place the zoom button in the bottom-right corner of the chart. The chart zooms from the bottom-right and expands up and to the left. Zooming from the top-left or bottom-right are the only two options for button placement with this code.
The “Top Right” file contains code that allows you to place the zoom button in the top-right corner of the chart. This option can cause problems if the chart is too close to column A and tries to expand beyond the left side of the worksheet. The location of the chart will move and the chart will need to be manually resized and moved back to align with the zoom button.
Please leave a comment below with any questions or modifications you have made.