Solution The VBA macro code finds all the dependent cells of the active cell (selected cell) and displays their full address in a message box. This macro shows all dependent cells including ones that are outside of current worksheet and workbook. The key to the solution is to use the method “Range.Navigatearrow” to go through…
Click here to download the Automatic Goal Seek / Guess and Test VBA Macro with Excel Input Template. With Goal Seek, Excel can calculate an answer by guessing and testing until it gets to the correct answer. The Goal Seek operation is quite handy when an answer we want cannot be calculated using normal formulas.…
Using the For Each Loop The code below loops through all worksheets in the workbook, and activates each worksheet. The code uses the “for each” loop to loop through the wrosheets contained inside ThisWorkbook. After it is done looping through all the worksheets, it reactivates the original worksheet that was active before running the macro.…
In a prior post (How to unhide or delete name ranges in Excel?), we talked about how to unhide and use the name range interface (Ctrl+F3) to select and delete name ranges. But what if you want to use VBA to delete name ranges quickly or with certain conditions? It is not uncommon to see older worksheets ending…
Sometimes you want to apply the “IFERROR” in Excel to trap errors and avoid “ugly” output, such as below: Let’s say for a report to your manager, you may have cleaner output. Instead of showing “#DIV/0”, you want to show “n/a” as the data point is not available. The best way is to use the…
You can use VBA to automatically “type” characters (and numbers) on the keyboard. In addition, it can also perform keyboard actions, such as Control+C (copy), Control+A (select all), Alt+Tab, etc. (See examples below) Almost anything that can be done on the keyboard is possible through VBA. This is very useful as you can automate many…
The “.Areas.Count” property has the number of areas stored in a Range object. You can loop through the “.Areas” in a Range object to access each of the areas in a range individually. This ability is very handy on subroutines or functions designed to perform actions on all cells a user selected, because this accounts for any number of areas a…
For some reason, after Excel 2003, you just can’t change the textbox name on a chart tab anymore. (It still works in a normal worksheet tab.) And it’s actually a crucial feature since it is how textbox are referenced in VBA. In Excel 2003, you can read the textbox name by clicking on it, and…
You can access and retrieve the data in the clipboard into a variable in Excel VBA, using the Microsoft Forms library. On top of copy and paste the code below, you must follow the these steps and activate Microsoft Forms 2.0 Object Library for the code to work. You only need to do it once…
You could use Excel VBA to move the mouse and click on things (left and right click). Below is an example of moving the mouse to the top left of the screen and then clicking. Just copy the code and paste it into macro window in Excel. The SingleClick() subroutine is a single click, while…