Microsoft Excel's worksheet password protection options have been known to trip people up from time to time. Mainly because there are just so many option combinations to choose from! Hopefully by the end of this article you will be comfortable with all the various restrictions you can place on other users who may be working in or viewing your spreadsheets.
Full article: The various ways to password protect Excel worksheets
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.
This post will explain 5 different uses for the Immediate Window:
- Get info about the Active Workbook.
- Execute a line of VBA code.
- Run a macro.
- Get or set a variable's value.
Full article: 5 ways to use the VBA Immediate window in Excel
The Worksheet Object has a
UsedRange property. Normally, we should be able to use this property so that we can quickly identify the entire
UsedRange on the Worksheet without having to jump through a lot of hoops.
However, a false positive occurs when we test for data and Excel tells us that there is data when in fact there is not. This article develops a VBA function for finding the true used range of a Worksheet.
Full article: Excel Used ghost
In its simplest form Excel's
GETPIVOTDATA function enables you to extract values from a PivotTable report, but if you're like me when you first came across the
GETPIVOTDATA function you were less than pleased with the results. Understandably so, because in it's default form it's quite inflexible.
However, the benefit in using
GETPIVOTDATA, as opposed to a regular cell reference, is huge in terms of reducing your ongoing workload in maintaining your reports. The trick with leveraging
GETPIVOTDATA power is to replace the hard keyed arguments with nested formulas so the
GETPIVOTDATA formula becomes dynamic.
Full article: Excel GETPIVOTDATA function
Chart events can make it much easier for people to use your programs, and add interactivity to your charts.
In this article, you'll find out how useful chart events can be. They can extend the user interface of the programs you write, making it easy for users to identify points for your program to work on.
With a little ingenuity, you can create powerful applications. Once you've experimented with chart events, you'll think of many ways to enhance your projects.
Full article: Chart events in Microsoft Excel
One of the greatest things about Excel is that with its endless array of tools and commands it almost always offers multiple ways of achieving a certain goal.
Some Excel purists (particularly those that are familiar with Array formulas), will almost always opt in favor of formulas, whereas a pragmatic will always choose the fastest way (whatever it may be). The following example shows pragmatic and pure approaches to solving the same problem.
Full article: Pragmatism vs. purity in Excel
During one of my Excel training sessions, I was surprised to find out that the people were aware and quite proficient in using
VLOOKUP, but most of them had no idea about
Here in this blog post, I will try and compare these 2 formulas as objectively as I can in terms of popularity, ease of use, flexibility, and speed.
INDEX/MATCH is powerful combo that has more flexibility and speed formula than the
VLOOKUP formula. If you haven't already adopted
MATCH as the way of life, it is time you do it.
Full article: VLOOKUP vs. INDEX/MATCH – The debate ends here!
An event occurs when certain actions are performed by the user or VBA. For example when you open a workbook the
Workbook_Open event is triggered. When you (or your VBA) saves a workbook, the
Workbook_BeforeSave event is triggered.
These events are built into Excel VBA and are executed automatically by Excel. You can write your own code which is also executed when these events occur. This code is known as an Event Handler.
In this article I'm going to look at events for worksheets and workbooks, but there are also events specific to the Excel application itself, user forms, and charts.
Full article: Workbook & worksheet events in Excel VBA