VBA

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

VBA primer plans

I have been requested numerous times recently by readers in comments and emails to write some how to articles for VBA. It is my intention to start to do this.

My basic plan will be to take situations where new VBA users record some code with the VBA recorder, and then want to try and make it more robust.

vba An article by Nick Hodge (the Excel Nick Hodge) on Dicks Daily Dose of Excel website illustrates my point.

I probably will not write articles as long or as in depth as he has done. Instead I will focus on small parts of this code such as range selection, switching workbooks, defining variables and constants and using them etc. Specifically each article I write will have real practical application. E.g how to copy data from one file to another, how to save a spreadsheet as values for emailing etc.

In the meantime enjoy Nicks article on his coding technique particularly the latter stages where he talks about the progression in code development in VBA.

jethro's picture

Manipulating and Massaging Data in Excel and VBA

Chad Rothschiller, a program manager on the Excel team has written a lengthy article discussing using formulas to 'clean up' data in Excel. It is very indepth and covers these topics

jethro's picture

Dynamic Ranges - and using VBA to create them

Andrew recently posted a piece of VBA code used to create dynamic ranges. Very useful.
Here is my original post on dynamic ranges posted back in 2004 repeated.
If you have used a range name before then you will know that it can be frustrating updating the reference of the range name if you want to add data to the range.
Here are some ways you can dynamically update the range by using the OFFSET function in the range name reference.
Assume for all these examples that column A has a mixture of text and numbers for several cells.
Click Insert - Name - Define on the menu.
In the Names in Workbook Dialog box type a range name (Eg test_range) and then try these different options.
1: Expand Down as Many Rows as There are Numeric Entries.
In the Refers to box type: =OFFSET($A$1,0,0,COUNT($A:$A),1)
2: Expand Down as Many Rows as There are Numeric and Text Entries.
In the Refers to box type: =OFFSET($A$1,0,0,COUNTA($A:$A),1)

jethro's picture

Navipane for Excel

Navipane contacted me to review their addin for Excel on this site.
I went to the Navipane website, downloaded, installed and used the Navipane addin for Excel. I noticed there a bunch of similar addins for other Microsoft Products, notably PowerPoint, Word and Access.

I am using Excel 2007 so this review relates to how I interacted with Navipane in that environment.

When installing you are required to have Excel closed.
The first thing I noticed after opening Excel again was that there was an addin menu called Navipane in the ribbon, and a resize able tabbed window had opened up on the right hand side of the Excel pane titled Navipane System.
The tabs listed are Home, Sheets, Favorites and History.

Home