CODEWITHSUNDEEP
vbaexcelif-statementuser-defined-functions
Jeongmin Raphael Cho
Jeongmin Raphael Cho

Reputation: 1

EXCEL VBA user defined function - else without if

The attached code is run on VBA, but I do not understand why there is an error says else without if or if without end if. I am pretty sure that I have matched every end if with if statement.

Sub teee() is just for testing the decimalize function. It would be greatly check the code and tell me what is wrong with my code... I am almost close to complete a project if I can troubleshoot this function.

Sub teee()

sss = "-1-21+"

MsgBox (decimalize(sss))


End Sub

Function decimalize(s As Variant) As Long

Dim checkers As Variant
Dim ab As Long
Dim leftnum As Long
Dim rightnum As Long
Dim poneg As Integer


checkers = s
ab = 0
leftnum = 0
rigntnum = 0
poneg = 0

'Positive payup or negative payup

If Left(checkers, 1) = "-" Then
poneg = 1
lencheckers = Len(checkers)
checkers = Mid(checkers, 2, lencheckers - 1)

Else: poneg = 0
End If


startp = InStr(checkers, "-")


If startp = 2 Then leftnum = Left(checkers, 1)
ElseIf startp = 3 Then leftnum = Left(checkers, 2)
ElseIf startp = 4 Then leftnum = Left(checkers, 3)
End If



rightnum = Mid(checkers, startp + 1, 2)


If InStr(checkers, "+") > 0 Then
ab = 0.5
ElseIf InStr(checkers, "1/4") > 0 Then
ab = 0.25
ElseIf InStr(checkers, "1/8") > 0 Then
ab = 0.125
End If

rightnum = rightnum + ab


If poneg = 0 Then
decimalize = rightnum + leftnum * 32
ElseIf poneg = 1 Then
decimalize = (rightnum + leftnum * 32) * -1
End If


End Function

Many Thanks in advance

Upvotes: 0

Views: 206

Answers (2)

Vityata
Vityata

Reputation: 43595

Change it like this:

If startp = 2 Then
    leftnum = Left(checkers, 1)
ElseIf startp = 3 Then leftnum = Left(checkers, 2)
ElseIf startp = 4 Then leftnum = Left(checkers, 3)
End If

Info: When you write the result after the "then" on the same line, should not write end if. Thus, VBA does not understand where the next ElseIf is coming from.

Pretty much you are allowed to use the following two examples:

'Example 1 (no end if here)
if startp = 2 then leftnum = Left(checkers,1)

'Example 2 (you need end if here)
if startp = 2 then
    leftnum = Left(checkers,1)
end if

Upvotes: 2

John Coleman
John Coleman

Reputation: 52008

@Vityata showed one way to eliminate that particular bug. Another way is to avoid If altogether and use a Select Case. The resulting code is somewhat more readable:

Select Case startp
    Case 2: leftnum = Left(checkers, 1)
    Case 3: leftnum = Left(checkers, 2)
    Case 4: leftnum = Left(checkers, 3)
End Select

Also, You have an arbitrary pattern of declaring some variables but not others, and you have at least one variable typo: rigntnum = 0 should almost certainly be rightnum = 0. You really need to use Option Explicitat the top of all of your modules (also, enable Require Variable Declaration in the VBA editor options). That will help you write code that isn't prone to random bugs.

Upvotes: 2

Related Questions