VBA Object type

Question

I've stumbled upon some behaviour I do not understand. Consider the following function:

Function identity(n As Integer) As Integer
  identity = n
End Function

Obviously this isn't the most practically useful function, but it highlights my problem well.

This works as I would expect, simply returning the input of it is an integer, otherwise throwing an error.

However, if I change the output to be an Object, I would still expect this to work, since, as I understand it, integers are objects. Alas, instead I get an 'error in Value'.

This also occurs if both the input and output types are changed to Objects.

The function exhibits truly curious behaviour when the input is changed to an Object but the output remains an integer. If it is called from excel via '=identity(3)' it errors, but if it is called via '=identity(A1)' where A1 contains 3, it returns the correct value.

Can anyone explain what is going on? I'm new to VB, but I've programmed in many different languages previously and I've never seen anything like this...

Answer 1

If this is to serve some larger purpose, perhaps you can use the variant type.

Function identity(v As Variant) As Variant
    identity = v
End Function

?identity("K") & "E"
KE

?identity("2") + 2
 4 
?identity(2) + 2
 4 
?identity(2) & 2
22 

?identity("K") & 2
K2

?identity("K") + 2
Run-time error '13': Type mismatch

VBA is considered a loose-typed (aka 'weak-typed') programming language so adding "2" + 2 and 2 + 2 produce the same result.

Answer 2

Integers are not objects in VBA. Set implicitly sets a pointer to an object (user-defined or built-in). An Integer variable stores it's value more directly.

Answer 3

If n is an Object and identity returns an Object then in VBA:

Set identity = n

Object types need to use Set and non-object types like Integer do not use Set

edit In response to comment below, 3 is an Integer literal. Integer is not an Object type and can't be treated as one.

You may find the Variant type to be useful as it can hold both Object and non-object types. You can then use TypeName to work out exactly what you are dealing with and proceed accordingly