running a macro into a function

[space1999]

Could somebody tell me...!!
I like to write a function in a cell (in Excel). The function is to run a macro.

For example..in a1 cell...
=If(a12+b12<3;&quot;small&quot;;RUNMAKRO(MAKRO1)) something like that.. do you know any function like runmakro?

 

[JohnAcc]

You can write a user defined function that can run a macro but you need to be aware that calling a macro from a function prohibits the physical alteration of the worksheet.

i.e you can only return a value to the calling cell.

For an example see the following code placed in a standard module:

Function Times10(CellData)
Macro1
Times10 = CellData * 10
End Function

Sub Macro1()
MsgBox &quot;Called from a function&quot;
End Sub

Now assuming you have a numerical value in Cell A1;in cell B1 type the following formula:

=times10(A1)

Hope This helps;

Rgds, John

 

[Zathras]

If I understand your formula, you want to run a macro whenever the value in either A12 or B12 is changed unless the net result after the change is that the sum (A12+B12) is less than three. You also want to display either &quot;small&quot; or blank in whatever cell your formula is in.

The way to do that is by putting code in the Worksheet_Change event for the sheet:
[blue]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  If Target.Address = "$A$12" Or Target.Address = "$B$12" Then
    If [A12] + [B12] >= 3 Then
      Call MAKRO1
    End If
  End If
End Sub

[/color]
Put this in a code module:
[blue]

Sub MAKRO1()
  MsgBox "Running MAKRO1"
End Sub

[/color]
Now test it by putting this in cell C12:
[blue]

  =IF(A12+B12<3,&quot;small&quot;,&quot;ok&quot;)

[/color]
Now try different values in A12 and B12 to see the effect.