Dynamically declare udf in VBA for Excel

Question

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?

Have a look at ParamArrays. tushar-mehta.com/publish_train/xl_vba_cases/… — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Sep 12, 2016 at 8:31
@twone see my answer below, let me know if that's what you meant in your post — Shai Rado
– Shai Rado, Commented Sep 12, 2016 at 8:35

Axel Richter · Accepted Answer · 2016-09-12 08:48:32Z

2

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)

edited Sep 12, 2016 at 8:48

answered Sep 12, 2016 at 8:38

Axel Richter

62.5k7 gold badges70 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CAD Developer · Accepted Answer · 2016-09-12 08:11:20Z

2

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?

answered Sep 12, 2016 at 8:11

CAD Developer

1,7072 gold badges23 silver badges33 bronze badges

Collectives™ on Stack Overflow

Dynamically declare udf in VBA for Excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related