Results 1 to 7 of 7

Thread: Numbers, Numbers, Numbers

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2015
    Location
    Colorado USA
    Posts
    261

    Numbers, Numbers, Numbers

    This package contains a large set of library routines I have built over the years to do many things related to numbers. These routines run in VB6 as well as 32 or 64-bit VBA. As you know, there are often multiple ways of solving a problem. I have been building these utilities for 20+ years and I often work on speed, accuracy and functionality for all of them. If you have any procedures that you think are “better” than those I have in this package please let me know so I can upgrade the library.

    A lot of the routines use normal VB code. For some I have used RtlMoveMemory (many of you use an alias of CopyMemory for this Windows API function) to facilitate things like shifting bits, copying to other data types (e.g., getting the top byte of a Long variable into a Byte) etc.
    In general I have found that I do not include an entire module but rather use specific ones you need in my code.

    The package consists of 9 modules and is there are no dependencies. Most of the subs and functions are self-explanatory but each is briefly discussed below. BTW, if you use any of this in Excel as User Defined Functions, you can greatly improve the time lag in the workbook by putting the “Application.Volatile False” at the top of whatever function(s) are using in the spreadsheet.

    At the end of this document are instructions for running the sample code.

    mBaseConversions

    This module allows for conversion between regular numbers, hexadecimal and binary.

    This module makes use of a variable I call UVar which represents an unsigned Currency which allows us to use all 64 bits of the number. If you are using 64-bit VBA then you can also think of UVar as an unsigned LongLong (also 64 bits). A UVar can be a Byte, Integer, Long, Currency, Single, Double, Decimal (part of a Variant) and a LongLong (64-bit only).

    What you submit to this function (and the others in this module) are treated as unsigned values. As you know, VB uses the top bit of scalar variables to designate the sign. We ignore that and treat the topmost bit as just another bit. For example, the hex value of -1 as a Long is &HFFFF (i.e., all bits are set). BTW, you can’t avoid the top bit being used for the sign. It would be tempting to think you can take and integer value of -1 and use the function to convert it to a Long that the full 16 bits would go to the Long. Unfortunately, VB doesn’t do that. Instead, VB converts the Integer -1 to a Long -1 (all 32 bits in the Long are set whereas only 16 bits were set in the Integer. For normal use this is okay but when we want to use all of the bits in hex or binary, this is a problem. Other than a Byte, VB doesn’t have any unsigned variables so that’s what we deal with here.

    One interesting thing occurs with the Currency variable. Even though it is an integer type variable, it has a decimal component. VB does this by taking the value given it an multiplying by 10,000 and then it uses the lower bits to do the fractional part. Internally it is just a 64-bit value and the multiplying and dividing by 10,000 is done when a value is assigned or the conversion function CCur is used. We treat a Currency value as an unsigned 64-bit value. If you have a regular Currency variable and you want to feed it to UVar2Hex or UVar2Bin then you should first divide it by 10,000. Likewise, if you use Hex2UVar or Bin2UVar and you want to use the return Currency value for other calculations you should multiply it by 10,000.
    If you use this in 64-bit Office you have access to a signed 64-bit variable called a LongLong that does not have the 10,000 factor that the Currency data type has. The only thing to watch out for is that it is a signed variable (i.e., the topmost of the 64 bits is the sign bit).

    UVar can also be a Single, Double or a Decimal value but note that when you use UVar2Hex or UVar2Bin with a negative number it gets converted to a signed value. For example, if you have a Double variable with a value of -1, when it gets converted it will become an integer (Currency or LongLong) with a value of -1. If that’s what you wanted that’s fine but just know that with anything other than a Byte data type, you have to be aware that VB messes around with the topmost bit. It would be nice if VB had unsigned variables but it doesn’t so this is why I created this set or procedures.

    Function UVar2Hex – Return a hex string from the UVar. The return will have no leading zeroes (e.g., Feeding it a Long value of 10000000& returns “98 9680” and not “0098 9680”). You can determine whether to group the return characters, and if so, how much (the example in the previous was based on the default grouping of 4 characters).

    The function specifies the input variable is a Variant but you can use whatever data type you want without first converting it to a variant (I believe VB does that internally).

    Function Hex2UVar – Convert a hex string (up to 16 characters (64-bit)) into an unsigned value. You can specify a data type or take the default which is to determine the data type from the size of the value. For example, if you pass “F” as the hex string, you could specify for it to be returned as a byte, integer, long, etc. but if you go with the default it will be returned as a byte inside the variant.

    Function UVar2Bin – Converts an unsigned variable into its binary equivalent (as a string). For a Byte, Integer, Long, Currency, LongLong, Single, Double or Decimal returns the string representation of the binary number (base 2). Since the routine uses an unsigned value, if you use a Currency then it should first be divided by 10,000 (or as part of the call) to use all of the bits.

    There is an optional parameter named RndType (optional) determines the size of the return number (higher bits are discarded from return). For example, if you pass an integer with a value of 111, this function will return “00000000 01101111” but if you set RndType to 1 to indicate a Byte, the function will return” 01101111”. You can specify what character (or none at all) to sue as the leading character. For example, if you specify no leading character, the return from the previous example will be “1101111”. Finally, you can set the grouping. The previous examples use a grouping of 8 characters. If you specify 0 there is no grouping and the string is returned without any spacing characters.

    Function Bin2UVar - Returns the variable related to the binary string of up to 64 characters (1s and 0s). Must specify the variant variable type in "VarType" (vbByte, vbInteger, vbLong, vbCurrency or vbLongLong) and the return is adjusted for the type of variable. If you do not specify this value then the function determines it from the length of the input binary string (1-8 byte, 9-16 integer, 17-32 long, 33-64 currency or longlong).

    Function Hex2Bin - Converts a hex string into its binary counterpart. As discussed above, you can also specify the leading character (defaults to 0) or you can not have leading zeroes by specifying “”. You can also set the grouping of binary characters in the return (default 8 characters which is 1 byte).

    Function Bin2Hex - Converts a binary string of up to 64 characters (all 0's and 1's) into a hex string.

    Module mBaseConversions1

    The previous module has very flexible routines for converting between base 10, hexadecimal and binary numbers. If you want type-specific solutions that have fewer frills, the following conversions could be used.

    Byte2Hex – Returns the hexadecimal representation of a Byte with optional leading 0’s.

    Int2Hex – Converts an Integer into a Hex.

    Long2Curr – Converts an unsigned Long to a Currency.

    Long2UCurr – Returns an unsigned Currency with the Long in the low order bytes.

    Long2Bin1 – Converts a Long into a binary string. Same functionality as Bin2Hex in module mBaseConversions but uses different logic & has a different name to avoid duplicate names in the same project.

    Long2Hex – Converts a Long variable into a Hex string.

    Curr2Hex – Converts a Currency into a hex string.

    Curr2Bin – Converts a Currency into a binary string.

    UCurr2Hex - An unsigned Currency (if it is a “normal” Currency, it should be divided by 10,000). This function converts this unsigned Currency into a Hex string.

    Bin2Byte – Converts a binary string into a Byte.

    Bin2Int – Converts a binary string into an unsigned integer (uses last 16 bits).

    Bin2Long – Converts a binary string into a Long.

    Bin2Curr – Converts a binary string into a Currency. Unlike Bin2UVar, this one returns the Currency without using the last 4 bits (i.e. not divided by 10,000).

    Bin2UCurr – Converts a binary string into the unsigned Currency.

    Bin2Hex1 – Converts a binary string into its hex equivalent. Same functionality as Bin2Hex in module mBaseConversions but uses different logic & has a different name to avoid duplicate names in the same project.

    Hex2Curr – Converts a hex string into a Currency.

    Double2Hex – Converts a Double into its hex equivalent.


    If you are using 64-bit VBA you also have the following procedures:

    Long2LL – Converts an unsigned Long into an unsigned LongLong.

    LLToHex – Converts an unsigned LongLong into its Hex equivalent.

    Bin2LL – Converts a binary string into an unsigned LongLong (string, max 62 chars)

    Hex2LL – Converts a hex String (up to 15 characters) into an unsigned LongLong.

    LL2Hex – Converts an unsigned LongLong into hex.

    LL2Bin – Converts an unsigned LongLong into a binary string.


    Module mDataTypeConversions

    This module works with sub-parts of variables. For example, one function takes all 4 bytes of a Long and puts them into 4 Byte variables.

    Bytes2Int – Puts 2 Bytes into the 2 bytes of an Integer.

    Bytes2Long – Puts 4 Bytes into one Long.

    Bytes2UCurr – Combines 8 Bytes into a Currency (not multiplied by 10,000).

    Int2Bytes – Puts the 2 bytes of an Integer into 2 Bytes.

    Ints2Long – Puts 2 Integers into a Long.

    Ints2UCurr – Combines 4 Integers into a Currency (not multiplied by 10,000).

    Int2LoByte - Returns the low byte (least significant byte) of "Integer1"

    Int2HiByte - Returns the high byte (most significant byte) of "Integer1"

    Long2Byte - Puts the specified byte (1=least significant byte, 4=most significant byte from a Long into a Byte.

    Long2Bytes – Puts the 4 bytes of a Long into 4 Bytes.

    Long2Ints – Puts the two integers making up the most and least significant words of a Long into 2 Integers.

    LongToUCurr - Takes a long (unsigned but contained in a signed long) like from an API call & returns it in a U64 (NOT multiplied by 10,000). Note – the top 4 bytes of the U64 value will always be 0 by definition.

    Longs2UCurr – Combines 2 Words into a Currency (not multiplied by 10,000).

    Long2LoWord - Returns the low integer (least significant word) of a Long into an Integer.

    Long2HiWord - Returns the high integer (most significant word) of a Long into an Integer.

    Long2CurrLo – Puts a Long into the low 4 bytes of a Currency (hi bits are 0).

    Curr2UCurr - Takes a Currency that VB has already multiplied by 10000. Existing fractional part is discarded.

    UCurr2Bytes – Puts the 8 bytes of a Currency (not divided by 10,000) into 8 Bytes.

    UCurr2Ints – Puts the 8 bytes of a Currency (not divided by 10,000) into 4 Integers.

    UCurr2Longs – Puts the 8 bytes of a Currency (not divided by 10,000) into 2 Longs.

    UCurr2Byte – Puts the selected byte of a Currency into a Byte.

    UCurr2Int – Puts the selected word of a Currency into an Integer.

    UCurr2LoLong - Returns the low long (least significant long) of a Currency.

    UCurr2HiLong - Returns the high long (most significant long) of a Currency.

    UCurrToCurr - Takes a 64-bit value that VB has NOT multiplied by 10,000 and does that to the number (for display & non-bit calculations).

    If you are using 64-bit VBA you also have the following procedures:

    LL2Bytes – Puts the 8 bytes of a LongLong into 8 Bytes.

    LL2Ints – Puts the 8 bytes of a LongLong into 4 Integers.

    LL2Longs – Puts the 8 bytes of a LongLong into 2 Longs.

    Bytes2LL – Puts 8 Bytes into a LongLong.

    Ints2LL – Combines 4 Integers into a LongLong.

    Longs2LL –Combines 2 Longs into a LongLong.

    Long2LLLo – Puts the 4 bytes of a Long into the low 4 bits of a LongLong (hi 4 bytes are 0’d).

    LL2Byte - Puts the specified byte (1=least significant byte, 8=most significant byte out of a LongLong into a Byte.

    LL2Int - Puts the specified integer (1=least significant integer, 4=most significant integer out of a LongLong into an Integer.

    LL2LoLong - Puts the low long (least significant long) of a LongLong into a Long.

    LL2HiLong – Puts the most significant 4 bytes of a LongLong into a Long.


    Numbers.zip

    {Continued in next post}
    Last edited by MountainMan; Jan 27th, 2021 at 02:32 AM.

  2. #2

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2015
    Location
    Colorado USA
    Posts
    261

    Re: Numbers, Numbers, Numbers

    Module mFlip

    This module enables you to flip bytes, integers and long parts of larger variables.

    SwapEndian - In a long, returns byte 0 in byte 3, byte 1 in byte 2, byte 2 in byte 1 and byte 3 in byte 0.

    SwapEndianInteger - Swaps bytes 1 and2 in an integer.

    FlipCurrencyLongs - Swaps the high and low longs in a Currency.

    FlipCurrencyIntegers – Flips the 4 integers contained in a Currency.

    FlipCurrencyBytes – Flips all 8 bytes in a Currency.

    FlipLongIntegers – Flips the integers in a Long.

    FlipLongBytes – Flips all of the bytes in a Long.

    FlipIntegerBytes – Flips the high and low bytes of an Integer.

    If you are using 64-bit VBA you also have the following procedures:

    FlipLLLongs - (Orig As LongLong) As LongLong

    FlipLLIntegers - (Orig As LongLong) As LongLong

    FlipLLBytes - (Orig As LongLong) As LongLong


    Module mNumbers

    This module has a wide array of procedures.

    Deg2Rad - Converts degrees to radians. For Excel users this is equivalent to worksheet function RADIANS & WorksheetFunction.Radians except using more accurate CDec conversion of variant.

    Rad2Deg - Converts radians to degrees. Equivalent to the Excel worksheet function DEGREES & WorksheetFunction.Degrees except using more accurate CDec conversion of variant.

    LogN - Converts a decimal number to a Log, given a specified Base (default base 10).

    Log10 – Returns the base 10 logarithm.

    HyperbolicSine - Calculates hyperbolic sine using the Exp() function.

    dRound - Rounds a number to a specified number of decimal places. 0.5 is rounded up

    RoundSimple - Rounds a number to a specified number of decimal places. 0.5 is rounded up. Same as worksheet function ROUND & WorksheetFunction.Round.

    RandomShuffle - Shuffles random numbers. Resulting Long array is filled with whole numbers from1 to the specified number of items without duplicates. For example, if Items = 5, the array will randomly have the numbers 1,2,3,4,5 as array elements.

    GreatestCommonFactor - Returns the largest number which will evenly divide into the 2 specified Longs.

    LowestCommonMultiple - Returns the smallest number of which both specified integers are factors.
    IsPrime – Returns True if a given number is Prime (treats negative numbers and positive numbers the same.)

    Factorial - Non-recursive factorial of a Long from 0 to 170, same as worksheet function FACT but 1.9x faster.

    AreaofCircle - Return the area of a circle (Pi * Radius ^ 2).

    AreaOfSphere - Return the area of a sphere (4 * Pi * Radius ^ 2).

    AreaOfRectangle - Return the area of a rectangle.

    AreaOfTrapezoid - Return the area of a trapezoid.

    VolOfPyramid - Return the volume of a pyramid.

    VolOfSphere - Return the volume of a sphere.

    AssetDepreciation - Calculates asset depreciation.

    CalcPayment - Calculates payments using the Pmt function.

    NetPresentValue - Calculates net present value of an array of cash flows.

    FtoC - Returns temperature in Celcius given a temperature in Fahrenheit. Can use Convert(xx,"F","C") instead if using Analysis ToolPak in Excel.

    CtoF - Returns temperature in Fahrenheit given a temperature in Celcius. Can use Convert(xx,"C","F") instead if using Analysis ToolPak in Excel.

    Quadratic - Solves the ax^2 + bx + c = 0 and returns the # of roots (0 if none, 1 if 1, or 2 if 2). If successful then Root1 and Root2 will contain the answers.

    Permutations – Calculates the permutation of a set of numbers with or without repeats allowed. This function and the Combinations function below are from https://www.vbforums.com/showthread....ability-in-VB6.

    Combinations – Calculates the combinations of a set of numbers with or without repeats allowed.


    Module mPointerMath

    Even though we all know VB6 & VBA don’t use pointers (!), if you did then doing pointer math is not necessarily straightforward. These calculations are not complicated for 64-bit VBA but are for 32-bit VB6 and VBA.

    PointerMath – Add or subtract one pointer from another. There are 2 versions of this function which are controlled by a conditional compilation directive, i.e., the 64-bit version is only used if we detect the compilation is occurring on 64-bit VBA. In Win64 the call parameters and the return are all LongPtr but in the 32-bit version the call parameters and the return are all Long.


    Module mSigDigits

    VB6/VBA Double data types have up to 17 digits, far in excess of the precision of real-world data. This module enables you to reduce the number of significant digits in a number to whatever you need.

    FindSigDig – Returns the number of significant digits in the supplied number.

    Round2SigDig - Rounds a real number to a specified # of significant digits [enclose single in CDbl()]. The result is the function return.

    SigDig - Same as Round2SigDig above but the result is not a function but instead replaces the incoming number.

    Round2SigDigStr1 - Returns a string that is essentially the same as the number returned by Round2SigDig above. You can specify if you want the return string to be with or without an exponent.

    ReduceSigDig – Finds the number of significant digits for the supplied number and then reduces it by the number you specify. For example, 3.3333 has 5 significant digits and if we specified to reduce this number by 2 then the return would be 3.33.


    Module mSort

    The module contains 3 sorting procedures and some array functions.

    Sort - This technique uses the recursive Quicksort algorithm to perform a sort on a one-dimensional array.

    Sort2D – Sorts a two-dimensional array using the recursive Quicksort algorithm.

    StringSort – Sorts a 1-D array of strings. Optionally you can set the sort to be case sensitive (or not) and whether the sort is increasing or decreasing order. This sort is fast because it uses a special technique to swap pointers to strings instead of copying the strings themselves and because it sue the QuickSort algorithm.

    ArrayMin - Minimum value for a 1-D array which could be longs, doubles, etc. (doesn't have to be a variant array). There is a VB6 version and one for Excel that uses the worksheet Min function.

    ArrayMax - Minimum value for a 1-D array which could be longs, doubles, etc. (doesn't have to be a variant array). There is a VB6 version and one for Excel that uses the worksheet Max function.

    ArrayMedian - Return the Median value in an array.

    ArrayAverage - Return the average value from an array.

    ArrayStandardDeviation - Calculates standard deviation for an array.

    ArrayMode - Returns the Mode or most common value of an array. In the case of a tie, the smallest number is the mode.




    Module mStatistics

    This module contains various routines for regression analysis.

    LinearInterpolation - Given 2 X,Y pairs and a 3rd X, returns the 3rd Y based on a linear interpolation / extrapolation. Also returns the slope and the y-intercept.

    LinearFit - Returns the values Slope, Intercept & r for y = Slope * x + Intercept using a linear curve fit.

    LinearProjection - Once you have a linear curve fit from LinearFit above, supply a new X-value and this function returns the estimated Y-value.

    ExponentialFit - Returns the values A & s for y = As^x as well as the correlation coefficient

    Function ExponentialProjection - Once you have an exponential curve fit from ExponentialFit above, supply a new X-value and this function returns the estimated Y-value.

    LogFit - For the equation y = a + b ln x, this returns a & b as well as the correlation coefficient.

    LogProjection - Once you have an natural log curve fit from LogFit above, supply a new X-value and this function returns the estimated Y-value.


    Module mVar2Str

    This module contains 2 miscellaneous string functions.

    VarString - Return formatted string for the variant type. An Integer, Long or LongLong returns a comma-separated value (e.g., 30000 will be returned as “30,000”. A Single, Double or Decimal will be returned using exponential notation with 7 significant digits. A Currency will be returned with a currency sign and 2 decimal places. A Date, String, Boolean or Byte will be returned using the CStr function.

    A call parameter will be returned as a string based on the type of data supplied. For example, supplying an Integer with a value of 30000 will cause this parameter to return “Integer”. Also, if the function detects that the supplied variable is an array, the type (Integer etc.) will have “ (array)” appended to it.

    Num2Str - Takes a number and converts it into text like is seen on checks. For example, 24.95 gets converted to Twenty-Four and 95/100.


    Test Module

    There are so many procedures in this package that I cold have spent a couple of months just writing “pretty” output. So I made a simple module (zMain) that includes a sample for each procedure that lists to the Immediate Window the inputs and outputs for each procedure in the project followed by a Stop statement after each one. Run the program from within the IDE (can’t do it in a compiled version since the compiled version has no Stop or Immediate Window). Open the Immediate window by pressing Ctrl-G. After each sample you will have a listing to the Immediate window and also you can use the Local Variables window to see the data or just hold the mouse pointer over the variable(s) you want to see.

  3. #3
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: Numbers, Numbers, Numbers

    Quote Originally Posted by MountainMan View Post
    In general I have found that I do not include an entire module but rather use specific ones you need in my code.
    Btw, no need to go over manually removing unused routines because the linker already does this to get the final executable to *not* include unused code from .bas modules.

    For .cls/.frm/.ctl/class-based modules the compiler already creates a static module's VTable with references to all the routines in the module (including the private ones) so the linker is helpless reducing the bloat here.

    cheers,
    </wqw>

  4. #4
    PowerPoster
    Join Date
    Jul 2010
    Location
    NYC
    Posts
    5,647

    Re: Numbers, Numbers, Numbers

    Your Num2Str function would seem to only support (1 trillion - 1)?

    I have a function someone wrote that supports no maximum whatsoever, but it's written in perl that was hosted on a website, and I don't know perl at all, and can't decipher it well enough to port

    So I wrote one myself that at least supports up to (1 milliauntillion - 1) (1 milliatillion = 10^3003, 1 milliauntillion = 10^3006)

    (too large to put in comment since it manually enumerates the prefixes)

    I'm attaching the Perl one too if you ever wanted a go. You could throw 10^10696276510359811 at that baby and get one duocenduotriginmilliamilliamilliamilliaduononaginmilliamilliamilliacenseptuaginmilliamilliacennovemd ecmillianongensextrigintillion.
    Attached Files Attached Files

  5. #5

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2015
    Location
    Colorado USA
    Posts
    261

    Re: Numbers, Numbers, Numbers

    My number to string routine that produces text like "Twenty Four and 95/100" was really to write checks. I thought a trillion dollars would be sufficiently large...

  7. #7
    PowerPoster
    Join Date
    Jul 2010
    Location
    NYC
    Posts
    5,647

    Re: Numbers, Numbers, Numbers

    Quote Originally Posted by wqweto View Post
    @fafalone: Check out this gist on how to produce huge string arrays by splitting concatenated multi-row consts.

    cheers,
    </wqw>
    Indeed it's not that I couldn't create the array, it's manually writing out each and every number. The perl script attached implements the naming rules themselves, so has no upper limit, while my array creating strategy is limited by how many you feel like writing out.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width