Convert Excel 4 Macro Functions

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

I have an old Excel 4 (?) Macro function that I am trying to convert to the
new macro approach that Excel currently uses with VB.
The old macro DOES NOT use VB, but the old macro language/framework.

This old macro does not perform a scripting purpose (mirroring user actions)
but is a user defined function (algorithm) that could be included in a
cell's equation.

For example the A1 cell contents may be

= my_user_defined_function(B1, C1) + 3



This would seem to be a basic task but I am not able to find out how to do
this with a version of Excel since the introduction of VB.

Joe

--
 
Joe,

Open the VBA Editor (ALT+F11) and insert a Module (from the
Insert menu) in to your VBA Project. In that code window, write
your user defined function, similar to the following:


Function Test(Arg1 As Variant, Arg2 As Variant) As Variant
' do something with Arg1 and Arg2
Test = some_result
End Function

Then, you can call the function from a worksheet cell with
something like

=TEST(A1,B1)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Hi Joe,

Excel4 macros and functions can not be converted automatically to VBA. In
general, that is not necessary; Excel4 macros are still supported (we don't
know about the next version of Excel, though).
In the last few versions of Excel you can no longer record an Excel4 macro,
but you can still write and edit one yourself.
But you may have your reasons to convert to VBA. Why not post the code here
and let us have a look so maybe we can help you?

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
In a normal module declare a function

Public function my_user_defined_function( rng1 as range, rng2 as Range)
' code to develop results
my_user_defined_function = results_to_return
End Function
 
Thanks for the very quick and informative reply.

BTW, it appears that the Excel documentation focuses on scripting type
functionality and not algorithm function that I needed (and that you showed
me). I could not find your advise in any excel documentation. My search for
"user defined function" brought up no good hits.

Thanks again


Chip Pearson said:
Joe,

Open the VBA Editor (ALT+F11) and insert a Module (from the
Insert menu) in to your VBA Project. In that code window, write
your user defined function, similar to the following:


Function Test(Arg1 As Variant, Arg2 As Variant) As Variant
' do something with Arg1 and Arg2
Test = some_result
End Function

Then, you can call the function from a worksheet cell with
something like

=TEST(A1,B1)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
Click to expand...
 
Back
Top