August 14, 2011
August 03, 2011
Excel Power Analyst Boot Camp
Mike Alexander of DataPig Technologies is holding a seminar at Alexandria, VA (near Washington the city, not the state), from September 19, 2011 (Monday) to September 20, 2011 (Tuesday).
Topics include
Analysis
Reporting
Sourcing External Data
Bacon Recipes (one can only hope)
Click the pig to check it out!
Oink! :-)
Topics include
Click the pig to check it out!
Oink! :-)
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.
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.
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.
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.
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!
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!
