Executing build-in functions from VBA (Excel)

Question

I need to have a capability to execute any kind of build-in functions (such as 'sum' or 'len') from VBA (MS Excel).

One of the restrictions that I have is that I may not pass cell ranges as arguments to these functions. Instead of this, I should be able to use strict values.

I want to be able to use the following expression SUM(1, 2) which should return 3, while the following version SUM("A1:A2") won't work for me.

I managed to develop some function that parses my prior input and makes it consist of a list of values (as for example above, it made the user's input of 'A1:A2' look like an array of numbers consisting of two values).

So, can anyone give me an example of using a build-in function that receives a list of values (not just range of cells)?

I tried the following code, but for some unknown reason, I haven't been able to get it working (I keep getting 1004 error, saying: Cannot run the macro 'SUM'. The macro may not be available in this workbook or all macros may be disabled.):

Application.Run "SUM", 2, 2

Some valuable advices that would help to find a solution to this problem will be greatly appreciated.

Some valuable advices that would help .... There is a magical key in Excel. Surprisingly you can press it from Worksheet and from the VBA Editor :) The key is F1 — Siddharth Rout
– Siddharth Rout, Commented Apr 23, 2012 at 15:47
@SiddharthRout Pushing F1 in the macro editor just takes you to a webpage saying that F1 doesn't work. — Crashworks
– Crashworks, Commented Mar 5, 2016 at 4:38

markblandford · Accepted Answer · 2012-04-23 15:45:03Z

5

To use a built-in, Excel, Worksheet function, you need to do something like the following:

Application.WorksheetFunction.Sum(2,2)

answered Apr 23, 2012 at 15:45

markblandford

3,2133 gold badges22 silver badges28 bronze badges

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

2 Comments

nhusnullin Over a year ago

ok, what do you say me if I say that function name and argument client send me from other side? My client, can send me function name as string and argument for function as range or array. Ok, I can use reflection but I`m not sure that all function realize in the WorksheetFunction. And f.e. what I should do if smb send me function "Len"?

markblandford Over a year ago

Sorry, I don't know what you mean but we seem to have addressed your original question.

Collectives™ on Stack Overflow

Executing build-in functions from VBA (Excel)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related