CODEWITHSUNDEEP
excelvba
IgorNikolaev
IgorNikolaev

Reputation: 1063

Dynamically declare udf in VBA for Excel

I need to create one function, that will take different (not fixed) number of arguments. Then it should perform request to the server and depending on first argument, receive different data.

E.g. =performMethod("methodName"; "arg1"; "arg2"; ...)

Main idea is to create unified function, that will replace all other functions and move all logic to the server side, so I can add new functions on the server, without need to replace client's excel file.

So my thoughts were about to define functions "on the go" so user will see excel prompt, describing arguments. Can I implement this via VBA? If not, are there any other ways to do this?

Upvotes: 0

Views: 177

Answers (2)

Axel Richter
Axel Richter

Reputation: 61945

Excel VBA can also use Parameter Arrays. So the following UDF 

Public Function performMethod(methodName As String, ParamArray args() As Variant) As Variant
 Dim sArgs As String
 For Each arg In args
  arg = arg
  If VarType(arg) = vbVariant + vbArray Then
   sArgs = sArgs & "; "
   For Each itm In arg
    sArgs = sArgs & itm & ","
   Next
   sArgs = Left(sArgs, Len(sArgs) - 1)
  Else
   sArgs = sArgs & "; " & arg
  End If
 Next
 MsgBox methodName & sArgs
 performMethod = "done"
End Function

can be called as:

=performMethod("POST",1,"Test",2)

or

=performMethod("POST",A1:C1,A2:A4,A6,A2:B4)

or

=performMethod("GET",1,A1:B4,"Test")

or

=performMethod("Test",A1:C4)

Upvotes: 2

CAD Developer
CAD Developer

Reputation: 1697

Are You sure You need different number of arguments? maybe better idea is to have one argument which in fact is the list array or variant of arguments?

Upvotes: 2

Related Questions