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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.