How To Call VBA Code from Excel 4 Macro

[Guest]

We have a large system done in Excel 4 Macro Language. We would like to
start migrating to VBA by writing all future additions and modifications in
VBA.

Is there a way to call a VBA function from an Excel 4 Macro? If so, how do
you do it, how do you pass parameters to the VBA function, and how do you get
a return value from the VBA function?

 

[Fredrik Wahlgren]

We have a large system done in Excel 4 Macro Language. We would like to
start migrating to VBA by writing all future additions and modifications in
VBA.

Is there a way to call a VBA function from an Excel 4 Macro? If so, how do
you do it, how do you pass parameters to the VBA function, and how do you get
a return value from the VBA function?

I think the RUN function should work
Snipped from macrofun.hlp

"Macro Sheets Only
Equivalent to choosing the Run button in the Macro dialog box, which
appears when you choose the Macro command from the Tools menu. Runs a macro.

Syntax

RUN(reference, step)
RUN?(reference, step)
Reference is a reference to the macro you want to run or a number from 1
to 4 specifying an Auto macro to run.

If reference is Specifies

1 All Auto_Open macros on the active workbook
2 All Auto_Close macros
3 All Auto_Activate macros
4 All Auto_Deactivate macros

If reference is a range of cells, RUN begins with the macro function in the
upper-left cell of reference.
If the macro sheet containing the macro is not the active document,
reference can be an external reference to the name of the macro, such as
RUN([BOOK1]Macro!Months) or an external R1C1-style reference to the location
of the macro, such as RUN("[Book1]Macro!R2C3"). The reference must be in
text form.
If reference is omitted, the macro function in the active cell is carried
out, and macro execution continues down that column.

Step is a logical value specifying that the macro is to be run in
single-step mode. If step is TRUE, Microsoft Excel runs the macro in
single-step mode; if FALSE or omitted, Microsoft Excel runs the macro
normally.

Remarks

RUN is recorded when you choose the Run command from the Macro menu while
recording a macro. The reference you enter in the Run dialog box is recorded
as text, with A1-style references converted to R1C1-style references.
To run a macro from a macro sheet, you could alternatively enter the name
of the macro as a formula, followed by a set of parentheses. For example,
enter =[Book1]Macro!Months() instead of =RUN([Book1]Macro!Months)."

/Fredrik

 

[Tom Ogilvy]

Prevously posted by Jim Rech:
-----------------------------------
If the VB routine is in the same workbook as the XL4 macro you merely have
to use its name. For instance with this function in Book1.xls:

Function TimesTwo(Num As Integer) As Integer
TimesTwo = Num * 2
End Function

in an XL4 macro sheet also in Book1.xls all you need is this:

=timestwo(2)

This will return 4 to the cell the above function call is in.

If the VB routine is being called from another workbook the XL4 call would
look like this:

=Book1.xls!timestwo(2)

 

[Guest]

Thanks!!

We have a large system done in Excel 4 Macro Language. We would like to
start migrating to VBA by writing all future additions and modifications in
VBA.

Is there a way to call a VBA function from an Excel 4 Macro? If so, how do
you do it, how do you pass parameters to the VBA function, and how do you get
a return value from the VBA function?

I think the RUN function should work
Snipped from macrofun.hlp

"Macro Sheets Only
Equivalent to choosing the Run button in the Macro dialog box, which
appears when you choose the Macro command from the Tools menu. Runs a macro.

Syntax

RUN(reference, step)
RUN?(reference, step)
Reference is a reference to the macro you want to run or a number from 1
to 4 specifying an Auto macro to run.

If reference is Specifies

1 All Auto_Open macros on the active workbook
2 All Auto_Close macros
3 All Auto_Activate macros
4 All Auto_Deactivate macros

If reference is a range of cells, RUN begins with the macro function in the
upper-left cell of reference.
If the macro sheet containing the macro is not the active document,
reference can be an external reference to the name of the macro, such as
RUN([BOOK1]Macro!Months) or an external R1C1-style reference to the location
of the macro, such as RUN("[Book1]Macro!R2C3"). The reference must be in
text form.
If reference is omitted, the macro function in the active cell is carried
out, and macro execution continues down that column.

Step is a logical value specifying that the macro is to be run in
single-step mode. If step is TRUE, Microsoft Excel runs the macro in
single-step mode; if FALSE or omitted, Microsoft Excel runs the macro
normally.

Remarks

RUN is recorded when you choose the Run command from the Macro menu while
recording a macro. The reference you enter in the Run dialog box is recorded
as text, with A1-style references converted to R1C1-style references.
To run a macro from a macro sheet, you could alternatively enter the name
of the macro as a formula, followed by a set of parentheses. For example,
enter =[Book1]Macro!Months() instead of =RUN([Book1]Macro!Months)."

/Fredrik

 

[Guest]

Thanks!!

Prevously posted by Jim Rech:
-----------------------------------
If the VB routine is in the same workbook as the XL4 macro you merely have
to use its name. For instance with this function in Book1.xls:

Function TimesTwo(Num As Integer) As Integer
TimesTwo = Num * 2
End Function

in an XL4 macro sheet also in Book1.xls all you need is this:

=timestwo(2)

This will return 4 to the cell the above function call is in.

If the VB routine is being called from another workbook the XL4 call would
look like this:

=Book1.xls!timestwo(2)