Excel-VBA: passing a string variable to another procedure to assign a value to it in a loop












0















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









share|improve this question




















  • 2





    This quality = "Sheets(2).Cells(i, 41).Value" assignment does not mean that quality contains the value of the cell Sheets(2).Cells(i, 41).Value. It will just contain the string Sheets(2).Cells(i, 41).Value. And I assume with here it doesn't work you mean the line prcdTariff_calc 1, quality throws an error, right. Please understand that "does not work" is not really helpful. Also have a look at the basics of using subs and func You also might have other issues in your code as you do not use Option Explicit and global variables.

    – Storax
    Jan 2 at 10:35













  • If you don't know the data type you can usually declare it as Variant and let Excel sort the problem. However, your calculation needs a numeric value. You can convert the cell value to a number with Val(Sheets(2).Cells(i, 41).Value). Of course, if the cell contains a string the numeric value will be zero which doesn't sit well with your multiplication. So, something like Iif(Val(Sheets(2).Cells(i, 41).Value),Val(Sheets(2).Cells(i, 41).Value),1) should do the job. If passed directly to the function make sure it is passed ByVal.

    – Variatus
    Jan 2 at 10:39











  • Thank you. Actually I use Option Explicit. The error message that occurs is Type mismatch with reference to b in the part Tariff_calc(a, b, c) of the procedure Sub prcdTariff_calc. Moreover, the variable quality = "Sheets(2).Cells(i, 41).Value" should always be numeric as the column includes numbers only. I am not sure if the IIF function is of any use in this case. I need to find a way that the procedure Sub prcdTariff_calc is extracting the number of the given Cell(i,41). Apologies if I missunderstood anything.

    – tom
    Jan 2 at 12:11













  • Sorry but you seem not to understand. Your variable quality will contain the string Sheets(2).Cells(i, 41).Value. It will not contain the value of cell(i,41) of Sheets(2). For that you have to remove the double quotes and write quality = Sheets(2).Cells(i, 41).Value. Do you see the difference? That is a real basic mistake.

    – Storax
    Jan 2 at 12:40













  • Thanks, got it! I've tried this already before, but it will only return 0 as value for quality. Though there is something strange: I modified Sub prcdTariff_calc by referring to another newly created procedure which defines quality = Sheets(2).Cells(i, 41).Value (so identitcal to your suggestion) and then back in Sub prcdTariff_calc define b = quality... and then it worked. But this is exactly the same thing as written in Sub prcdSum_year_IR() ?!

    – tom
    Jan 2 at 15:12


















0















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









share|improve this question




















  • 2





    This quality = "Sheets(2).Cells(i, 41).Value" assignment does not mean that quality contains the value of the cell Sheets(2).Cells(i, 41).Value. It will just contain the string Sheets(2).Cells(i, 41).Value. And I assume with here it doesn't work you mean the line prcdTariff_calc 1, quality throws an error, right. Please understand that "does not work" is not really helpful. Also have a look at the basics of using subs and func You also might have other issues in your code as you do not use Option Explicit and global variables.

    – Storax
    Jan 2 at 10:35













  • If you don't know the data type you can usually declare it as Variant and let Excel sort the problem. However, your calculation needs a numeric value. You can convert the cell value to a number with Val(Sheets(2).Cells(i, 41).Value). Of course, if the cell contains a string the numeric value will be zero which doesn't sit well with your multiplication. So, something like Iif(Val(Sheets(2).Cells(i, 41).Value),Val(Sheets(2).Cells(i, 41).Value),1) should do the job. If passed directly to the function make sure it is passed ByVal.

    – Variatus
    Jan 2 at 10:39











  • Thank you. Actually I use Option Explicit. The error message that occurs is Type mismatch with reference to b in the part Tariff_calc(a, b, c) of the procedure Sub prcdTariff_calc. Moreover, the variable quality = "Sheets(2).Cells(i, 41).Value" should always be numeric as the column includes numbers only. I am not sure if the IIF function is of any use in this case. I need to find a way that the procedure Sub prcdTariff_calc is extracting the number of the given Cell(i,41). Apologies if I missunderstood anything.

    – tom
    Jan 2 at 12:11













  • Sorry but you seem not to understand. Your variable quality will contain the string Sheets(2).Cells(i, 41).Value. It will not contain the value of cell(i,41) of Sheets(2). For that you have to remove the double quotes and write quality = Sheets(2).Cells(i, 41).Value. Do you see the difference? That is a real basic mistake.

    – Storax
    Jan 2 at 12:40













  • Thanks, got it! I've tried this already before, but it will only return 0 as value for quality. Though there is something strange: I modified Sub prcdTariff_calc by referring to another newly created procedure which defines quality = Sheets(2).Cells(i, 41).Value (so identitcal to your suggestion) and then back in Sub prcdTariff_calc define b = quality... and then it worked. But this is exactly the same thing as written in Sub prcdSum_year_IR() ?!

    – tom
    Jan 2 at 15:12
















0












0








0








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









share|improve this question
















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






excel vba excel-vba






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 7 at 8:50









Pᴇʜ

23.7k62952




23.7k62952










asked Jan 2 at 10:26









tomtom

11




11








  • 2





    This quality = "Sheets(2).Cells(i, 41).Value" assignment does not mean that quality contains the value of the cell Sheets(2).Cells(i, 41).Value. It will just contain the string Sheets(2).Cells(i, 41).Value. And I assume with here it doesn't work you mean the line prcdTariff_calc 1, quality throws an error, right. Please understand that "does not work" is not really helpful. Also have a look at the basics of using subs and func You also might have other issues in your code as you do not use Option Explicit and global variables.

    – Storax
    Jan 2 at 10:35













  • If you don't know the data type you can usually declare it as Variant and let Excel sort the problem. However, your calculation needs a numeric value. You can convert the cell value to a number with Val(Sheets(2).Cells(i, 41).Value). Of course, if the cell contains a string the numeric value will be zero which doesn't sit well with your multiplication. So, something like Iif(Val(Sheets(2).Cells(i, 41).Value),Val(Sheets(2).Cells(i, 41).Value),1) should do the job. If passed directly to the function make sure it is passed ByVal.

    – Variatus
    Jan 2 at 10:39











  • Thank you. Actually I use Option Explicit. The error message that occurs is Type mismatch with reference to b in the part Tariff_calc(a, b, c) of the procedure Sub prcdTariff_calc. Moreover, the variable quality = "Sheets(2).Cells(i, 41).Value" should always be numeric as the column includes numbers only. I am not sure if the IIF function is of any use in this case. I need to find a way that the procedure Sub prcdTariff_calc is extracting the number of the given Cell(i,41). Apologies if I missunderstood anything.

    – tom
    Jan 2 at 12:11













  • Sorry but you seem not to understand. Your variable quality will contain the string Sheets(2).Cells(i, 41).Value. It will not contain the value of cell(i,41) of Sheets(2). For that you have to remove the double quotes and write quality = Sheets(2).Cells(i, 41).Value. Do you see the difference? That is a real basic mistake.

    – Storax
    Jan 2 at 12:40













  • Thanks, got it! I've tried this already before, but it will only return 0 as value for quality. Though there is something strange: I modified Sub prcdTariff_calc by referring to another newly created procedure which defines quality = Sheets(2).Cells(i, 41).Value (so identitcal to your suggestion) and then back in Sub prcdTariff_calc define b = quality... and then it worked. But this is exactly the same thing as written in Sub prcdSum_year_IR() ?!

    – tom
    Jan 2 at 15:12
















  • 2





    This quality = "Sheets(2).Cells(i, 41).Value" assignment does not mean that quality contains the value of the cell Sheets(2).Cells(i, 41).Value. It will just contain the string Sheets(2).Cells(i, 41).Value. And I assume with here it doesn't work you mean the line prcdTariff_calc 1, quality throws an error, right. Please understand that "does not work" is not really helpful. Also have a look at the basics of using subs and func You also might have other issues in your code as you do not use Option Explicit and global variables.

    – Storax
    Jan 2 at 10:35













  • If you don't know the data type you can usually declare it as Variant and let Excel sort the problem. However, your calculation needs a numeric value. You can convert the cell value to a number with Val(Sheets(2).Cells(i, 41).Value). Of course, if the cell contains a string the numeric value will be zero which doesn't sit well with your multiplication. So, something like Iif(Val(Sheets(2).Cells(i, 41).Value),Val(Sheets(2).Cells(i, 41).Value),1) should do the job. If passed directly to the function make sure it is passed ByVal.

    – Variatus
    Jan 2 at 10:39











  • Thank you. Actually I use Option Explicit. The error message that occurs is Type mismatch with reference to b in the part Tariff_calc(a, b, c) of the procedure Sub prcdTariff_calc. Moreover, the variable quality = "Sheets(2).Cells(i, 41).Value" should always be numeric as the column includes numbers only. I am not sure if the IIF function is of any use in this case. I need to find a way that the procedure Sub prcdTariff_calc is extracting the number of the given Cell(i,41). Apologies if I missunderstood anything.

    – tom
    Jan 2 at 12:11













  • Sorry but you seem not to understand. Your variable quality will contain the string Sheets(2).Cells(i, 41).Value. It will not contain the value of cell(i,41) of Sheets(2). For that you have to remove the double quotes and write quality = Sheets(2).Cells(i, 41).Value. Do you see the difference? That is a real basic mistake.

    – Storax
    Jan 2 at 12:40













  • Thanks, got it! I've tried this already before, but it will only return 0 as value for quality. Though there is something strange: I modified Sub prcdTariff_calc by referring to another newly created procedure which defines quality = Sheets(2).Cells(i, 41).Value (so identitcal to your suggestion) and then back in Sub prcdTariff_calc define b = quality... and then it worked. But this is exactly the same thing as written in Sub prcdSum_year_IR() ?!

    – tom
    Jan 2 at 15:12










2




2





This quality = "Sheets(2).Cells(i, 41).Value" assignment does not mean that quality contains the value of the cell Sheets(2).Cells(i, 41).Value. It will just contain the string Sheets(2).Cells(i, 41).Value. And I assume with here it doesn't work you mean the line prcdTariff_calc 1, quality throws an error, right. Please understand that "does not work" is not really helpful. Also have a look at the basics of using subs and func You also might have other issues in your code as you do not use Option Explicit and global variables.

– Storax
Jan 2 at 10:35







This quality = "Sheets(2).Cells(i, 41).Value" assignment does not mean that quality contains the value of the cell Sheets(2).Cells(i, 41).Value. It will just contain the string Sheets(2).Cells(i, 41).Value. And I assume with here it doesn't work you mean the line prcdTariff_calc 1, quality throws an error, right. Please understand that "does not work" is not really helpful. Also have a look at the basics of using subs and func You also might have other issues in your code as you do not use Option Explicit and global variables.

– Storax
Jan 2 at 10:35















If you don't know the data type you can usually declare it as Variant and let Excel sort the problem. However, your calculation needs a numeric value. You can convert the cell value to a number with Val(Sheets(2).Cells(i, 41).Value). Of course, if the cell contains a string the numeric value will be zero which doesn't sit well with your multiplication. So, something like Iif(Val(Sheets(2).Cells(i, 41).Value),Val(Sheets(2).Cells(i, 41).Value),1) should do the job. If passed directly to the function make sure it is passed ByVal.

– Variatus
Jan 2 at 10:39





If you don't know the data type you can usually declare it as Variant and let Excel sort the problem. However, your calculation needs a numeric value. You can convert the cell value to a number with Val(Sheets(2).Cells(i, 41).Value). Of course, if the cell contains a string the numeric value will be zero which doesn't sit well with your multiplication. So, something like Iif(Val(Sheets(2).Cells(i, 41).Value),Val(Sheets(2).Cells(i, 41).Value),1) should do the job. If passed directly to the function make sure it is passed ByVal.

– Variatus
Jan 2 at 10:39













Thank you. Actually I use Option Explicit. The error message that occurs is Type mismatch with reference to b in the part Tariff_calc(a, b, c) of the procedure Sub prcdTariff_calc. Moreover, the variable quality = "Sheets(2).Cells(i, 41).Value" should always be numeric as the column includes numbers only. I am not sure if the IIF function is of any use in this case. I need to find a way that the procedure Sub prcdTariff_calc is extracting the number of the given Cell(i,41). Apologies if I missunderstood anything.

– tom
Jan 2 at 12:11







Thank you. Actually I use Option Explicit. The error message that occurs is Type mismatch with reference to b in the part Tariff_calc(a, b, c) of the procedure Sub prcdTariff_calc. Moreover, the variable quality = "Sheets(2).Cells(i, 41).Value" should always be numeric as the column includes numbers only. I am not sure if the IIF function is of any use in this case. I need to find a way that the procedure Sub prcdTariff_calc is extracting the number of the given Cell(i,41). Apologies if I missunderstood anything.

– tom
Jan 2 at 12:11















Sorry but you seem not to understand. Your variable quality will contain the string Sheets(2).Cells(i, 41).Value. It will not contain the value of cell(i,41) of Sheets(2). For that you have to remove the double quotes and write quality = Sheets(2).Cells(i, 41).Value. Do you see the difference? That is a real basic mistake.

– Storax
Jan 2 at 12:40







Sorry but you seem not to understand. Your variable quality will contain the string Sheets(2).Cells(i, 41).Value. It will not contain the value of cell(i,41) of Sheets(2). For that you have to remove the double quotes and write quality = Sheets(2).Cells(i, 41).Value. Do you see the difference? That is a real basic mistake.

– Storax
Jan 2 at 12:40















Thanks, got it! I've tried this already before, but it will only return 0 as value for quality. Though there is something strange: I modified Sub prcdTariff_calc by referring to another newly created procedure which defines quality = Sheets(2).Cells(i, 41).Value (so identitcal to your suggestion) and then back in Sub prcdTariff_calc define b = quality... and then it worked. But this is exactly the same thing as written in Sub prcdSum_year_IR() ?!

– tom
Jan 2 at 15:12







Thanks, got it! I've tried this already before, but it will only return 0 as value for quality. Though there is something strange: I modified Sub prcdTariff_calc by referring to another newly created procedure which defines quality = Sheets(2).Cells(i, 41).Value (so identitcal to your suggestion) and then back in Sub prcdTariff_calc define b = quality... and then it worked. But this is exactly the same thing as written in Sub prcdSum_year_IR() ?!

– tom
Jan 2 at 15:12














1 Answer
1






active

oldest

votes


















0














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





share|improve this answer























    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54004659%2fexcel-vba-passing-a-string-variable-to-another-procedure-to-assign-a-value-to-i%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    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





    share|improve this answer




























      0














      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





      share|improve this answer


























        0












        0








        0







        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





        share|improve this answer













        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






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Jan 3 at 11:04









        tomtom

        11




        11
































            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54004659%2fexcel-vba-passing-a-string-variable-to-another-procedure-to-assign-a-value-to-i%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Monofisismo

            Angular Downloading a file using contenturl with Basic Authentication

            Olmecas