Solved How can I fix the following Excel VBA Code?

April 2, 2017 at 07:55:52
Specs: Windows 10
I would like to make a macro that outputs a value calculated from a weighted evaluation matrix. The weighted evaluation matrix is meant to determine the result of the effects of different inputs on a given output.

In my case I have 5 inputs (pro-renewable legislation, anti-carbon legislation, energy storage, electric vehicles and wind versus solar). I need to know the effect of these inputs being at different levels for each output (wind energy, solar energy, natural gas energy, coal energy, hydro energy).

Each input can be at a high, medium or low level. On another sheet, I have assigned values for high, medium and low (3, 2, 1). I have also assigned weights for each input and output (for example energy storage will have a weight of 5 for solar energy but a weight of 1 for natural gas energy). The function should read the input cells and the specific output of interest and return the sum of each input level multiplied by its weight.

As an example, if the EnergyType is "Solar", the function should see that the cell input for ProRenewable contains "High", convert it to the value assigned to "High" on the "Weighting and Logic" sheet and then multiply it by the weight assigned to ProRenewable for Solar on the "Weighting and Logic" sheet.

Here is the code:

Public Function ComputeWeightedValue(EnergyType, ProRenewable, _
                                     AntiCarbon, EnergyStorage, _
                                     EV, WindvSolar)

High = Worksheets("Weighting and Logic").Range("C16")
Medium = Worksheets("Weighting and Logic").Range("C17")
Low = Worksheets("Weighting and Logic").Range("C18")

If EnergyType = "Wind" Then

        ComputeWeightedValue = _
           ProRenewable * _
           Worksheets("Weighting and Logic").Range("B9") + _
           AntiCarbon * _
           Worksheets("Weighting and Logic").Range("C9") + _
           EnergyStorage * _
           Worksheets("Weighting and Logic").Range("D9") + _
           EV * _
           Worksheets("Weighting and Logic").Range("E9") + _
           WindvSolar * _
           Worksheets("Weighting and Logic").Range("F9")
    
    ElseIf EnergyType = "Solar" Then

         ComputeWeightedValue = _
            ProRenewable * _
            Worksheets("Weighting and Logic").Range("B10") + _
            AntiCarbon * _
            Worksheets("Weighting and Logic").Range("C10") + _
            EnergyStorage * _
            Worksheets("Weighting and Logic").Range("D10") + _
            EV * _
            Worksheets("Weighting and Logic").Range("E10") + _
            WindvSolar * _
            Worksheets("Weighting and Logic").Range("F10")
        
        ElseIf EnergyType = "NG" Then

             ComputeWeightedValue = _
                ProRenewable * _
                Worksheets("Weighting and Logic").Range("B11") + _
                AntiCarbon * _
                Worksheets("Weighting and Logic").Range("C11") + _
                EnergyStorage * _
                Worksheets("Weighting and Logic").Range("D11") + _
                EV * _
                Worksheets("Weighting and Logic").Range("E11") + _
                WindvSolar * _
                Worksheets("Weighting and Logic").Range("F11")
            
            ElseIf EnergyType = "Coal" Then

                 ComputeWeightedValue = _
                    ProRenewable * _
                    Worksheets("Weighting and Logic").Range("B12") + _
                    AntiCarbon * _
                    Worksheets("Weighting and Logic").Range("C12") + _
                    EnergyStorage * _
                    Worksheets("Weighting and Logic").Range("D12") + _
                    EV * _
                    Worksheets("Weighting and Logic").Range("E12") + _
                    WindvSolar * _
                    Worksheets("Weighting and Logic").Range("F12")

                ElseIf EnergyType = "Hydro" Then

                    ComputeWeightedValue = _
                       ProRenewable * _
                       Worksheets("Weighting and Logic").Range("B13") + _
                       AntiCarbon * _
                       Worksheets("Weighting and Logic").Range("C13") + _
                       EnergyStorage * _
                       Worksheets("Weighting and Logic").Range("D13") + _
                       EV * _
                       Worksheets("Weighting and Logic").Range("E13") + _
                       WindvSolar * _
                       Worksheets("Weighting and Logic").Range("F13")

End If

End Function


Please help if possible. Thank you!


See More: How can I fix the following Excel VBA Code?

Report •

✔ Best Answer
April 4, 2017 at 06:29:59
As far as I can determine, there is no direct way for VBA to take the strings you are passing to the function and use them as a variable name or specific value. i.e. There doesn't seem to be any type of "conversion function" built into VBA that can convert the string "Low" to the variable name Low.

The If method I suggested yesterday will work, as will Select Case. Select Case might look a little cleaner, but you still need one for each string/variable combination.


'Assign value to variable

High = Worksheets("Weighting and Logic").Range("C16")
Medium = Worksheets("Weighting and Logic").Range("C17")
Low = Worksheets("Weighting and Logic").Range("C18")

'Create variable for each string

   With Worksheets(1)
    
        Select Case ProRenewable
            Case "High":     ProRenewable_val = High
            Case "Medium":   ProRenewable_val = Medium
            Case "Low":      ProRenewable_val = Low
        End Select
         
        Select Case AntiCarbon
            Case "High":     AntiCarbon_val = High
            Case "Medium":   AntiCarbon_val = Medium
            Case "Low":      AntiCarbon_val = Low
        End Select
        
        'etc.
        
    End With

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
April 2, 2017 at 18:06:25
I'm not sure what you mean by "fix" the code.

We can't see your workbook from where we're sitting so we don't what the problem is or even how the code is supposed to get its inputs.

You'll need to provide some more detail.

I can offer this...

You can shorten your function if you want. Since it appears that only the Row number changes in each Else-If, you can condense them into a single formula by using a variable for the Row.

Public Function ComputeWeightedValue(EnergyType, ProRenewable, _
                                     AntiCarbon, EnergyStorage, _
                                     EV, WindvSolar)

High = Worksheets("Weighting and Logic").Range("C16")
Medium = Worksheets("Weighting and Logic").Range("C17")
Low = Worksheets("Weighting and Logic").Range("C18")

If EnergyType = "Wind" Then rw = 9
If EnergyType = "Solar" Then rw = 10
If EnergyType = "NG" Then rw = 11
If EnergyType = "Coal" Then rw = 12
If EnergyType = "Hydro" Then rw = 13

  With Worksheets("Weighting and Logic")
        ComputeWeightedValue = _
           ProRenewable * .Range("B" & rw) + _
           AntiCarbon * .Range("C" & rw) + _
           EnergyStorage * .Range("D" & rw) + _
           EV * .Range("E" & rw) + _
           WindvSolar * .Range("F" & rw)
  End With
    
End Function

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 3, 2017 at 11:22:21
I've isolated the problem to be converting a cell that has a word in it to a number. In this case, each input (ProRenewable, AntiCarbon,...etc.) cell would contain either "high", "medium" or "low". I then tried to to convert this into a value by defining "high", "medium" and "low" at the beginning of the function with values from another worksheet (so that the results could be easily modified by a user). But obviously the way I did it was incorrect. When I switch the input cells to contain the values directly (e.g. "high" --> 3, "medium" -->2, "low"-->1), then the problem went away.

To clarify, how do you set up a function to read a cell containing a word and understand it as a numerical value?


Report •

#3
April 3, 2017 at 12:52:11
To be honest, I don't know of a way to directly use a "text string" in a cell as a "variable name" in VBA. I might play a little more later, but for now, this "manual conversion" seems to work.

Note the use of *_val in the formula.

Public Function ComputeWeightedValue(EnergyType, ProRenewable, _
                                     AntiCarbon, EnergyStorage, _
                                     EV, WindvSolar)
                                     


High = Worksheets("Weighting and Logic").Range("C16")
Medium = Worksheets("Weighting and Logic").Range("C17")
Low = Worksheets("Weighting and Logic").Range("C18")

If ProRenewable = "High" Then
    ProRenewable_val = High
 ElseIf ProRenewable = "Medium" Then
    ProRenewable_val = Medium
 Else: ProRenewable_val = Low
End If

'Repeat for Anticarbon, EnergyStorage, Ev and WindvSolar


If EnergyType = "Wind" Then rw = 9
If EnergyType = "Solar" Then rw = 10
If EnergyType = "NG" Then rw = 11
If EnergyType = "Coal" Then rw = 12
If EnergyType = "Hydro" Then rw = 13
  With Worksheets("Weighting and Logic")
        ComputeWeightedValue = _
           ProRenewable_val * .Range("B" & rw) + _
           AntiCarbon_val * .Range("C" & rw) + _
           EnergyStorage_val * .Range("D" & rw) + _
           EV_val * .Range("E" & rw) + _
           WindvSolar_val * .Range("F" & rw)
  End With
    
End Function

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions

#4
April 4, 2017 at 06:29:59
✔ Best Answer
As far as I can determine, there is no direct way for VBA to take the strings you are passing to the function and use them as a variable name or specific value. i.e. There doesn't seem to be any type of "conversion function" built into VBA that can convert the string "Low" to the variable name Low.

The If method I suggested yesterday will work, as will Select Case. Select Case might look a little cleaner, but you still need one for each string/variable combination.


'Assign value to variable

High = Worksheets("Weighting and Logic").Range("C16")
Medium = Worksheets("Weighting and Logic").Range("C17")
Low = Worksheets("Weighting and Logic").Range("C18")

'Create variable for each string

   With Worksheets(1)
    
        Select Case ProRenewable
            Case "High":     ProRenewable_val = High
            Case "Medium":   ProRenewable_val = Medium
            Case "Low":      ProRenewable_val = Low
        End Select
         
        Select Case AntiCarbon
            Case "High":     AntiCarbon_val = High
            Case "Medium":   AntiCarbon_val = Medium
            Case "Low":      AntiCarbon_val = Low
        End Select
        
        'etc.
        
    End With

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
April 5, 2017 at 19:46:34
I ran your issue past a few colleagues and one of them came up with a non-VBA solution that might work for you. If you'll send me an email address via Private Message I'll pass his solution along.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question