VBA : get Long limits programmatically

Question

Is there a way in VBA to programmatically get the limits (minimum value, maximum value) of a numeric type (Long for instance) ?

Something like the numeric_limits<long>::min() in C++.

score 9 · Accepted Answer · answered Nov 02 '11 at 09:17

9

No, but they're fixed size anyway, so you can infer them directly.

Here's some info on their sizes: http://msdn.microsoft.com/en-us/library/aa164754.aspx

From the article:

The Integer and Long data types can both hold positive or negative values. The difference between them is their size: Integer variables can hold values between -32,768 and 32,767, while Long variables can range from -2,147,483,648 to 2,147,483,647. Traditionally, VBA programmers have used integers to hold small numbers, because they required less memory. In recent versions, however, VBA converts all integer values to type Long, even if they are declared as type Integer. Therefore, there is no longer a performance advantage to using Integer variables; in fact, Long variables might be slightly faster because VBA does not have to convert them.

answered Nov 02 '11 at 09:17

Polynomial

27,674
12
80
107

If you assume that all types are signed and that LenB will correctly return the size of the value in bytes (without adding any padding), you can compute their range by using `min=-2^((LenB(value)*8)-1)` and `max=(((2^((LenB(value)*8)-2))-1)*2)+1`. The more complex math in the latter is to avoid an overflow - i.e. for a 32-bit signed int compute 2^30, subtract 1, multiply by two, add 1, giving you `(2^30-1)*2+1` = `2^31-1` – Polynomial Nov 02 '11 at 09:23
Actually as even you mention in your answer the "fixed sizes" vary depending on your Office version, processor architecture and sometimes you might think even a weather. So if you build something that you would like to be reusable regardless of those changes, building a module with static values will fail you. While very raw and very inefficient, the solution suggested by @user3819867 seems to resolve that problem. What I would do (and actually I am considering it) is combining both approaches - building an improved version of function to recognize max and wrapping it in a module. – Ister Apr 14 '17 at 10:43

score 1 · Answer 2 · answered Nov 02 '11 at 09:21

1

I don't think there's a function for that. I would create a library of const values for each number type then you could reference this.

answered Nov 02 '11 at 09:21

Ash Burlaczenko

24,778
15
68
99

score 0 · Answer 3 · answered Jul 11 '17 at 09:45

For a programming platform with 32-Bit articuture: "Dim Item1 As Long", variable is 32-Bit in length. This means that each Long dimmed variable is 32-Bit. he maximum value it can contain (positive or negative) is a little over 2 billion.

    Sub sumall() 
    Dim firstRow As long
   firstRow = 5
   Dim lastRow Aslong
   lastRow = 12
  Dim aRow As long
  Dim sumall As Variant
  Dim sumResult As Variant
  sumResult = 0
  Dim previousValue As Variant

  previousValue = -1
  For aRow = firstRow To lastRow
    If Cells(aRow, 2).Value <> previousValue Then
        sumResult = Cells(aRow, 2).Value
        previousValue = Cells(aRow, 2)
    End If
  Next aRow
sumall = sumResult
End Sub

Another option for the tasc would be to use scriptingDictionary to get unique values only:

                Sub sumall()
                Dim objDictionary As Object
                Dim firstRow As Long
                firstRow = 5
                Dim lastRow As Long
                lastRow = 12
                Dim aRow As Variant
                Dim varKey As Variant
                Dim sumResult As Variant


                Set objDictionary = CreateObject("Scripting.Dictionary")

                For aRow = firstRow To lastRow
                        If objDictionary.exists(Cells(aRow, 2).Value) = False Then
                        objDictionary.Add Cells(aRow, 2).Value, True
                        End If

                Next aRow
                sumResult = 0
                For Each varKey In objDictionary.keys
                sumResult = varKey + sumResult
                Next varKey

                End Sub

it will look much better if you correct your indentation – Gahan Jul 11 '17 at 09:55 — Gahan, Jul 11 '17 at 09:55

score -1 · Answer 4 · answered Feb 24 '15 at 08:05

-1

Sub highlong()
Dim x As Long
On Error GoTo Prt
Do While True
   x = x + 1
Loop
Prt:
   MsgBox (x)
End Sub

Whatever floats your boat.

answered Feb 24 '15 at 08:05

user3819867

1,114
1
8
18

VBA : get Long limits programmatically

4 Answers4