Modulus and the Mod function

  • Thread starter Thread starter Mark Woolven
  • Start date Start date
M

Mark Woolven

The Excel formula MOD(436.15,1) returns the value 0.15. However the Mod
function in VBA returns the value 0, as (quoting from online help) "Usually,
the data type of result is a Byte, Byte variant, Integer, Integer variant,
Long, or Variant containing a Long, regardless of whether or not result is a
whole number."

Can any kind soul indicate how I might achieve the result that the Excel
formula gives? I am basically trying to get my code to split 436.15 into two
elements, 436 and 15, so that I can print the two values to a text file
using the appropriate decimal delimiter for any given country.

Thanks in advance for any help.

Mark Woolven
 
You could try something like this:

Function GetDecimalPart(RealValue as Double) as Double

Dim IntTemp as Integer

'Because it truncates, IntTemp will now store the Whole
number part of RealValue
IntTemp = RealValue

'Now subtract the whole number part of RealValue from
RealValue, to get the Decimal part...
GetDecimalPart = RealValue - IntTemp

end function
 
One way:

Public Function Frac(ByVal inVal As Variant) As Variant
Select Case VarType(inVal)
Case vbSingle, vbDouble, vbCurrency, _
vbDate, vbInteger, vbLong
Frac = inVal - Fix(inVal)
Case Else
Frac = CVErr(xlErrValue)
End Select
End Function 'Frac()
 
Back
Top