You may want some way of pausing or delaying VBA code execution and you can do this with two functions called Wait and Sleep. You can also do this using a loop, and we will look at that approach too.
Why would you pause the code? Maybe you need to wait for another task to finish, for instance if you made a call to a Windows API/shell function. Or you may want to wait for the user to update data in the sheet, or you just want to run a macro at a set time.
Full article: Pausing or delaying VBA using Wait, Sleep or a loop
Today's post is about the Goal Seek Method of the Range Object of the Excel Object Model.
I can use Goal Seek to manually find a value, but what if I need to find values for 12 different months? 52 Weeks? Some other scenario with 100's of desired outputs? Time for some VBA!
Full article: Begin with the end in mind
PivotTables are one of the most useful tools in Excel. They allow you to easily summarise, examine and present a complex list of data.
This blog post explores 5 advanced PivotTable techniques:
- Grouping fields by month and year.
- Calculating data as a percentage of the total.
- Using Slicers.
- Applying Conditional Formatting to PivotTable data.
- Creating calculated fields.
Full article: 5 advanced PivotTable techniques
The scope of a variable in Excel VBA determines where that variable may be used. You determine the scope of a variable when you declare it. There are three scoping levels: procedure level, module level, and public module level.
This article describes VBA variable scope, including examples.
Full article: Variable scope in Excel VBA
How do you know when a user has entered a value into a formula cell, essentially overriding your formula?
Starting with Excel 2013, we can use conditional formatting with the new ISFORMULA function to highlight when this happens.
Full article: Formula override Conditional Formatting alert
This post shows a simple technique that will vastly reduce the number of errors in your VBA code.
The simple technique is the Assertion statement. It is simple to use and implement and will provide dramatic results. However don't be fooled by the simplicity of Debug.Assert. Used correctly it is an incredibly powerful way of detecting errors in your code.
Full article: How to reduce VBA errors by up to 70%
Today we look at Excel's built-in feature that flags inconsistent formulas, and see how that feature can call attention to potentially critical information lurking beneath the surface.
Excel's way of telling you that the formula underneath a cell is not like the others is to display a small green triangle in the upper left-hand corner of the cell.
Full article: Detecting inconsistent formulas
Is there any way to make a shape stick to a point in a scatterplot, not an absolute location on the chart?
Of course. There is actually a pretty simple way to make the shapes stick to the points. This article shows you how.
Full article: Stick a shape to a point in an Excel chart
By default, Excel (and most other spreadsheet systems too) return approximate matches when using the VLOOKUP function.
There is a benefit in using the approximate match, it is quicker (as binary search is used) but for small spreadsheets the difference is negligible.
The interesting thing is that there are risks associated with using the approximate match. Because it uses binary search it requires the search range to be sorted. If not, funky things can happen.
Full article: VLOOKUP considered harmful
