![]() The solution is to convert one of the operands in the multiplication to long first. In your example, the arithmetic is being performed with 16-bit integers and the result is then being converted to long but at that point it is too late, the overflow has already occurred. The second example ( conversion with CLng )works because the first number is first converted to a 32-bit type and the arithmetic is then carried out using 32-bit numbers. Since the result of multiplying these two numbers exceeds the value that can be represented with 16 bits you get an exception. In VBA, Integers are 16-bit signed types, when you perform arithmetic on 2 integers the arithmetic is carried out in 16-bits. Compiled but not tested: Private Sub CommandButton1Click () Dim sht As Worksheet Dim twp (1 To 20), carp (1 To 20), autop (1 To 20) Dim twd (1 To 20), card (1 To 20), autod (1 To 20) As Variant Dim n As Long, c (1 To 20, 1 To 6), vtype, age, mil, f. WsResults.Cells(6, 3).Value = CLng(60 * 60) * 24 * 30Ħ0, 60 and 24 all are Integer values. Your code will be much shorter if you switch to 2D arrays for m and c. In your case try using this wsResults.Cells(4, 3).Value = CLng(60 * 60) * 24 To work around this situation, type the number, like this: Dim x As Long You attempt to use a number in a calculation, and that number is coerced into an integer, but the result is larger than an integer. Make sure your assignment fits the range for the property to which it is made. An assignment to a property exceeds the maximum value the property can accept.Assign the value to a variable of a type that can hold a larger range of values. ![]() The result of an assignment, calculation, or data type conversion is too large to be represented within the range of values allowed for that type of variable.This error has the following causes and solutions: WsResults.Cells(7, 3).Value = (60 * 60 * 24 * 365)Īn overflow results when you try to make an assignment that exceeds the limitations of the target of the assignment. WsResults.Range("A1:M1000").ClearContents ![]() Public Const GameYear As Single = GameDay * 365 Public Const GameMonth As Single = GameDay * 30 Public Const GameWeek As Single = GameDay * 7 Public Const GameDay As Single = GameHour * 24 Public Const GameHour As Single = GameRound * 60 Public Const GameRound As Single = GameTick * 12 Public Const GameTick As Single = (60 / 12) / 4 I've tried closing Excel and re-opening the workbook, and I've tried putting the formula directly into the formula bar (it displayed correctly), but the VBA code still won't work.Īnybody have any ideas? Thanks! Option Explicit This is the line it errors on: wsResults.Cells(4, 3).Value = (60 * 60 * 24) I'm just trying to multiply three numbers together and put the result in a cell. Normally, that's due to an integer that ought've been a long. Getting an Error 6 - Overflow with Excel VBA.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |