When working with cell values in code, do you generally know the data type you expect in the cell?
Or do you often need to assign the cell value to a variant or whatever and then look inside at the type before deciding what to do?
I reckon most of the time I know as I’m coding what type I expect, basically either a double or a string or an empty cell (and the occasional error 2042). Normally the type of processing is dependent on the data type, and usually I work out the target range before starting processing.
I think its quite rare I would not know, although there are times I might want to check maybe. What about you?
cheers
Simon
Tuesday, 6th May, 2008 at 1:01 am |
So what’s the harm reading a range’s .Value (if you need to distinguish dates from other numbers) or .Value2 (if you don’t need to distinguish dates) into an array of variants? Execution speed working with variants rather than doubles or strings? Still load ranges into arrays of variants, then loop through the array of variants assigning values to arrays of doubles or strings as needed. It’s faster in VBA to process a VBA array of variants than to loop through the range a cell at a time loading doubles or strings into like-typed arrays.
I never assume any cell contains a value of a particular type.
Tuesday, 6th May, 2008 at 5:15 am |
In other words:
I never assume a user puts a value of a particular type into a cell.
Tuesday, 6th May, 2008 at 7:22 am |
I never ever trust the user.
But I guess it depends on where the data is going. Lately I’ve done alot of projects where Excel is the GUI, but data is read from and saved back to a database. Since I wouldn’t dream of having a database without proper types, type checking becomes important.
I try to use the Data, Validation feature to catch the error as early as possible. I also check the type before I save it to the database.
For type checking I use IsNumber, IsDate etc, TypeName and last On Error Resume Next : intValue = CInt(varValue).
But as always you make decisions depending on the project.
Thursday, 8th May, 2008 at 11:54 am |
Simon,
It depends on the sub. Some code has a specific purpose, so the users understand (or can be easily trained) to provide the proper inputs. But on some level you still need to check you aren’t doing math with strings, for example. You never know when someone is going to try something new with your code that it isn’t intended for.
Thx,
JP