Question: Macro overloading, passing variable number of arguments

Question: Macro overloading, passing variable number of arguments

Post by Frederik Roman » Wed, 09 Jul 2003 20:41:22



Excel-97 (SR-2)

In general I would like to write macros that can be overloaded, for
example Hyperlink() has an optional displaytext field.

        HyperLink( Filename)
        HyperLink( Filename, DisplayText)

Please could someone post a short, useful macro that does such a
thing, rather than give a description of how to do so.

I have searched the online help for "overloading" and "arguments" but
nothing useful is shown there.

TIA,
Fred.

 
 
 

Question: Macro overloading, passing variable number of arguments

Post by Tom Ogilv » Wed, 09 Jul 2003 22:51:14


See the help for Functions

You are just talking about optional arguments.

Function MyFunc(MyStr As String, Optional MyArg1 As _ Integer = 5, _
  Optional MyArg2 = "Dolly")
  Dim RetVal

' The function can be invoked as follows:
RetVal = MyFunc("Hello", 2, "World")    ' All 3 arguments supplied.
RetVal = MyFunc("Test", , 5)    ' Second argument omitted.
' Arguments one and three using named-arguments.
RetVal = MyFunc(MyStr:="Hello ", MyArg1:=7)

The arglist argument has the following syntax and parts:

[Optional] [ByVal | ByRef] [ParamArray] varname[( )] [As type] [=
defaultvalue]

      Part Description
      Optional Optional. Indicates that an argument is not required. If
used, all subsequent arguments in arglist must also be optional and declared
using the Optional keyword. Optional can't be used for any argument if
ParamArray is used.

--
Regards,
Tom Ogilvy


Quote:> Excel-97 (SR-2)

> In general I would like to write macros that can be overloaded, for
> example Hyperlink() has an optional displaytext field.

> HyperLink( Filename)
> HyperLink( Filename, DisplayText)

> Please could someone post a short, useful macro that does such a
> thing, rather than give a description of how to do so.

> I have searched the online help for "overloading" and "arguments" but
> nothing useful is shown there.

> TIA,
> Fred.


 
 
 

1. Question: Passing a range to a macro and using this argument

Excel-97 (SR-2)

I would like to write a macro (CrossProduct) to achieve the following

        [G15:Fourier Coefficient] = CrossProduct( A20:A2067, G20:G2067) /G14

presumably to look something like

public function CrossProduct( range1, range2)
 dim iCos, result
 result = 0
 for iCos = 0: Len( range1)-1
  result += range1[ iCos] *range2[ iCos]
 end for
 CrossProduct = result
End Function

BTW, I am doing something equivalent to this (well not precisely
correct since I have not corrected with the Sqrt( N/(N-1))^2 factor
cleaning up after STDEV introduces), but it is slow and tedious:

        [G15:Fourier Coefficient] = Correl( A20:A2067, G20:G2067)
                               * Stdev(A20:A2067) *Stdev(G20:G2067)
/G14

but I need the method for accessing a cell range for plenty of other
applications.

I have no idea how to find a thread to this topic in the online help,
hence the post.

TIA,
Fred.

2. Audit (rentry)

3. Macro variables - processing a variable number of items - conditional

4. WS467 Switches - woth keeping

5. wireless networking security bug

6. Macro variables - processing a variable number of items - conditional compilation of open code

7. System auto shutdown/restart

8. Passing a variable to a sub (WAS: passing a variable to a module)

9. Passing a structured variable as an argument of procedure

10. pass a conditional expression as macro argument?

11. Array name passed to macro as argument?

12. Is there a good method to replace a variable number of arguments function?