Executing build-in functions from VBA (Excel)

excel vba evaluate
nhusnullin picture nhusnullin · Apr 23, 2012 · Viewed 11.8k times · Source

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.

Answer

markblandford picture markblandford · Apr 23, 2012

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

Application.WorksheetFunction.Sum(2,2)

Related questions

Does .Value = .Value act similar to Evaluate() function in VBA?

Consider the following snippet. It writes the same formula to two cells A1 and A2 Sub Main() With Range("A1") .Formula = "=1+1" End With With Range("A2") .Formula = "=1+1" .Value = .Value End With End Sub The second with block uses .Value = .Value …

Read More
VBA Evaluate function with string arguments

I have this function working ( It returns the row where the text DK001 sits in the ID range) Found = Application.Evaluate("=IF(ID=""DK001"",ROW(ID),""x"")") I would like to feed the searchcriteria (e.g. DK001) as a string, …

Read More
Evaluate Match function with variables

I would like to use this through VBA - =MATCH("PlanA",A:A,0) with EVALUATE. Sub Test() Dim SectionStartRow As Integer Dim planname As String planname = "PlanA" SectionStartRow = [MATCH(planname,A:A,0)] 'Error 2029 /// Type mismatch '13 End Sub I've already …

Read More