Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert array formula into VBA module
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert array formula into VBA module
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 "Forgone" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert array formula into VBA module
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=VALU E(BCostCentre))" _ & "*(ytd.ccc=VALUE($C19))" _ & "*(ytd.ccd<191)" _ & "*(ytd.ccd<999)" _ & "*(ytd.act))*-1)") Forgone wrote: 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. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert array formula into VBA module
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert array formula into VBA module
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 wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert array formula into VBA module
On Oct 31, 9:22*pm, Dave Peterson wrote:
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=VALU E(BCostCentre))*(ytd.ccc=VALUE($C19))*(ytd.ccd=VAL UE(rep.fund))*(ytd.act)))} |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert array formula into VBA module
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 wrote: On Oct 31, 9:22 pm, Dave Peterson wrote: 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=VALU E(BCostCentre))*(ytd.ccc=VALUE($C19))*(ytd.ccd=VAL UE(rep.fund))*(ytd.act)))} -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert array formula into VBA module
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complex excel formula Array how do I convert it to a vba Function | Excel Worksheet Functions | |||
Array contents in module page? | Excel Programming | |||
Convert Normal formula to array formula | Excel Programming | |||
Convert Chart Series Formula to Array Via VBA? | Excel Programming | |||
variant array containing cel adresses convert to actual ranges-array | Excel Programming |