EXCEL VBA user defined function - else without if

Question

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

John Coleman · Accepted Answer · 2016-10-27 11:53:56Z

2

@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.

answered Oct 27, 2016 at 11:53

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeongmin Raphael Cho Over a year ago

Thank you for great advice John, Will do for the next time. Using several IF statements at the same time always makes me complicated.

Vityata · Accepted Answer · 2016-10-27 11:57:02Z

2

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

edited Oct 27, 2016 at 11:57

answered Oct 27, 2016 at 11:36

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

2 Comments

Jeongmin Raphael Cho Over a year ago

Much appreciated, Vityata, your solution has worked perfectly!

Vityata Over a year ago

Good to know, +1 karma for today :) If you want, you may select the answer by clicking the left green check like this: meta.stackexchange.com/questions/5234/…

Collectives™ on Stack Overflow

EXCEL VBA user defined function - else without if

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related