Excel-VBA: passing a string variable to another procedure to assign a value to it in a loop
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
add a comment |
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
2
Thisquality = "Sheets(2).Cells(i, 41).Value"
assignment does not mean that quality contains the value of the cellSheets(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 lineprcdTariff_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 useOption 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 useOption Explicit
. The error message that occurs isType mismatch
with reference tob
in the partTariff_calc(a, b, c)
of the procedureSub prcdTariff_calc
. Moreover, the variablequality = "Sheets(2).Cells(i, 41).Value"
should always be numeric as the column includes numbers only. I am not sure if theIIF
function is of any use in this case. I need to find a way that the procedureSub 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 variablequality
will contain the stringSheets(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 writequality = 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 forquality
. Though there is something strange: I modifiedSub prcdTariff_calc
by referring to another newly created procedure which definesquality = Sheets(2).Cells(i, 41).Value
(so identitcal to your suggestion) and then back inSub prcdTariff_calc
defineb = quality
... and then it worked. But this is exactly the same thing as written inSub prcdSum_year_IR()
?!
– tom
Jan 2 at 15:12
add a comment |
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
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
excel vba excel-vba
edited Jan 7 at 8:50
Pᴇʜ
23.7k62952
23.7k62952
asked Jan 2 at 10:26
tomtom
11
11
2
Thisquality = "Sheets(2).Cells(i, 41).Value"
assignment does not mean that quality contains the value of the cellSheets(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 lineprcdTariff_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 useOption 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 useOption Explicit
. The error message that occurs isType mismatch
with reference tob
in the partTariff_calc(a, b, c)
of the procedureSub prcdTariff_calc
. Moreover, the variablequality = "Sheets(2).Cells(i, 41).Value"
should always be numeric as the column includes numbers only. I am not sure if theIIF
function is of any use in this case. I need to find a way that the procedureSub 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 variablequality
will contain the stringSheets(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 writequality = 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 forquality
. Though there is something strange: I modifiedSub prcdTariff_calc
by referring to another newly created procedure which definesquality = Sheets(2).Cells(i, 41).Value
(so identitcal to your suggestion) and then back inSub prcdTariff_calc
defineb = quality
... and then it worked. But this is exactly the same thing as written inSub prcdSum_year_IR()
?!
– tom
Jan 2 at 15:12
add a comment |
2
Thisquality = "Sheets(2).Cells(i, 41).Value"
assignment does not mean that quality contains the value of the cellSheets(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 lineprcdTariff_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 useOption 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 useOption Explicit
. The error message that occurs isType mismatch
with reference tob
in the partTariff_calc(a, b, c)
of the procedureSub prcdTariff_calc
. Moreover, the variablequality = "Sheets(2).Cells(i, 41).Value"
should always be numeric as the column includes numbers only. I am not sure if theIIF
function is of any use in this case. I need to find a way that the procedureSub 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 variablequality
will contain the stringSheets(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 writequality = 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 forquality
. Though there is something strange: I modifiedSub prcdTariff_calc
by referring to another newly created procedure which definesquality = Sheets(2).Cells(i, 41).Value
(so identitcal to your suggestion) and then back inSub prcdTariff_calc
defineb = quality
... and then it worked. But this is exactly the same thing as written inSub 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
add a comment |
1 Answer
1
active
oldest
votes
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
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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
add a comment |
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
add a comment |
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
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
answered Jan 3 at 11:04
tomtom
11
11
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
2
This
quality = "Sheets(2).Cells(i, 41).Value"
assignment does not mean that quality contains the value of the cellSheets(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 lineprcdTariff_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 useOption 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 isType mismatch
with reference tob
in the partTariff_calc(a, b, c)
of the procedureSub prcdTariff_calc
. Moreover, the variablequality = "Sheets(2).Cells(i, 41).Value"
should always be numeric as the column includes numbers only. I am not sure if theIIF
function is of any use in this case. I need to find a way that the procedureSub 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 stringSheets(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 writequality = 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 modifiedSub prcdTariff_calc
by referring to another newly created procedure which definesquality = Sheets(2).Cells(i, 41).Value
(so identitcal to your suggestion) and then back inSub prcdTariff_calc
defineb = quality
... and then it worked. But this is exactly the same thing as written inSub prcdSum_year_IR()
?!– tom
Jan 2 at 15:12