CODEWITHSUNDEEP
excelvba
Im Hun Jung
Im Hun Jung

Reputation: 23

Basic VBA Questions (Integer)

Sub CommandButton1_Click()
    Dim x As Integer
    x = 6
    Range("A1").Value = x
End Sub

This means that you assign X as Integer, and you say the x equals to 6. And then you put the value x(=6) in cell "A1".

Sub CommandButton1_Click()
    Dim x As Double
    x = 6
    Range("A1").Value = x
End Sub

But why does the second one work also?

Upvotes: 1

Views: 171

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

TL;DR: Type conversions.

Range.Value is a Variant, which is a special data type that stores a pointer to a value, along with a description of the data type: that's how a cell can hold a Double, a String, a Boolean, or an Error value.

Anything deeper than that is irrelevant to the question at hand.

Integer is a 16-bit signed integer type that can easily fit into a Double, which is much larger than 16 bits. If you followed the .Value assignment with this:

Debug.Print TypeName(Range("A1").Value)

You would get Double in the debug output.

Somewhere in the implementation of the Range.Value property, a validation of the supplied value is performed, and if the value is acceptable, it's stored internally in the appropriate data type. If the value isn't of an acceptable data type, error 1004 is thrown. Integer being a fine numeric value, all is good.

The exact same thing happens in the second snippet: Double being a fine numeric value, all is good. And since any numeric value taken from a cell is a Variant/Double, we can reasonably infer that somewhere in the internal guts of Range, numeric values are stored as Double - although, that could very well just be an implementation detail of how the getter of the Range.Value property is implemented.

VBA was designed to work with a specific set of data types, and the type libraries of VBA host applications (e.g. Excel) were designed to accept these data types. Hence, you would have to work pretty hard to give Range.Value a value it can't deal with, using VBA code.

But before the value even gets to the Range.Value property, an implicit type conversion has aready occurred in the second snippet.

Dim x As Integer
x = 6

Here 6 is an integer literal. When VBA executes the x = 6 instruction, that 6 already has a data type - and that data type is Integer.

Dim x As Double
x = 6

Here 6 is also an integer literal, but it's assigned to a Double, which isn't the same type: an implicit type conversion occurs, and x happily takes an Integer value - because the conversion is widening.

Now consider:

Dim x As Double
x = 6#

Here 6# uses a type hint. Debug.Print TypeName(6#) prints Double: that 6# is a Double literal - no type conversion occurs here. But it's ugly.

Dim x As Double
x = CDbl(6)

Now the widening type conversion is explicit.

When an implicit conversion is narrowing instead, and the value can't fit into the needed data type...

Dim x As Integer
x = 32768 '<~ that's a Long integer literal: doesn't fit the 16 bits of an Integer

...then runtime error 6 ("Overflow") is thrown. Since every VBA numeric data type can safely be converted to a Double, every numeric value that can be supplied by VBA code, can be assigned to Range.Value.

Upvotes: 4

Alan
Alan

Reputation: 3042

Internally Excel does not use an Integer. Cells are one of four types:

  • Double precision floating point (all numbers including integers, currency, dates times etc)
  • String
  • Boolean
  • Error

Note this means all numbers are doubles.

See these references:

Data types used by Excel - "All worksheet numbers in Excel are stored as doubles"

Excel VBA internal data format and memory storage

Upvotes: 0

Related Questions