Attribute VB_Name = "V04_Sub_Function"
Option Explicit

' ------------------------------------------------------------------
' FUNCTIONS return a value. Use them in a cell like any Excel function,
' or call them from another procedure.
' ------------------------------------------------------------------

' Retail price from the ex-factory price: wholesaler and pharmacy markups,
' then VAT. VAT is optional and defaults to zero.
Function RetailPrice(ByVal exFactory As Double, ByVal wholesaleMarkup As Double, _
                     ByVal pharmacyMarkup As Double, Optional ByVal vat As Double = 0) As Double
    RetailPrice = exFactory * (1 + wholesaleMarkup) * (1 + pharmacyMarkup) * (1 + vat)
End Function

' A share, returned as a fraction (format the cell as %).
' On a zero total it returns #DIV/0! like a native function, instead of a message box.
Function SharePct(ByVal part As Double, ByVal total As Double) As Variant
    If total = 0 Then
        SharePct = CVErr(xlErrDiv0)
    Else
        SharePct = part / total
    End If
End Function

' ------------------------------------------------------------------
' SUBS perform actions. They appear in the Macro dialog (Alt+F8).
' ------------------------------------------------------------------

' Calls the RetailPrice function for every row and writes values in column I
Sub Fill_Retail_Prices()
    Dim ws As Worksheet, lastRow As Long, r As Long
    Set ws = ThisWorkbook.Worksheets("Price Chain")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ws.Range("I2:I" & ws.Rows.Count).ClearContents
    For r = 2 To lastRow
        ws.Cells(r, 9).Value = RetailPrice(ws.Cells(r, 3).Value, ws.Cells(r, 4).Value, _
                                           ws.Cells(r, 5).Value, ws.Cells(r, 6).Value)
    Next r
End Sub

' Flags retail prices above the threshold in L2 and counts them.
' The counter lives here and is passed ByRef to the helper, which updates it.
Sub Review_Prices()
    Dim ws As Worksheet, lastRow As Long, r As Long
    Dim threshold As Double, flagged As Long
    Set ws = ThisWorkbook.Worksheets("Price Chain")
    threshold = ws.Range("L2").Value
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ws.Range("J2:J" & ws.Rows.Count).ClearContents

    flagged = 0
    For r = 2 To lastRow
        Flag_If_Above ws, r, threshold, flagged
    Next r

    ws.Range("L3").Value = flagged
    MsgBox flagged & " retail prices above " & Format(threshold, "#,##0.00"), vbInformation
End Sub

' Private: hidden from the Macro dialog, callable only from this module
Private Sub Flag_If_Above(ByVal ws As Worksheet, ByVal r As Long, ByVal threshold As Double, ByRef flagged As Long)
    If ws.Cells(r, 7).Value > threshold Then
        ws.Cells(r, 10).Value = "Above"
        flagged = flagged + 1
    End If
End Sub