Using Function within VBA Sub

Question

I'm trying to run a function within my Sub but am getting a compile error on the line x = NINOFunction() and am not sure how to get it working.

Sub CheckNINO()
    Dim NINumber As Range, NINumbers As Range
    Dim Usdrws As Long, x As Boolean

    Usdrws = Worksheets("EeeDetails").Range("C" & Rows.Count).End(xlUp).Row
    Set NINumbers = Worksheets("EeeDetails").Range("Q2:Q" & Usdrws)

    For Each NINumber In NINumbers
        x = NINOFunction(NINumber.Value)
        If x = False Then
            Range(NINumber).Copy Destination:=Worksheets("Validation").Cells(2, x)
        End If
    Next
End Sub

Function NINOFunction(sInp As String) As Boolean
    Const s1 As String = "[AaBbCcEeGgHhJjKkLlMmNnOoPpRrSsTtWwXxYyZz]"
    Const s2 As String = "[AaBbCcEeGgHhJjKkLlMmNnPpRrSsTtWwXxYyZz]"
    Const s3 As String = "######"
    Const s4 As String = "[AaBbCcDd]"

    NINO = sInp Like s1 & s2 & s3 & s4
End Function

The function is tested and works with a Boolean result.

If the result is False I want to copy the row across to a separate sheet.

Vityata · Accepted Answer · 2018-01-10 11:27:39Z

2

Your compilation error is "Argument is not optional". This is quite good error message. Thus write x = NINOFunction(arg) and put an argument sInp.

Something like x = NINOFunction ("something")

or probably you need:

x = NINOFunction (NINumber)

Furthermore, your function always returns False, because this is the default value of the Boolean function. You may consider fixing it like this:

Function NINOFunction(sInp As String) As Boolean
    Const s1 As String = "[AaBbCcEeGgHhJjKkLlMmNnOoPpRrSsTtWwXxYyZz]"
    Const s2 As String = "[AaBbCcEeGgHhJjKkLlMmNnPpRrSsTtWwXxYyZz]"
    Const s3 As String = "######"
    Const s4 As String = "[AaBbCcDd]"

    NINOFunction = sInp Like s1 & s2 & s3 & s4
End Function

edited Jan 10, 2018 at 11:27

answered Jan 10, 2018 at 11:16

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

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

4 Comments

jufg Over a year ago

Hi Vityata, thanks for the response, I'm still getting a compile error: Argument not optional error though

user4039065 Over a year ago

I was wondering about your original response. The parameter to the NINOFunction function is not optional. edit Your last reponse looks like a call to a procedure; not a function.

Vityata Over a year ago

@Jeeped - yup true. I have simply started to remove parenthesis everywhere in Subs since the last week, thus this new habit results in flaws like the one above.

jufg Over a year ago

Excellent thanks Vityata, I was wondering why they were all returning False, didn't realise I hadn't updated the Variable name at the end!!

Magnetron · Accepted Answer · 2018-01-10 11:19:49Z

0

NINOFunction takes 1 argument, which is a string, but you didn't pass any. Try x = NINOFunction("something")

answered Jan 10, 2018 at 11:19

Magnetron

8,8032 gold badges31 silver badges46 bronze badges

2 Comments

Magnetron Over a year ago

And, of course, replace "something" with the desired input string.

jufg Over a year ago

Thanks Magnetron, I was unaware that I had to assign the function like that, but it makes perfect sense!

Collectives™ on Stack Overflow

Using Function within VBA Sub

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related