VBA Return Value from Function

[avd88]

Hello,

Trying to build a counter function I will be using in a loop to check for 4 different requirements and check you at least have 3 of those requirements or how many you attained. As you can see in my code below the x variable will be the variable I'm using in my For Loop so I can use the code with each row in my list. Then I need to return the value of Count and set it to another variable called MileCount outside of the function. Can't figure out what's the equivalent of a return statement in VBA. Can someone help me out on this one?

Dim MileCount as Integer
MileCount = MileCounter()

Function MileCounter(x) As Integer
    Dim Count As Integer
    Count = 0

    'D Milestones
    If Sheets("Attainment").Cells(x, 9).Value >= DMile1Req Then
        Count = Count + 1
    End If
    
    If Sheets("Attainment").Cells(x, 11).Value >= DMile2Req Then
        Count = Count + 1
    End If
    
    'F Milestones
    If Sheets("Attainment").Cells(x, 21).Value >= FMile1Req Then
        Count = Count + 1
    End If
    
    If Sheets("Attainment").Cells(x, 23).Value >= FMile2Req Then
        Count = Count + 1
    End If
    
    return Count
    Exit Function
    

End Function

 

[Joe4]

You don't use "return" in Excel function.
You set the value of the Function name to the value you want, i.e.

MileCounter = Count

Though you should NEVER use reserved words like "COUNT" as the name of variables (functions or procedures).
Using reserved words can cause errors and unexpected results.

 

[avd88]

You don't use "return" in Excel function.
You set the value of the Function name to the value you want, i.e.

MileCounter = Count

Though you should NEVER use reserved words like "COUNT" as the name of variables (functions or procedures).
Using reserved words can cause errors and unexpected results.

Thanks for your reply Joe. I'm actually trying this which I believe uses that logic but can't seem to get it to work. I set the values in column 35 to be equal to MileCount so I could check that the counter was working properly but I'm not getting any values.:

    Dim lastrow As Long
 Dim i As Integer
 
 lastrow = Sheets("Attainment").Cells(Rows.Count, 1).End(xlUp).Row
 
 For i = 4 To lastrow
    
    Dim MileCount As Integer
    MileCount = MileCounter(i)
    
    If MileCount <= 1 Then
        Sheets("Attainment").Cells(i, 12).Value = 0
        Sheets("Attainment").Cells(i, 24).Value = 0
        Sheets("Attainment").Cells(i, 32).Value = "Y"
        Sheets("Attainment").Cells(i, 35).Value = MileCount

        GoTo Last
    End If
Last:
Next i

Function MileCounter(x As Integer) As Integer
    MileCounter = 0

    'D Milestones
    If Sheets("Attainment").Cells(x, 9).Value >= DMile1Req Then
        MileCounter = MileCounter + 1
    End If
    
    If Sheets("Attainment").Cells(x, 11).Value >= DMile2Req Then
        MileCounter = MileCounter + 1
    End If
    
    'F Milestones
    If Sheets("Attainment").Cells(x, 21).Value >= FMile1Req Then
        MileCounter = MileCounter + 1
    End If
    
    If Sheets("Attainment").Cells(x, 23).Value >= FMile2Req Then
        MileCounter = MileCounter + 1
    End If
        

End Function

 

[Joe4]

Except for global/public variable declarations (if any), all your code should be contained within Sub Procedures or Functions.
You cannot just have free-floating blocks of code outside of one of those two types of code containders.

So you would need to probably put your VBA code up above your Function within a Sub Procedure.

 

[avd88]

Thanks for your reply, the top part of the code is contained within a Sub. I didn't include it in there because it's just a small part of my full code. It looks like the issue is coming from the if statements. i.e. I have Sheets("Attainment").Cells(x, 9).Value = 47% and DMile1Req = 60%so the statement below should be omitted and MileCounter would be 0.

If Sheets("Attainment").Cells(x, 9).Value >= DMile1Req Then
        MileCounter = MileCounter + 1
    End If

On my first row where this is running the 4 statements in the function are similar to the one above and should be False. Hence MileCounter should be equal to 0.

When that value is returned here:

Dim MileCount As Integer
    MileCount = MileCounter(i)

If MileCount <= 1 Then
        Sheets("Attainment").Cells(i, 12).Value = 0
        Sheets("Attainment").Cells(i, 24).Value = 0
        Sheets("Attainment").Cells(i, 32).Value = "Y"
        Sheets("Attainment").Cells(i, 35).Value = MileCount

        GoTo Last
    End If

It should be printing out a Y in column 32 and a 0 in Column 35. It isn't printing out anything in Column 35. When I change the operator to If MileCount >= 1 it is printing out a 4 as if all 4 statements in the function were true.

 

[avd88]

Ok, I see the Problem. I'm trying to use variables from my sub inside my Function! I'll try and fix it and update how it went. Thanks for the conversation this was very helpful.

 

[Joe4]

Where are the DMile1Req, DMile2Req, FMile1Req, and FMile2Req values declared and set/calculated?
It looks like your Fumction is trying to use those, but I don't see those set anywhere.

 

[Joe4]

Ok, I see the Problem. I'm trying to use variables from my sub inside my Function! I'll try and fix it and update how it went. Thanks for the conversation this was very helpful.

Looks like our last posts probably crossed in posting!

 

[avd88]

It worked beautifully! Thanks for helping me find the issue Joe!

 

[Joe4]

You are welcome.