how to call VBA macro like =CALL

Joe Was

MrExcel MVP

Joined

Feb 19, 2002

Messages

7,539

• Jun 22, 2002

• #2

I am not sure if this is what you want?

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
the name of your sub here!
End Sub

This will run your sub any time any sheet is recalculated. JSW

 

Upvote 0

S

stevebausch

Well-known Member

Joined

May 11, 2002

Messages

810

• Jun 22, 2002

• #3

Park the following code in a module, and then use it.

You can also Insert Function; this will be found under User Defined Functions.

Code:

Function TotalOf(Number1 As Double, Number2 As Double) As Double
TotalOf = Number1 + Number2

End Function

 

Upvote 0

E

et-excel

New Member

Joined

Jun 21, 2002

Messages

2

• Jun 23, 2002

• #4

Thanks for the replies. I didn't know about Workbook_SheetCalculate. But it's not exactly what I mean. But Steve's answer is perfect. It was the format I didn't have right. (I forgot about Function, I tried to call a Sub (duh)). What would the function look like if I wanted to pass a range in?

 

Upvote 0

Jay Petrulis

MrExcel MVP

Joined

Mar 17, 2002

Messages

2,040

• Jun 23, 2002

• #5

Hi,

Here is an example...

In B1:C20 on the worksheet, fill in some numbers (both positive and negative).

Now throw a value in cell A1 and in another cell type in the following

=IF(A1>0,MyFunction(B1:B20),MyFunction(C1:C20))

where MyFunction is:

Code:

Function MyFunction(MyRange As Range) As Double
Dim Total As Double, Cell As Range

For Each Cell In MyRange
    Total = Total + Abs(Cell)
Next Cell

MyFunction = Total
End Function

All this will do is add the absolute value of each cell in the range. The range to work on is dependent on the value in cell A1 in the IF statement.

I just recently posted a UDF which works with multiple ranges being passed. The function itself calls another function in its processing before returning a value back to the worksheet. It could help to give you a lot of ideas.

I will edit this post with the link after I send.

http://mrexcel.com/board/viewtopic.php?topic=12195&forum=2&16

Bye,
Jay
This message was edited by Jay Petrulis on 2002-06-23 08:55

 

Upvote 0

S

stevebausch

Well-known Member

Joined

May 11, 2002

Messages

810

• Jun 23, 2002

• #6

Okay, here's an example of argument as Range, please note the following details:

This example is a poor excuse for Sum(); it just shows how to process a Range.

Remember, Boolean is numeric also; False is zero, True is -1. Sum() just ignores Booleans.

You really don't want to simulate the complete functionality of Sum(), I hope....If you do, study up on Parameter Arrays.

Code:

Function TotalOfRange(TheRange As Range) As Double
Dim RowLoop As Integer
Dim ColLoop As Integer
Dim Subtotal As Double

For RowLoop = 0 To TheRange.Rows.Count - 1
For ColLoop = 0 To TheRange.Columns.Count - 1
If IsNumeric(TheRange.Cells(RowLoop + 1, ColLoop + 1)) Then
'Don't forget VarType() for determining what's in a cell
Subtotal = Subtotal + TheRange.Cells(RowLoop + 1, ColLoop + 1)
End If
Next
Next
TotalOfRange = Subtotal
End Function

 

Upvote 0

Jay Petrulis

MrExcel MVP

Joined

Mar 17, 2002

Messages

2,040

• Jun 23, 2002

• #7

Hi Steve,

Great minds think alike -- go for a simple sum as an example.

I am learning to use ParamArrays and I like the added flexibility a lot. Also, there is an .Areas property which should be handled when passing ranges, although I usually am too lazy to work with that.

Bye,
Jay

 

Upvote 0

S

stevebausch

Well-known Member

Joined

May 11, 2002

Messages

810

• Jun 23, 2002

• #8

Range.Areas, eh?

Just took a quick glance at the Help for Areas, I see how that can be of great help, though I haven't gotten that far.

 

Upvote 0