VBAifelse在excel中循环返回#VALUE-VBAifelseloopinexcelreturning#VALUE

9  

Change Function Res(myval As Integer) As Integer to:

将函数Res(myval As Integer)更改为Integer:

Function Res(myval As Long) As Long

You're hitting the integer maximum.

你正在达到整数最大值。

Long stands for long integer, and you want to use it anytime your number could go above 30k.

Long代表长整数,你想在任何时候你的数字超过30k就可以使用它。

2  

You need to use long instead of integer

你需要使用long而不是整数

integer only covers from -32,768 to 32,767

整数仅涵盖-32,768至32,767

long covers -2,147,483,648 to 2,147,483,647

长封面-2,147,483,648至2,147,483,647

    Function Res(myval As Long) As Long

    Res = 0

    If ((myval > 0) And (myval <10)) Then    
       Res = myval * 100000

    ElseIf ((myval > 9) And (myval <100)) Then
        Res = myval * 10000

    ElseIf ((myval > 99) And (myval <1000)) Then 
        Res = myval * 1000

    ElseIf ((myval > 999) And (myval <10000)) Then  
        Res = myval * 100

    ElseIf ((myval > 9999) And (myval <100000)) Then
        Res = myval * 10

    ElseIf ((myval > 999999) And (myval <10000000)) Then
        Res = myval / 10

    Else
        Res = myval

    End If

    End Function

1  

Function Res(myVal as long) As Long
    Res = myVal * 10 ^ ( 6 - Len(Cstr(myVal)) )
End Function

Note - this still suffers from the same problem as the original approach:

注意 - 这仍然遇到与原始方法相同的问题:

If myVal is 9999999 (7 digits) then myVal/10 is 999999.9 and Clng(999999.9) is 10000000 (still seven digits)

如果myVal是9999999(7位),那么myVal / 10是999999.9而Clng(999999.9)是10000000(仍然是7位数)