VBA if else loop in excel returning #VALUE

Question

Hi All in new to using VBA in excel and was trying to create a function that would look at a number and return it as a six digit number.

The function I wrote to try and accomplish this is below but when I use the command =Res(A1) in a cell I just get a #VALUE! as the answer.

The value in cell one at the moment is 30508.

Any help anyone could offer to resolve this would be greatly appreciated. Thanks Guys.

Function Res(myval As Integer) As Integer

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

Answer 1

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

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.

Answer 2

You need to use long instead of integer

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

long covers -2,147,483,648 to 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