How does VBA assign doubles to integers?

[RickOlson]

In VBA it looks like it rounds. If I have the function

Function myint(x As Double) As Integer
myint = x
End Function

then myint(3.9) yields 4 instead of the value of 3 that I was expecting.

I can't find a place in the documentation where this is documented. Can someone point me in the right direction? I'm using Office 2010, and even a pointer to help pages would be a big help. I'd like to see whether there are some other assumptions I'm making that might not hold in VBA.

Thanks

 

[shg]

Your statement is effectively

myint = CInt(x)

... which rounds. See Help for CInt.

 

[Rick Rothstein]

See Help for CInt.

To follow up on shg's comment, make note of the method used to round numbers whose fractional component is exactly one-half...

"When the fractional part is exactly 0.5, CInt and CLng
always round it to the nearest even number."

This method is usually known by the name "Banker's Rounding" and is not the method of rounding you were probably taught in school (halves always round up). If you need should need the rounding to be what I call "Normal Rounding", you can do this...

myint = Format(x, "0")

All functions and coercions in VB use Banker's Rounding except for the Format function which uses Normal Rounding. By the way, unless you have a good reason for doing so, you should consider using Long instead of Integer data types... for one reason, you won't have to worry about overflowing your variables if you numbers should get near an Integer's top end of 32767.

 

[RickOlson]

Thanks for your replies. I think I might not have been clear about my question. I used that function as an example of a more general question.

In C, Fortran and other places when real variables are converted to integers, they are truncated. So if x is declared as an integer, then the expression x=3.8 will result in x taking on the value 3.

I see that in VBA x = 3.8 would be the same as x = CInt(3.8). I'm wondering how I would have known that VBA rounds rather than truncates. I can't see anything in the Help that talks about how expressions that have real and integer variables are handled. I can fix this particular situation by using the int() function, but I was hoping to find something in writing so that I could see whether there are other assumptions I make without thinking about it that are also wrong.

Thanks again for the feedback.

 

[xenou]

VB and VBA in a nutshell by Paul Lomax is an excellent reference (book-wise). You also read the help files in Excel - they are generally good, if you know where to look. In this case, for instance, this behavior is described under the topic Type Conversion Functions.

 

[RickOlson]

I'll look for the Lomax book. Thanks for the tip. There are so many books that it can be hard to know which are good. I'll also look at some VB books. None of the books I have explicitly talk about casting.

I looked under the topic Type Conversion Function, but from what I saw, that just said how the conversion functions work. I'm looking for an explanation of the default casting behaviors when functions aren't used. I might have missed it, though; there are times when paper is superior to on-line.

Fortunately, Lomax is paper

 

[xenou]

I don't think I've ever seen documentation on how vba handles implicit data type conversions. I'll check my copy of Lomax's book tonight if I see anything but it may not exist there too. But I think this is probably the one to worry about.

Off the top of my head, some things to watch for would be checking up on how Excel handles dates. Variants are common in VBA and create some interesting twists. Also, VBA allows for any number to be an upper bound or lower bound of an array, which is unusual - and in some cases an array will have a lower bound of 1 (VBA collections are indexed starting at 1, and assignments of range values directly to variant array is by default 1-based - this is more or less in line with the Excel motif if rows and columns also starting at 1). Another characteristic of VBA is having default properties.

Actually I'm pretty sure this conversion issue is a problem in something I wrote this year too - I've assumed that the numbers are truncated too and I think I was relying on that in one case too. I was kicking myself when I saw your post.

If you want a lot of nitty gritty Chip Pearson's Excel website is a fount of knowledge.