Excel VBA code Private Sub Worksheet Change Power function not working

07
2014-07
  • YouKnowWho

    I'm making a worksheet where the wind speed defines the potential energy of the location. This value is the basis of a few sheets in the same Excel file. I'm trying to write a code that where I10 is the wind speed and I12 is the potential kWh on that location. I want both I10 as I12 to be input cells where you can either say: I want to know what wind speed I need to generate 4000 kwh i.e. or my wind speed is 4.2 m/s what will my potential energy be?

    This is what I made so far, but I don't get the power function to work. At the moment I defined the cell I12 (The kWh's) without VBA as =(2208,5/(54,872))*I10^3 where I10 is the windspeed.

    VBA code behind sheet which doesn't work:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address = "$I$10" Or Target.Address = "$I$12" Then

        WIND = Range("I10")
        KWH = Range("I12")

        Application.EnableEvents = False

        If Target.Address = "$I$10" Then
            Range("I12").Value = (2208.5 / (54.872)) POWER(WIND,3)
        Else
            Range("I10").Value = POWER( KWH (2208.5 / (54.872)),(1/3)

        End If
    End If
    Application.EnableEvents = True
End Sub

    Potential solution, but not working at the moment.

    Function MyPower(Number As Double, Exponent As Double) As Double
    MyPower = Number ^ Exponent
End Function
    • Answers
  • Raystafarian

    Try this (but double check the math, I'm not sure if it's calculating correctly because I'm not sure what the equation should look like)

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address = "$I$10" Or Target.Address = "$I$12" Then

        wind = Range("I10")
        KWH = Range("I12")

        Application.EnableEvents = False

        If Target.Address = "$I$10" Then
            Range("I12").Value = (2208.5 / 54.872) * wind ^ 3
        Else
            Range("I10").Value = KWH * (2208.5 / 54.872) ^ (1/3)

        End If
    End If
    Application.EnableEvents = True
End Sub

    • Related Question

    vba - Excel - How to change code to Workbook and not worksheet
  • FernandoSBS

    I have the following code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Set KeyCells = Range("C9")
    Set isect = Application.Intersect(KeyCells, Range(Target.Address))
    If Not isect Is Nothing Then
        Application.EnableEvents = False
        isect.Value = isect.Value - 40
        Application.EnableEvents = True
    End If
End Sub

    what I want to do is make it universal, ie, work for all sheets not just one. How to do it?


    • Related Answers
  • Brad Patton

    Move the code to the ThisWorkbook page

    vba

    and change the event to the Workbook_SheetChange event.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Set KeyCells = Range("C9")
    Set isect = Application.Intersect(KeyCells, Range(Target.Address))
    If Not isect Is Nothing Then
        Application.EnableEvents = False
        isect.Value = isect.Value - 40
        Application.EnableEvents = True
    End If
End Sub