0

Goal: Assign values to variables and get the product of these variables

Problem: VBA returns incorrect result

I've been using VBA for well over a decade now and this is the first time I have been unable to find an answer or solution to this online. Hope you can help!

For reference, this is for the computation of wages.

How it's set up:

  1. variables declared as PUBLIC with data type SINGLE
Public REGHOURSRATE As Single
Public REGHOURSCOUNT As Single
Public REGHOURSAMOUNT As Single
Public HourlyRate As Single
  1. values assigned to these variables by referencing cells in excel. Resulting assigned values (verified in debug) are as follows
REGHOURSRATE = 1
REGHOURSCOUNT = 57.5
HOURLYRATE = 673.47
  1. variables multiplied
REGHOURSAMOUNT = REGHOURSCOUNT * REGHOURSRATE * HourlyRate

The correct product is 38,724.525 but strangely, VBA evaluates this as 38,724.52!

This results in minor variances which, while insignificant, cause accounting concerns.

What I've tried:

  • using different data types (DOUBLE, VARIANT). These also yield the wrong result (38,724.5233154297)
  • Multiplying the variables with decimals with a factor to turn them into whole numbers. Getting the product of the variables, and then dividing the product by the factors. ends up with the same incorrect result
  • using ROUND and worksheetfunction.round to 2 decimal places. ends with the same incorrect result because it only had 2 decimal places to begin with

Am I doing anything wrong here? Or is this simply how VBA is set up?

Thank you.

EDIT: a friendly member of the community directed me to clues to the answer on a different page. what worked for me was to set the data type as CURRENCY.

user15821334
  • 1
  • 1
  • What if you use `REGHOURSAMOUNT = Application.Evalute("=" & REGHOURSCOUNT & "*" & REGHOURSRATE & "*" & HOURLYRATE)`? – Raymond Wu Nov 12 '21 at 03:29
  • If you declace *all* your variables as `Double`, the result should be okay. If you only declare `REGHOURSAMOUNT ` as Double and the rest as `Single`, you loose precision because the calculation is done as `Single` and only converted to `Double` when assigning it to the result – FunThomas Nov 12 '21 at 07:31
  • @FunThomas Tried that too, but strangely it still gives an incorrect result (38,724.5233154297). the only way I was (finally) able to get a correct assessment was to use the CURRENCY data type instead :) nevertheless, thank you for your suggestion – user15821334 Nov 15 '21 at 02:22
  • @raymondwu Thanks for your suggestion, i'll try it out next time. I have a feeling that would work since Excel had no problems evaluating it, only VBA. In this case, solved it by using CURRENCY instead. Was trying to keep the formulas as clean looking as possible :) – user15821334 Nov 15 '21 at 02:23

0 Answers0