Bdtjtk

I want to pass a a string variable "Sheets(2).Cells(i, 41).Value" to the procedure Sub prcdTariff_calc that will assign in a loop a value to that variable in order to pass it on to another function,Function Tariff_calc.
The problem is with Sub prcdSum_year_IR(). I tried already to play around with the datatypes (String / Double) in the procedures and also CDbl(quality) etc... Is there a way to pass on the variable quality into the loop in Sub prcdTariff_calc ? Thanks for advice.

  Option Explicit

    Dim  price As String

    Dim i As Byte

    Public  sum_tariff As Double

    Dim tariff As Double, period As Double, quality As Double





    Function Tariff_calc(a As Double, Optional b As Double = 1, Optional c As Double = 1) As Double

    Tariff_calc = a * b * c

    Debug.Print b

    End Function



    Function Price_calc(a As Double, Optional b As String = 1, Optional c As Double = 1) As String

    Price_calc = Format(a * b * c, "0.00   €/MWh")

    End Function



    Sub prcdTariff_calc(Optional a As Double = 1, Optional b As Double = 1, Optional c As Double = 1)

    For i = 2 To 43

        If ThisWorkbook.Sheets(2).Cells(i, 1).Value = 1 Then

            a = Sheets(2).Cells(i, 19).Value

            'I also tried b = val(quality) here

'I experimented here by referring to newly created procedure Sub [prcdquality() quality = Sheets(2).Cells(i, 41) End Sub] and back here then b=quality; and it worked         

            sum_tariff = sum_tariff + Tariff_calc(a, Val(b), c) 'I tried val(b), but it still shows 0 in Debug.Print b above.



            price = Price_calc(a, CStr(b), c)

            lblPrice.Caption = lblPrice.Caption & price & vbCrLf

        End If

    Next i

    End Sub



    Sub prcdSum_year_Firm()'works fine

    prcdTariff_calc

    lblUnitCost.Caption = Format(sum_tariff, "0.00    €/MWh")

    End Sub



    Sub prcdSum_year_IR()

    quality = Sheets(2).Cells(i, 41)

    prcdTariff_calc b:=quality

    lblUnitCost.Caption = Format(sum_tariff, "0.00    €/MWh")

    End Sub

I resolved my problem by modifying the code by declaring the variables a,b,c inside the loop of procedure. It works fine now.

Function Tariff_calc(a As Double, Optional b As Double = 1, Optional c As Double = 1) As Double

Tariff_calc = a * b * c

End Function



Function Price_calc(a As Double, Optional b As String = 1, Optional c As Double = 1) As String

Price_calc = Format(a * b * c, "0.00   €/MWh")

End Function



Sub prcdTariff_calc(Optional a As Double = 1, Optional b As Double = 1, Optional c As Double = 1)

For i = 2 To 43

    If ThisWorkbook.Sheets(2).Cells(i, 1).Value = 1 Then

        a = Sheets(2).Cells(i, 19).Value 'annual tariff

        If optIR.Value Then

           b = Sheets(2).Cells(i, 41) 'quality factor

        End If

        If optQuarter.Value Then

            c = Sheets(2).Cells(i, 21 + q) 'period factor Quarter

        ElseIf optMonth.Value Then

            c = Sheets(2).Cells(i, 28 + m) 'period factor Month

        ElseIf optDay.Value Then

            c = Sheets(2).Cells(i, 27)

        End If



        sum_tariff = sum_tariff + Tariff_calc(a, b, c)



        price = Price_calc(a, CStr(b), c)

        lblPrice.Caption = lblPrice.Caption & price & vbCrLf

    End If

Next i

lblUnitCost.Caption = Format(sum_tariff, "0.00    €/MWh")

End Sub