No olvides que en las ciencias cualquier problema casi siempre tiene diferentes resoluciones, de igual modo nosotros aquí te compartimos lo más óptimo y mejor.
Ejemplo: excel vba equivalente a la función mod de Excel
'VBA does have the 'Mod' operator:
MsgBox 5Mod2'<--displays: 1 (5 ÷ 2 has a REMAINDER of 1)'But bear in mind that it returns an integer value... always.
MsgBox 11.5Mod3'<--displays: 0 (But it should be 2.5)'And in some cases it fails completely:
MsgBox 11Mod0.4'<--Fails: Runtime error 11: division by zero'It also does not always handle negative numbers correctly:
MsgBox -11Mod3'<--displays: -2 (But it should be 1)
MsgBox 11Mod-3'<--displays: 2 (But it should be -1)'The VBA 'Mod' operator also imposes limits on the size of 'the operands. The operands are limited by the Long Integer data type,'which maxes out at 2147483647:
MsgBox (2147483647+1)Mod5'<--Fails: Runtime error 6: overflow'In contrast, the 'MOD()' worksheet function returns a Double'floating point value... always. Also the 'MOD()' worksheet function'handles negative and decimal operands correctly:
MsgBox [MOD(-11,3)] '<--displays: 1 (Correct)
MsgBox [MOD(11,-3)] '<--displays: -1 (Correct)
MsgBox [MOD(11.5,3)] '<--displays: 2.5 (Correct)
MsgBox [MOD(2147483648,5)] '<--displays: 3 (Correct)
MsgBox [MOD(11,0.4)] '<--displays: 0.199999999999999'But notice the floating point rounding error on the last example 'above. It should calculate a result of precisely: 0.2'And, calling the Excel object model is always inefficient, so it'is better to stay with pure VBA when possible.'So here is a superior pure VBA function:Function Mod2(n, divisor)
Mod2 =CDec(n)- divisor * Int(n / divisor)EndFunction'This VBA function has all of the advantages of the worksheet function'but is more precise and MUCH faster when called from VBA:'1) It can correctly return a decimal value'2) It correctly handles negative arguments'3) It uses the Decimal Variant data subtype to reduce ' floating point error'4) Because of the Decimal subtype it can handle parameters MUCH' larger than even the worksheet function can'5) It does not call the Excel object model (faster). This also' allows it to be used in other VBA environments like Word and Access
MsgBox Mod2(11,0.4)'<--displays: 0.2 (Correct, no rounding error)
MsgBox Mod2(11.5,3)'<--displays: 2.5 (Correct)
MsgBox Mod2(-11,3)'<--displays: 1 (Correct)
MsgBox Mod2(11,-3)'<--displays: -1 (Correct)
MsgBox Mod2("9851201567410588",1349)'<--displays: 948 (Correct)''Notice in the final example we pass the first argument as a string.'This is done to preserve value accuracy as the number is too large'for even a Double to represent accurately. Notice that the worksheet'function fails here, whereas the 'Mod2()' function works just fine:
MsgBox [MOD(9851201567410588,1349)] '<--Fails: Runtime error 13: type mismatch'''Reseñas y puntuaciones
Al final de todo puedes encontrar las explicaciones de otros programadores, tú asimismo puedes insertar el tuyo si te apetece.
¡Haz clic para puntuar esta entrada!
(Votos: 0 Promedio: 0)