Transforming array multiplication from Excel to VBA function

I'm new to VBA and just starting to try working with array formulas. I have two functions that I can't figure out how to do in VBA.

1. I want to create a function called =ifIs(CheckItem,CheckRange)
CheckItem is a cell that has string. CheckRange is a range with a string array.

if checkItem is blank, I'd like ifIs to return an array of 1s the length of the CheckRange.
If checkItem is a string, I'd like ifIs to return and of 1 if that matches that column in checkRange, and 0 if it does not match.

The array formula in Excel is
={IF(CheckItem="",Ones,--(CheckRange=CheckItem))}

"Ones" is a range name that refers to a column of ones the same length as CheckRange. I'd like the udf to have only two inputs, however, checkItem and checkRange.

Ideally, I'd also like ifIs() to not have any loops. The function will be used on large columns of numbers so speed of execution is important.

2. I'd also like to create a UDF called TMM(vector,Matrix)

The TMM function would return as an array with the same number of columns as the matrix.

In the example spreadsheet,
=MMULT(TRANSPOSE(B10:B14),C3:E7)
would return {25 24 25}

My goal is to use these functions as building blocks for bigger formulas. I'd be grateful for any help getting started.

Hi

Sorry, I'm just leaving, don't have time to look into the file.

For your first problem, however, if the problem is the Ones range, that you don't want, I think you don't need it.

I think your array formula can be written as:

=0+((CheckItem="")+(CheckRange=CheckItem)>0)

This formula only uses the 2 inputs, as you want .

Does this help?

Originally Posted by lecxe

I think your array formula can be written as:

=0+((CheckItem="")+(CheckRange=CheckItem)>0)

Does this help?

This is very clever... I was thinking a UDF was the best way to get away from nesting ifs, but this works great. What does the zero at the beginning do?

I have two objectives in looking for a VBA solution:
1) to make the formulas easier to read. I start with formulas written in matrix notation, e.g. (Region=North.Rep=Joe)'(Sales.Product=....), and by the time the formula is written in Excel it takes 5 lines of text and is very hard to decipher. I have one solution, which is to hand the data to R for matrix work and get it back for presentation, but that means I can't share the file.
2) I haven't figured out how to do any work with arrays in VBA except by looping through them. My hope is to learn how to do these basic matrix transformations in VBA because I've run into several things that are fast in a formula but slow in VBA.

If I could have calculation times a little slower than Excel (2-3x calculation time) but with more clarity, that would be a good trade-off.