2

My questions is somewhat related to this post.

However, let's say I want to use Excel's built-in MOD (modulo) function in VBA. This site suggests that using Application.WorksheetFunction.[insert name of Excel function] would provide the desired effect.

However, Application.WorksheetFunction.MOD does not work.

In fact, when I simply type Application.WorksheetFunction. into VBA, a dropdown menu appears providing a list of function names to choose from, but MOD is not provided in this list!

2 questions:

  1. What's going on here?
  2. How do I actually use Excel's built-in MOD function in VBA?

Note: I started down this path b/c I'm trying to get 1.7 Mod 0.5 to equal 0.2 using VBA's Mod function, but it produces #VALUE! in the resulting Excel cell I apply my VBA function to. However, if I type MOD(1.7,0.5) directly into Excel, I get the correct answer (i.e., 0.2)

Adrian Mole
  • 49,934
  • 160
  • 51
  • 83
theforestecologist
  • 4,667
  • 5
  • 54
  • 91
  • `Mod` is a valid operator in `vba`. e.g. `123 Mod 11 = 2` – Uri Goren Dec 06 '18 at 22:37
  • @UriGoren Then why does using `1.7 Mod 0.5` in VBA ***not*** work while typing `MOD(1.7,0.5)` directly into Excel *does*? (the answer should be 0.2) – theforestecologist Dec 06 '18 at 22:52
  • As a quick reference answer to my last comment: as QHarr points out in his [answer](https://stackoverflow.com/a/53660665/4581200) the reason `1.7 Mod 0.5` doesn't work is because VBA's `Mod` only works with integers. I'd have to use `Evaluate("Mod(1.7,0.5)")` to make this work in VBA. – theforestecologist Dec 06 '18 at 23:32

1 Answers1

5

You use as is since it is an operator (with integers). Use evaluate if you want to use doubles etc e.g.

E.g. 

Debug.Print 2 Mod 12
Debug.Print Evaluate("Mod(1.7,0.5)")
Dim x As Double, y As Double
x = 1.7
y = 0.5
Debug.Print Evaluate("Mod(" & x & "," & y & ")")

See documentation. There are limitations with large numbers. A common workaround, from Microsoft, is:

=number-(INT(number/divisor)*divisor)
QHarr
  • 83,427
  • 12
  • 54
  • 101