Convert array formula into VBA module

[Forgone]

I've got an Array Formula that I want to be able to convert to a VBA
function so that I can manipulate it without having to copy and paste
the formula a large number of times through out the 800 line workbook.

The base Formula, which uses Name ranges is:

Revenue:
=SUM((ytd.ccb=VALUE(BCostCentre))*(ytd.ccc=VALUE($C19))*(ytd.ccd<>VALUE(191))*(ytd.ccd<>VALUE(999))*(ytd.act))*-1)

Expense:
=SUM((ytd.ccb=VALUE(BCostCentre))*(ytd.ccc=VALUE($C19))*(ytd.ccd<>VALUE(191))*(ytd.ccd<>VALUE(999))*(ytd.act)))


What I want to do is along the lines of....... if the values in
ytd.ccb, ytd.ccc and ytd.ccd are null then sum up everything otherwise
if there is a value, only do that value.

This is one of the formula modifications I've done.
=IF(BCostCentre="",SUM((ytd.ccc=VALUE($C19))*(ytd.ccd<>VALUE(191))*(ytd.ccd<>VALUE(999))*(ytd.act))*-1,SUM((ytd.ccb=VALUE(BCostCentre))*(ytd.ccc=VALUE($C19))*(ytd.ccd<>VALUE(191))*(ytd.ccd<>VALUE(999))*(ytd.act))*-1)

but I think it would be a lot easier to do it using a VBA function.

Ideally, I would like to use a wildcard string in those values, eg:
"***" which will tell the formula to sum all and not filter it based
on the results.

Any assistance would be appreciated.

Thanks.

 

[Joel]

I don't quite understand what you are doing. Not sure what values are names
ranges, which are integers but this code below shoiuld help you get started.

Function Revenue(ValueCenter As String, YTD_B As Single, YTD_C As Single, _
YTD_D As Single, Val1 As Variant, Val2 As Variant, Val3 As Variant, _
Actual As Single, All As Boolean)

If (YTD_C = Val(Range(ValueCenter))) And (YTD_C = Val(Val1)) And _
(YTD_D <> Val(Val2)) And (YTD_D <> Val(Val3)) Then

Revenue = -1 * Val(Actual)
End If
End Function

 

[Dave Peterson]

First, I'm not sure why you use =value() around your terms. It may make sense
for cells that may not be numeric, but 191 and 999 are already numbers, so it's
not necessary/useful there.

Second, you have a couple of choices to convert the array formula into VBA.

#1. Loop through each of the cells and accumulate the total.
#2. Use Evaluate.

dim mySum as double
mysum = worksheets("Sheet999").evaluate("SUM((ytd.ccb=VALUE(BCostCentre))" _
& "*(ytd.ccc=VALUE($C19))" _
& "*(ytd.ccd<>191)" _
& "*(ytd.ccd<>999)" _
& "*(ytd.act))*-1)")

 

[Forgone]

First, I'm not sure why you use =value() around your terms.  It may make sense

for cells that may not be numeric, but 191 and 999 are already numbers, so it's
not necessary/useful there.

I've since removed the 191 and 999 and converted it to
ytd.ccd=VALUE(rep.fund)

I have had to use value because the cells that is being used as the
search parameters is formatted as text intentionally.

The worksheet is setup so that the search parameters are in the top of
the worksheet which I've named.
BCostCentre | BFund | BEntity | BProject - this is where the user can
input the search parameters if they want to restrict it to a certain
business area or project.

The named ranges: ytd.cca | ytd.ccb | ytd.ccc | ytd.ccd | ytd.cce is
the main datasource table for and are the relevant costcodes.
YTD represents the datasource for "year to date" thus ytd.act = year
to date actuals

BEntity = CCA
BCostCentre = CCB
BFund = CCC
BAccount (not used) = CCD
BProject = CCE

The reference to $C19 is the specific account code (CCD)

I have a number of data sources.

YTD for Year To Date Actuals which gets updated every month
O9B for 2008/2009 Budget
O8B for 2007/2008 Budget
O8A for 2007/2008 Actuals

I'm trying to keep a consistency with the named ranges.

What I've had to do is if I wanted to look at the entire Department as
a whole, I've had to create another worksheet and manipulate the
formulas to suit but that means on a very old system (which I'm using
at work) takes a very long time to calculate. I would like to work on
the one worksheet.

At the end, I only want to use the one worksheet and either report on
the required values (if not null then ...... report on each criterion
if applicable) or if null then report on the lot.

 

[Dave Peterson]

Does this mean that the Evaluate function worked?

If you're having trouble, get the array formula working in a cell on the
worksheet. Then post that working formula.

 

[Forgone]

Does this mean that the Evaluate function worked?

I tried it, and it doesn't appear to be working at all.
This is the entire function that works.

{=IF(BCostCentre="",SUM((ytd.ccc=VALUE($C19))*(ytd.ccd=VALUE(rep.fund))*(ytd.act)),SUM((ytd.ccb=VALUE(BCostCentre))*(ytd.ccc=VALUE($C19))*(ytd.ccd=VALUE(rep.fund))*(ytd.act)))}

 

[Dave Peterson]

Maybe...
dim res as variant
dim myFormula as string

myformula = "IF(BCostCentre="""",SUM((ytd.ccc=VALUE($C19))" _
& "*(ytd.ccd=VALUE(rep.fund))*(ytd.act))," _
& "SUM((ytd.ccb=VALUE(BCostCentre))*(ytd.ccc=VALUE($C19))", _
& "*(ytd.ccd=VALUE(rep.fund))*(ytd.act)))"

res = worksheets("somesheetnamehere").evaluate(myformula)

(Untested.)

If that doesn't work, share that snippet of code.

 

[Forgone]

I tried using the worksheet version Tools > Formula Auditing >
Evaluate - it passes the first condition ({=IF(BCostCentre="",) which
works but excel dies when it moves on.


I'll try it and see how it works.