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...

user4039065 · Accepted Answer · 2015-08-20 03:33:54Z

4

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.

answered Aug 20, 2015 at 3:33

user4039065

Sign up to request clarification or add additional context in comments.

Comments

barrowc · Accepted Answer · 2015-08-20 03:11:13Z

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

edited Aug 20, 2015 at 3:11

answered Aug 20, 2015 at 2:43

barrowc

10.7k2 gold badges47 silver badges59 bronze badges

2 Comments

DomJack Over a year ago

This still fails for '=identity(3)'. Is there some root class I can use? I would have thought that was Object...

barrowc Over a year ago

I've added some more details about object vs. non-object in VBA

John Coleman · Accepted Answer · 2015-08-20 03:14:20Z

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.

answered Aug 20, 2015 at 3:14

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

Collectives™ on Stack Overflow

VBA Object type

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related