July 25, 2011

VBA Code Indenter 2

A slightly "improved" version of my VBA Code Indenter is available.

This version starts indenting after Sub and Function names. The reason I decided to do this is because it seems to be the standard with other folk's code I have seen posted online. Who am I to go against the grain? (I have a bridge to sell you if you think I'm a conformist!)

The download link is here.  

Posted by andrewe at 18:46

July 24, 2011

VBA Code Indenter

There are 2 VBA code indenters that I know of, and unfortunately, I am not able to install either of them where I need them most to due to admin rights. With "complex" code, indenting can help a lot by making code easier to read so I decided to make my own.

My own version is rather basic. You can either indent a whole project or selected module. The menu items are available at the bottom of the right click menu in the Code Pane.

VBA Code Indenter


Note that you must tick Trust access to the VBA project object model before installing. Here is how to do it with various Excel Versions.

Pre-2007
Go to the Tools Menu at the top of Excel. Select Macros, Security, then Trusted Sources. Tick "Trust access to Visual Basic Project".

Excel 2007
Go to the Developer tab at the top of Excel. Select Macro Security, Macro Settings and then tick "Trust access to the VBA project object model."

If the Developer tab is not visible, select the round Office button at the top of Excel, then Excel Options, Popular, "Show Developer tab in the Ribbon."

Excel 2010
Go to the Developer tab at the top of Excel. Select Macro Security, Macro Settings and then tick "Trust access to the VBA project object model."

If the Developer tab is not visible, select the File button at the top of Excel, then Options, Customize Ribbon and tick Developer on the Customize the Ribbon, Main Tabs list.



Here is the download link to try it out.


  
Posted by andrewe at 11:09

July 16, 2011

Quick Paste Special

When I saw Paste Special in Excel 2010, I thought "Cool!". I was a bit dubious about some of the choices and the icons that represent them, but that was okay because nobody ever thinks the same way.

However, I had 2 problems. The first is I only have 2010 at home, where I get to play with Excel only with permission signed and countersigned in triplicate 3 months in advance. (At this time of writing, I can feel waves of wrath heading in my general direction - this explains my infrequent blogging, right?).

Which leads to the second problem. Where I do most of my development, I have Excel 2007 and the extra coaxing required to paste just what I want can be a little frustrating at times.

Anyway, as you can guess, I decided to make my own. I tried to think of some of the most useful paste special options or combinations thereof. I've added 20 of them to the right click menu (Cells, Rows and Columns) and I also added a most recently used menu item so you don't have to keep looking if you are a (mostly) mouse person like me. You can access the commands by using the keyboard too though.

Here's what it looks like in Excel 2007 and Excel 2010.

Excel 2007


Excel 2010


I have not tested but I'm guessing it will work with other versions. (English menus only, sorry!)

Here is the download link. Hope it comes in useful!  
Posted by andrewe at 09:55

July 03, 2011

Excel Everest

Recently I was asked by Excel Everest for a review of their training.



First off – I like their approach of using Excel itself to present the training.

And I liked how they make it fun. (Watch out for the bear and other hazards!)

What’s most important is that the available courses cover a lot of useful, hands-on material that is simple to follow and version specific.

Learning Excel can be an uphill battle, but Excel Everest will help you climb your way to the top ;-)  
Posted by andrewe at 10:51