3

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.

Amicable
  • 3,115
  • 3
  • 49
  • 77
nhusnullin
  • 51
  • 1
  • 1
  • 3
  • 2
    `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 Apr 23 '12 at 15:47
  • @SiddharthRout Pushing F1 in the macro editor just takes you to a webpage saying that F1 doesn't work. – Crashworks Mar 05 '16 at 04:38

1 Answers1

5

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

Application.WorksheetFunction.Sum(2,2)
markblandford
  • 3,153
  • 3
  • 20
  • 28
  • 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"? – nhusnullin Apr 24 '12 at 08:27
  • Sorry, I don't know what you mean but we seem to have addressed your original question. – markblandford Apr 24 '12 at 14:18