Custom VBA Function throws unexpected output

Question

I'm trying to write a custom formula that shows me the reason of low On-Shelf-Availability a.k.a. OSA. I've planted all those nested if statements and expected to see the text from debug.print statement, but in all the cases I have, i receive 0 as the answer. What i did wrong?

Code is Below


*Option Explicit*

Function Octopus (PNZ As Double, OSA As Double, SL As Double, Current_Stock As Integer, SO_Month As Integer, Client_SI_Forecast As Integer, SO_FCST As Integer, SO_Fact As Integer, Availability_Report As Double, Client_FA As Double, OTIF As Double)


'Make formula update like an excel formula

Application.Volatile True


If Current_Stock / PNZ >= 1 Then
    
    'OSA Check
    If OSA > 0.9 Then
        ' Checking Sell Out dynamics
        If SO_Fact / (1 - PNZ) > 1.2 * Client_SI_Forecast Then
            Debug.Print " Under Forecast. Increase PNZ"
        Else
            Debug.Print "No Problems Discovered"
        End If
    
    Else
        ' Checking if SO is zero
        If SO_Fact > 0 Then
            ' Comparing Stock to its monthly average
            If Current_Stock < SO_Month Then
                Debug.Print "No Spare space on shelf, possible virtual stock"
            Else
                Debug.Print "No Spare space on shelf, possible delay on layout"
            End If
            
        Else
            Debug.Print "Virtual Stock"
        End If
    End If

        
Else

    If OSA > 0.9 Then
        If Availability_Report > 0.9 Then
            Debug.Print "No Problems Discovered"
        Else
            Debug.Print "Incorrect PNZ tuning"
            End If
    Else
        If SL > 0.9 Then
            If PNZ > SO_Fact * 2 / 7 Then
                If Client_FA > 0.8 Then
                    Debug.Print "Double-Check PNZ"
                Else
                    Debug.Print "Adress FA Issue to Client Forecasting Team"
                End If
            Else
                Debug.Print " Update PNZ value"
                End If
        Else
            If OTIF > 0.8 Then
                Debug.Print " We Didn't have product"
            Else
                Debug.Print " Issues with our logisttics "
                End If
        End If
    End If
    


End If


End Function

Please add a set of common input arguments, it is hard to follow through the code without knowing any of the values. — Toddleson, Jul 05 '21 at 16:08
YWhere are you setting the value of `Octopus` in the function? — Siddharth Rout, Jul 05 '21 at 16:09
**1.** Above the line `Application.Volatile True`, type `Dim Ret as String` **2.** Replace `Debug.Print` everywhere with `Ret = ` **3.** Finally before `End Function`, type this `Octopus = Ret` — Siddharth Rout, Jul 05 '21 at 16:14

Chronocidal · Answer 1 · 2021-07-05T16:16:01.923

You don't seem to be setting the return value anywhere. Here is a very basic VBA function:

Function TwicePlusOne(Input AS Long) As Long
    Dim TempValue As Long
    TempValue = Input *2
    TwicePlusOne= TempValue + 1 'This sets the Output value
End Function

Since your Octopus function does not have an Octopus = <VALUE> line, it will always return the default value, 0

It is possible to use ByRef to pass output via a Variable, but I do not particularly like doing so. Here is an example of that:

Function TwicePlusOne_ByRef(ByVal Input As Long, ByRef Output AS Long) As Boolean
    TwicePlusOne_ByRef = False
    On Error GoTo AbortFunction 'In case something goes wrong
    
    Dim TempValue As Long
    TempValue = Input *2
    Output = TempValue + 1 'This sets the Output value
    TwicePlusOne_ByRef = True 'Tell us that the function has worked
AbortFunction:
    On Error GoTo -1 'Clear the Error Handler
End Function

Sub UseTheFunction()
    Sub StartValue AS Long, EndValue AS Long
    StartValue = 4
    
    If TwicePlusOne_ByRef(StartValue, EndValue) Then
        MsgBox "Twice " & StartValue & " plus 1 is " & EndValue
    Else
        MsgBox "There was an error in the function"
    End If
End Sub

You're right! Now everything works, kudos! – Vasilii Chirkov Jul 05 '21 at 16:10 — Vasilii Chirkov, Jul 05 '21 at 16:10

Custom VBA Function throws unexpected output

1 Answers1