1

From what I understood of VBA, it seems that Type characters (MS Docs - Type characters) are mostly used during the declaration process of variables, to shorten code lines and use implicit declaration while still forcing a data type.

On the other hand, Type conversion functions (MS Docs - Type conversion functions) are mostly used in calculations and value conversion to ensure type compatibility between variables.

But I have seen type characters beeing used in formulas where type conversion functions could have been. So what are the differences between these two methods ? Which one should I use and when ? What is the best practice ?

For example, what are the differences between each calculation of b in the following code:

Dim a As Integer, b As Double
a = 4
b = a# * 10.0
b = a * 10#
b = CDbl(a * 10)
b = CDbl(a) * CDbl(10)

Pardon me if this question is dumb or if it has already been answered somewhere on this site.

Vincent
  • 417
  • 3
  • 12
  • Have you seen [Use of symbol # (hash) in VBA Macro](https://stackoverflow.com/questions/10890892/use-of-symbol-hash-in-vba-macro)? – Siddharth Rout Jun 19 '21 at 16:52
  • Yes I have, and it explains well how type characters work, but does not state the differences with type conversion functions. – Vincent Jun 19 '21 at 16:53

2 Answers2

1

Please, look to the next Sub example and each line comments:

Sub testTypeCharConv()
 Dim a As Integer, b As Double
 a = 4.2                     'in order to see how Integer declaration trunks the decimals
 'b = a# * 10.2              'this will raise an error, since the variable has already been declared
 b = a * 1000                'it returns correct 4000 (4.2 is rounded at 4, being an Integer)
 'b = a * 10000              'Overflow error, because of Integer maximum value of 32,767
   b = a * 10000#            'returns 40000 due to Double conversion, the result is converted to Double
 b = CDbl(a) * 1000          'it does not change anything. 4.2 has already been trunked to 4
  b = CDbl("4.2") * 1000     'returns 4200, CDbl converting a string to a double
  b = a * 10.2               'returns 40.8
 b = CDbl(a) * CDbl(10.2)    'both conversions do not help in any way
End Sub
FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • Thank you for explaining each case. I'm always surprised how some languages performs implicit type conversion to the most precise data type, while some others stick to the explicit declaration of the variable. – Vincent Jun 19 '21 at 21:21
1

When declaring variables or functions, the type characters are the explicit, not implicit, option. They are simply a more obscure and less welcome version of As Datatype.

When using variables or functions, the type characters are not required at all. They can be kept as a personal preference, but then they must match the type with which the variable was declared (which is why they are not required in the first place), and it does not matter whether the declaration was made with a # or with an As Double. In this case, however, the code begins to look and feel like QBasic.
A notable exception is the use of some built-in functions that come with two flavours for Variant and for String. Specifying a $ there makes a difference.

And when declaring literals, why would anyone want to downgrade a literal to a function call? The only result of this is slower code, confused future developers and the inability to save the result of the expression in a Const, so when you want a 10-as-a-Double, 10# is vastly superior to CDbl(10), just like #10/19/2012 11:34:06 AM# is vastly superior to CDate("2012-10-19 11:34:06").

GSerg
  • 76,472
  • 17
  • 159
  • 346
  • Thanks a lot ! Your explanations and links are very helpful. – Vincent Jun 19 '21 at 21:33
  • In the 3rd paragraph, when comparing ```10#``` to ```CDbl(10)```, is the overtime of ```CDbl``` due to the process of memory re-allocation for a bigger variable than initially declared ? (2 bytes for ```Integer``` vs 8 bytes for ```Double```) – Vincent Jun 19 '21 at 21:34
  • No, it's for the fact that it's called at all, as compared to no call. – GSerg Jun 19 '21 at 22:16