Is there a way to make a macro run automatically? 1

[fiel]

In my Excel Spreadsheet, I have 1 cell that monitors the value of two other cells. The cell monitoring is supposed to change values based on what the user will input for the two cells. I created a macro, but is there a way, to have the macro always running so the user will never have to do it manually?

 

[SPV]

Do you really need a macro at all? As long as Automatic Calculation is enabled *, the formula in your monitor cell should recalculate whenever either of the input cells is changed.

* - Tools -> Options, Calculation tab

 

[fiel]

The thing is I'm using alot of If Statements to compare my values.

 

[SkipVought]

Why should that matter?

If Calculation is set to AUTOMATIC, your formulas will calculate whenever a change is made on the sheet.

Skip,

_[red][/red]

 

[SPV]

If it's really that complex, you could write your own function (not a sub) in VBA. It doesn't even have to be in the same workbook, it just needs to be in a workbook that it currently open. That way you get all the flexibility of a 'macro' but it will still update like a spreadsheet function, as long as Calculation is set to Automatic.

 

[GlennUK]

What we really need to see is the logic that you require to be executed. Describe what is needed please?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[markdmac]

None of that is necessary. You simply have to name your macro properly.

Use the VisualBasic editor in Excel to view the macro code and change the sub so it is named like this:

Sub Auto_Open()[green]
'
' AutoExec Macro
'[/green]
    Range("A1").Select
    Selection.Font.Bold = True
End Sub

The above macro will automatically bold cell A1 when the worksheet is opened.

Note that you should digitally sign your macros and set security to allow digitally signed macros, otherwise when opening you will be prompted to allow content to run.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[SPV]

Yes, that macro will run every time the worksheet is opened. But fiel is after something that will update a cell whenever one of 2 other cells is updated. Auto_open() doesn't cover that.

Writing a VBA function does cover the requirement:

Function my_function(var1, var2)
If blah blah
Then blah
Else blah
my_function=desired_answer
End if
End Function

Back in the spreadsheet, typing =MY_FUNCTION(A1,B1) in a cell will give the result of my_function and it will update automatically as the values of A1 and B1 change.

 

[fiel]

Thanks SPV, I'm trying to go in your direction. The only thing is as a function I'm not trying to have a value returned, is it possible to do this and just change the cells as I need, my code so far is as follows:

Function DeratingCoefficient()

' This handles the derating factor for degrees of 60 C
If Cells(2, 4) = 60 Then
If Cells(3, 4) = "21-25" Then
Cells(2, 7) = 1.08
ElseIf Cells(3, 4) = "26-30" Then
Cells(2, 7) = 1#
ElseIf Cells(3, 4) = "31-35" Then
Cells(2, 7) = 0.91
ElseIf Cells(3, 4) = "36-40" Then
Cells(2, 7) = 0.82
ElseIf Cells(3, 4) = "41-45" Then
Cells(2, 7) = 0.71
ElseIf Cells(3, 4) = "46-50" Then
Cells(2, 7) = 0.58
ElseIf Cells(3, 4) = "51-55" Then
Cells(2, 7) = 0.41
ElseIf Cells(3, 4) = "56-60" Then
Cells(2, 7) = "---"
ElseIf Cells(3, 4) = "61-70" Then
Cells(2, 7) = "---"
ElseIf Cells(3, 4) = "71-80" Then
Cells(2, 7) = "---"
End If

' This handles the derating factor for degrees of 75 C
ElseIf Cells(2, 4) = 75 Then
If Cells(3, 4) = "21-25" Then
Cells(2, 7) = 1.05
ElseIf Cells(3, 4) = "26-30" Then
Cells(2, 7) = 1#
ElseIf Cells(3, 4) = "31-35" Then
Cells(2, 7) = 0.94
ElseIf Cells(3, 4) = "36-40" Then
Cells(2, 7) = 0.88
ElseIf Cells(3, 4) = "41-45" Then
Cells(2, 7) = 0.82
ElseIf Cells(3, 4) = "46-50" Then
Cells(2, 7) = 0.75
ElseIf Cells(3, 4) = "51-55" Then
Cells(2, 7) = 0.67
ElseIf Cells(3, 4) = "56-60" Then
Cells(2, 7) = 0.58
ElseIf Cells(3, 4) = "61-70" Then
Cells(2, 7) = 0.33
ElseIf Cells(3, 4) = "71-80" Then
Cells(2, 7) = "---"
End If

' This handles the derating factor for degrees of 90 C
ElseIf Cells(2, 4) = 90 Then
If Cells(3, 4) = "21-25" Then
Cells(2, 7) = 1.04
ElseIf Cells(3, 4) = "26-30" Then
Cells(2, 7) = 1#
ElseIf Cells(3, 4) = "31-35" Then
Cells(2, 7) = 0.96
ElseIf Cells(3, 4) = "36-40" Then
Cells(2, 7) = 0.91
ElseIf Cells(3, 4) = "41-45" Then
Cells(2, 7) = 0.87
ElseIf Cells(3, 4) = "46-50" Then
Cells(2, 7) = 0.82
ElseIf Cells(3, 4) = "51-55" Then
Cells(2, 7) = 0.76
ElseIf Cells(3, 4) = "56-60" Then
Cells(2, 7) = 0.71
ElseIf Cells(3, 4) = "61-70" Then
Cells(2, 7) = 0.58
ElseIf Cells(3, 4) = "71-80" Then
Cells(2, 7) = 0.41
End If
End If
End Function

 

[SkipVought]

Change your function to a sub.

Skip,

_[red][/red]

 

[SkipVought]

... and call from the Worksheet_Change event of the Sheet Object.

Skip,

_[red][/red]

 

[fiel]

That's the thing, is there a way that as a sub it can continue running automatically?

 

[SkipVought]

"... and call from the Worksheet_Change event of the Sheet Object."

Right click the sheet tab and select View Code

hit the Object drop down and select worksheet

hit the Procedure drop down and select Change

call the procedure from Private Sub Worksheet_Change(ByVal Target As Range)


Skip,

_[red][/red]

 

[fiel]

Thanks Skip, it works exactly how I need it!

 

[SPV]

In your code, your only ever write to Cells(2,7), which means you are producing a single output value. Why not make it a function and return the value to the cell that contains the formula?

It will need to take two parameters, which your code currently pulls directly from Cells(2,4) and Cells(3,4). I've called the second parameter Blah because I don't what it means in the real-life context.

Public Function DeratingCoefficient(Temperature as Integer, Blah as String) as Variant

If Temperature = 60 Then
    If Blah = "21-25" Then
        DeratingCoefficient = 1.08
    ElseIf Blah = "26-30" Then

(etc)

End Function

You then call the function as follows:
=DERATINGCOEFFICIENT(D2,D3)

IMHO this would be an improvement over the sub/worksheet_change configuration for 2 reasons:
1. You can put the function anywhere in the spreadsheet, taking values from any cells, without having to rewrite all the cell references in the code;
2. You don't need to use the Worksheet_Change event because Excel's automatic calculation will deal with it for you.

 

[Rofeu]

Why not do away with all the ifs and use:

Select Case Temperature
    Case 60
        Select Case Blah
            Case "21-25"
            DeratingCoefficient = 1.08
            Case Blah = "26-30"
            etc
        End Select
    Case 75
        etc
End Select

Cheers,

Roel

 

[SPV]

Good suggestion.