Still trying to figure out how to skip over code using Goto Statement

This post is related to:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26913245.html

I thought we'd solved the problem, but it seems that it's doing the second block of code (lines 11-24) regardless.
 
Function FindDept(AccountRef1, AccountRef2)
Dim colonPos As Integer
Dim AccountRefToUse As String
Dim TextToRight As String

If IsNull([AccountRef1]) And IsNull([AccountRef2]) Then
    FindDept = ""
GoTo EndofSection
End If

If AccountRef1 = "" Then
   AccountRefToUse = AccountRef2
Else
   AccountRefToUse = AccountRef1
End If

colonPos = InStr([AccountRefToUse], ":")
TextToRight = Right([AccountRefToUse], Len([AccountRefToUse]) - colonPos)

If colonPos = 0 Then
FindDept = [AccountRefToUse]
Else
FindDept = TextToRight
End If
EndofSection:
End Function

Open in new window


Any suggestions on getting the function to end if both of the parameters retrieved are null?
BBluAsked:
Who is Participating?
 
F IgorDeveloperCommented:
TThe condition could be:

"If IsNull([AccountRef1]) Or IsNull([AccountRef2]) Then"

if you do not use this, then AccountRef1 or AccountRef2 may be null
and you need to check it individually in the second part:


If IsNull([AccountRef1]) Or AccountRef1 = "" Then .....
0
 
Saqib Husain, SyedEngineerCommented:
Try

Function FindDept(AccountRef1, AccountRef2)
Dim colonPos As Integer
Dim AccountRefToUse As String
Dim TextToRight As String

If IsNull([AccountRef1]) And IsNull([AccountRef2]) Then
    FindDept = ""
Else
    If AccountRef1 = "" Then
       AccountRefToUse = AccountRef2
    Else
       AccountRefToUse = AccountRef1
    End If
   
    colonPos = InStr([AccountRefToUse], ":")
    TextToRight = Right([AccountRefToUse], Len([AccountRefToUse]) - colonPos)
   
    If colonPos = 0 Then
        FindDept = [AccountRefToUse]
    Else
        FindDept = TextToRight
    End If
End If
End Function
0
 
BBluAuthor Commented:
I still get an "invalid use of null" error on:

If AccountRef1 = "" Then
       AccountRefToUse = AccountRef2

It must be still executing this part even when one of the values is null
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
F IgorDeveloperCommented:
use "Exit Function" instead of "Goto EndOfSection" to exit inmediately
if the function dowes not exits you maybe forgetting something in the
main condition  "If IsNull([AccountRef1]) And IsNull([AccountRef2]) Then...."

0
 
SiddharthRoutCommented:
BBlu

One major point

When you are declaring function declare them as ... For Example

Function FindDept(AccountRef1 as String, AccountRef2 as String) As String

Now what is AccountRef1 and AccountRef2 ?

Sid
0
 
SiddharthRoutCommented:
I have reduced your function to just few lines. Could you test this for me?

Function FindDept(AccountRef1, AccountRef2) As String
    Dim colonPos As Integer
    AccountRefToUse As String, TextToRight As String

    If IsNull([AccountRef1]) And IsNull([AccountRef2]) Then Exit Function

    If IsNull([AccountRef1]) Then [AccountRefToUse] = [AccountRef2] Else [AccountRefToUse] = [AccountRef1]
    
    colonPos = InStr([AccountRefToUse], ":")
    
    TextToRight = Right([AccountRefToUse], Len([AccountRefToUse]) - colonPos)
    
    If colonPos = 0 Then FindDept = [AccountRefToUse] Else FindDept = TextToRight
End Function

Open in new window


Sid
0
 
SiddharthRoutCommented:
Also one more thing. If there is no ":" in [AccountRefToUse] then you might incorrect results because of this line of code

colonPos = InStr([AccountRefToUse], ":")

If AccountRef1 and AccountRef2 are string then you can use this code as well.

Function FindDept(AccountRef1 As String, AccountRef2 As String) As String
    Dim colonPos As Integer
    AccountRefToUse As String, TextToRight As String

    If Len(Trim(AccountRef1)) = 0 And Len(Trim(AccountRef2)) = 0 Then Exit Function

    If Len(Trim(AccountRef1)) = 0 Then AccountRefToUse = AccountRef2 Else AccountRefToUse = AccountRef1
    
    colonPos = InStr(1, AccountRefToUse, ":")
    
    TextToRight = Right(AccountRefToUse, Len(AccountRefToUse) - colonPos)
    
    If colonPos = 0 Then FindDept = AccountRefToUse Else FindDept = TextToRight
End Function

Open in new window


Sid
0
 
BBluAuthor Commented:

Thanks to you both.
It was the exclusion of the "Or" that I was missing.  I'd actually thought of that before fraigor mentioned it.  I guess that means I'm learning.  LOL. But you guys did teach a few more things (Exit Function, using as, shortening the code)  Thank you both!
0
 
BBluAuthor Commented:
Oh, here is the final code that works:

 
Function FindDept(AccountRef1, AccountRef2)
Dim colonPos As Integer
Dim AccountRefToUse As String
Dim TextToRight As String

If (IsNull([AccountRef1]) Or [AccountRef1] = "") And (IsNull([AccountRef2]) Or [AccountRef2] = "") Then
    FindDept = ""
Exit Function
End If

If AccountRef1 = "" Then
   AccountRefToUse = AccountRef2
Else
   AccountRefToUse = AccountRef1
End If

colonPos = InStr([AccountRefToUse], ":")
TextToRight = Right([AccountRefToUse], Len([AccountRefToUse]) - colonPos)

If colonPos = 0 Then
FindDept = [AccountRefToUse]
Else
FindDept = TextToRight
End If
EndofSection:
End Function

Open in new window

0
 
SiddharthRoutCommented:
BBlu: Please refer to ID: 35221406

Sid
0
 
BBluAuthor Commented:
Thanks, Guys
0
 
Patrick MatthewsCommented:
BBlu,

Glad you got your answer.  Just so you know, use of GoTo is very much discouraged, except for invoking an error handler.  The other Experts did not come out and say this, but do note how their suggestions avoid the need for GoTo by using the typical flow-of-control structures VBA provides.  As such, they are implicitly leading you away from GoTo :)

Patrick
0
 
BBluAuthor Commented:
Got it.  Thank you very much, Patrick.  Have a great weekend.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.