Do you know the data type?

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?




4 Responses to “Do you know the data type?”

  1. Harlan Grove Says:

    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.

  2. jonpeltier Says:

    In other words:

    I never assume a user puts a value of a particular type into a cell.

  3. Johan Nordberg Says:

    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.

  4. JP Says:


    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.


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: