Monday, April 24, 2006

VBA tricks - capturing spreadsheet data in VBA

I have been writing and debuging a lot of VBA code lately, though unfortunately most of it is very specific to the applications it is for.

Here is a cool trick I use regularly to combine VBA with spreadsheets and manipulate them.

To use a value stored in a cell, eg a constant or variable, can be done simply by sucking that value into a VBA variable. Naming the range of that cell with the variale name makes this very easy and also not affected by most spreadsheet changes that a user can do. The main two things you need to watch for is the sheet that the range name is on is either hidden or deleted (renaming doesn't matter) or the cell itself deleted.
I also tend to follow some other principles.
Make sure that the range name contains no "illegal" characters. Most SHIFT and a number and punctuation symbols are not allowed in a range name.
Name the range and the variable in VBA slightly differently. I tend to use a convention of my own for this. Eg the range name will be my_name and the VBA variable will be myname.

Dim myname as string
myname = Range("my_name").Value

That piece of code will suck the value from the cell and store it in a variable.


Posted by: Jethro | 7:59 AM |