VBA Archives - Page 2 of 3 - Struggling To Excel

Magically Format Tables in Excel using VBA

By Ejaz June 2, 2014Macros5 Comments

Excel Tables make analyzing data, a breeze. It surprises me that it is not used as often as it should. It automatically “includes” new data you add to your spreadsheets, it automatically drags down formulas for you, it automatically formats the tables for you. In addition to that, you can use structured references that make your formulas tractable without having to name each range. You can also link an Excel Table to your PowerPivot Model. For a comprehensive, yet concise list of stuff excel tables can do, I recommend reading through this page.

Get the Actual Used Range in a Spreadsheet

By Ejaz May 26, 2014Macros32 Comments

In one of my posts, I wrote about the UsedRange property of the Worksheet object. I use it in almost all of my spreadsheet applications. Excel keeps track of the last cell you used during your current session, and uses it to determine the used range of a sheet. The last cell is the one you get to, when you press Ctrl+End.

Prepare for the show: A framework for hiding rows and columns in your spreadsheet applications

By Ejaz April 25, 2014Macros, Tips and Tricks8 Comments

Hiding a bunch of rows and columns in a sheet before showing it to your boss is inevitable. I insert blank rows and columns around a table, so I can use the CurrentRegion property of the Range Object in my code. I add labels to all my named ranges in the sheet. I split out complex formulas into a couple of columns. Ultimately I end up with a lot of rows and columns to hide. I desperately needed a framework to hide and un-hide rows and columns in all my sheets. I experimented with a lot of methods before settling down with one and I would definitely like to know if you have a better way to do it.

To Err is Excel, Handle your Errors with grace

By Ejaz April 15, 2014Macros10 Comments

Error handling is an important aspect of programming in VBA, especially if you are writing macros for other users. Unfortunately, many users ignore it completely. Visual Basics is an amazing programming language, but it lags far behind in the error handling department. All we have is the “On Error”, “Goto” and the “Resume” statements. These statements allow only a few error handling structures, and each of the structures has its own set of expert proponents. In this post, I am going to share with you, a little block of code that I use to handle errors in all my spreadsheet applications; and hopefully offer a fresh perspective.

Snakes in Excel, Lets Play!

By Ejaz April 6, 2014Excel Fun13 Comments

The biggest downside to working in massive corporate offices is the locked down computer systems. There is no internet access, no games, no fun! However, Excel is always installed and kicking. How awesome would it be if Excel had a couple of games in it?

Progress Bar for all your excel Applications

By Ejaz March 27, 2014Applications, Macros74 Comments

Excel is versatile by itself and VBA makes it even better by allowing us to do our own thing. Most of us use VBA to automate tasks of varying complexity – some macros are executed in a flash, but others take hours to run. While there are users who are happy with just a Msgbox “This thing is DONE!”, there are others who’d like to let the user know more about what is happening.

Concatenate Unique Cells, with Text Formatting

By Ejaz February 18, 2014User Defined Function7 Comments

A fellow struggler requested me to help him with a slightly beefed up Range Concatenate function. He needed to combine a bunch of numbers in a range; there were multiple entries of the same number, and he wanted a unique list; finally he needed to format the numbers.

Clean a string for Range Names – Regular Expressions

By Ejaz February 2, 2014User Defined Function3 Comments

I am a big fan of using named ranges in my VBA code. It makes referring to cells in formulas easy, and I can also helps in auditing the code. For instance, I can simply search for “RangeName” (including the quotes) to see if that particular range name is being used in my code.

Local trumps Global – Local Named Ranges make the spreadsheet more tractable

By Ejaz January 21, 2014Macros, Tips and Tricks3 Comments

Creating user friendly spreadsheets is not just a professional courtesy anymore. We have the obligation to help users decipher the spreadsheet we so hastily put together. Spending a little extra time setting up well designed spreadsheets help a firm reduce future costs by

reducing the time spent on testing and scrutinizing the sheet.
improving the productivity of the worker.
helping the ‘new guy’ figure out the sheet in no time.
increasing the visibility of errors, consequently reducing the need for rework.

Navigate in Style! – An Excel VBA Add-in to help get around in a sheet

By Ejaz January 13, 2014Applications4 Comments

Have you ever been frustrated about having to scroll through endless rows and columns in an Excel Spreadsheet? I have! With all the amazing touch screen devices flooding the market, having to use the scroll bar seems a bit archaic. I longed for a better solution and transformed that yearning into a fun little project. I drew inspiration from the navigator panes in graphics design suits and strategy games.