Variant Vaiable Type

  • Thread starter Thread starter boborta
  • Start date Start date
B

boborta

Over the years I have avoided using variant data types unless
absolutely necessary. Avoiding them is mentioned in most text books
I've used and conversations/posts have agreed with this position. I
just finished reading the contents of a book and to my surprise, it
recommends using variants when interacting with the JET. The following
information is cited from the text as indicated.


VBA Professional Projects
by Chaudhary, Rachna.; Goel, Taruna.
Publication: [Rocklin, Calif.] Course PTR, 2002.

(Quote from Appendix A:)

"Note that you should use explicit data types only when working with
VBA. If you are working with data stored in tables within your code or
working with the Jet database, it is recommended that you use Variant
data types. This is because the Jed database integrates and works with
Access using Variant data types. Therefore, if you specify explicit
data types, VBA code needs to convert data types, and this may lead to
slower performance of the application."

What say other experts?

Regards,
Bob
 
Variants are useful, and I agree with the authors.

There are 2 values here that may conflict, but are not contradictory:
a) Always type your variables as tightly as you can in VBA.
b) If a procedure accepts values from a field, you need to declare its
arguments as variants.

The Variant is the only VBA data type that can contain Null or Error. Null
is very common in fields: even required fields (e.g. at a new record, or in
an outer join.) The Error value also turns up in cases other than bad
expressions (e.g. when a report has no records, or an argument is optional.)

Therefore, you need to write your procedures so they accept variants. Then
the first task in the procedure is to validate the variant contains the
expected data type.

If a function may need to return Null, its return value will also be a
Variant.

Here's a simple example that calculates someone's age as of a certain date,
based a on date of birth field:
http://allenbrowne.com/func-08.html
It accepts variants, and uses IsDate() to test them. But within the routine,
it works on Date type variables to ensure that the data is handled
correctly. (That's particularly important with dates in international
settings.)

The function therefore meets both design goals:
- It accepts and returns Variants, so it copes with all data.
- It operates on variables that are data typed as tightly as possible.

In my view, the code is much more reliable if accepts and checks the
variants rather than expects every function call to be wrapped in IIf()
expressions that tests for nulls, errors, etc.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Over the years I have avoided using variant data types unless
absolutely necessary. Avoiding them is mentioned in most text books
I've used and conversations/posts have agreed with this position. I
just finished reading the contents of a book and to my surprise, it
recommends using variants when interacting with the JET. The following
information is cited from the text as indicated.


VBA Professional Projects
by Chaudhary, Rachna.; Goel, Taruna.
Publication: [Rocklin, Calif.] Course PTR, 2002.

(Quote from Appendix A:)

"Note that you should use explicit data types only when working with
VBA. If you are working with data stored in tables within your code or
working with the Jet database, it is recommended that you use Variant
data types. This is because the Jed database integrates and works with
Access using Variant data types. Therefore, if you specify explicit
data types, VBA code needs to convert data types, and this may lead to
slower performance of the application."

What say other experts?

Regards,
Bob
Click to expand...
 
Back
Top