2

I'm working with dates and I ran into a strange issue. If you multiply 160 by 1440, 60*24, vba gives an overflow error even when using a long. However, if you multiply 160 by 86440, 60*60*24, the overflow error does not occur. Does anyone have any idea as to why this would happen? I have already found a work around, but this doesn't make any sense.

Thank you for any help!

Sub test()
Dim numb As Long
numb=160*1440
numb=160*86400
End Sub

If you comment out the first line it runs without an overflow error, but with the first line in the overflow error occurs.

T.M.
  • 9,436
  • 3
  • 33
  • 57
Taylor7353
  • 43
  • 7
  • You need to write that as `numb = 160 * CLng(1440)` - you can read more about Overflow errors [here](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/overflow-error-6). Honestly those docs are a little confusing but it's the only answer I can provide! – dwirony Apr 19 '19 at 16:53
  • Thank you. I'm sorry it was worded confusingly, I tried to be as clear as possible. I learned how to code in java, I really hate the silly nature of vba. – Taylor7353 Apr 19 '19 at 17:02
  • @Taylor7353 VBA is the most hated programming language for a reason :) – Louis Apr 19 '19 at 17:04
  • An alternative to `CLng` is to use `&` (type declaration character for Long), e.g.`numb=160&*1440&` so that the compiler knows those are `Longs`. – xidgel Apr 19 '19 at 17:12
  • @Louis I would direct you towards a post by Mat's Mug: [What’s Wrong With VBA?](https://rubberduckvba.wordpress.com/2019/04/10/whats-wrong-with-vba/) – Cyril Apr 19 '19 at 18:06
  • @Cyril Thanks, I read it. I don't fully agree with everything, but he's right on one thing: most of the problems are releted to the terrible editor it has. – Louis Apr 19 '19 at 18:42
  • https://stackoverflow.com/questions/10890892/use-of-symbol-hash-in-vba-macro/10891051#10891051 – Siddharth Rout Apr 20 '19 at 19:53

1 Answers1

2

Okay this was a little confusing for me at first, but now I understand. Note that this will result in an Overflow error:

Sub Test()
    Dim numb As Long
    numb = 160 * 32767
    Debug.Print numb
End Sub

But this won't:

Sub Test()
    Dim numb As Long
    numb = 160 * 32768
    Debug.Print numb
End Sub

If your calculation result is greater than 32767 (the integer cap) and both of the numbers involved in the calculation are integers, then there is a data type conversion issue which results in the Overflow. So you have to force the conversion with CLng() to avoid the error:

Sub Test()
    Dim numb As Long
    numb = 160 * CLng(32767)
    Debug.Print numb
End Sub
dwirony
  • 5,487
  • 3
  • 21
  • 43
  • Thank you for this in depth reply. It makes perfect sense and although I dislike that this is what must be done I understand the reasoning – Taylor7353 Apr 19 '19 at 17:03
  • 1
    @DavidZemens: The `#`is for doubles. If you want to force a number to a Long, use `&`: `32767&`. See [the documentation](https://learn.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/data-types/type-characters) – Zack Apr 19 '19 at 17:19