VBA Evaluate to result an array

Barbatian

New Member
Joined
Sep 12, 2014
Messages
25
Hello,

I've got an array Countifs function that should return array as result:
Code:
Dim ws_Test As Worksheet
Dim Result_Array As Variant


Set ws_Test = ThisWorkbook.Worksheets("Sheet1")


Result_Array = ws_Test.Evaluate("COUNTIFS(I1,A:A,I2,B:B,I3,C:C,I4,D:D,I5,E:E,I6,F:F)")

But instead Result_Array becoming array it becomes double from "non array" function.

However if I do:
Code:
Result_Array = ws_Test.Evaluate("SUM(COUNTIFS(I1,A:A,I2,B:B,I3,C:C,I4,D:D,I5,E:E,I6,F:F))")
The Result will be SUM from an Array...

Does this mean that Evaluate cannot return true result of an formula(e.g. array) but the value result as the formula in cell would do?

Best Regards,
Barb
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Evaluate often needs a bit of coaching to return an array - you could use:

Rich (BB code):
Result_Array = ws_Test.Evaluate("INDEX(COUNTIFS(I1,A:A,I2,B:B,I3,C:C,I4,D:D,I5,E:E,I6,F:F),)")
 
Upvote 0
Must be going mad because it produced an array of values for me without index.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top