Re: Sumproduct in UDF

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Bob,

Thank you very much. And if I add "Application.Volatile" the UDF takes care
of any changes as well.

Using Booleans for text strings was just a silly mistake.

I am simply using UDF in this case to make an Excel application more user
friendly. There will be several different sumproduct formulas prepared. The
user will then copy these UDF formulas into other sheets within the the same
workbook. Then it's easier for the user to change cell references without
"destroying" the formula.
--
Regards,

Martin


"Bob Phillips" wrote:

> Why are you using Booleans for text strings.
>
> This works
>
> Function Function_Name(Code As Integer, Job As String, Country As String)
> Dim sFormula As String
>
> sFormula = "SumProduct(--(Rng1=" & Code & ")," & _
> "--(Rng2=""" & Job & """)," & _
> "--(Rng3=""" & Country & """), (Rng4))"
> Function_Name = Evaluate(sFormula)
> End Function
>
> But why are you creating a UDF that just does what SUMPRODUCT does anyway?
> Not efficient.
>
>
> --
> HTH
>
> Bob Phillips
>
> "Martin" <martin@xxxxxxxx> wrote in message
> news:F8D4DD97-116E-46D5-ACAE-0FC4DACB449F@xxxxxxxxxxxxxxxx
> > Dear All,
> >
> > Excel 2000.
> >
> > I cannot get the following UDF to work - it comes out as #VALUE!.
> >
> > Code is entered as numbers, ex: 230
> > Job is entered as text, ex: Manager
> > Country is entered as text, ex: England
> > Rng1, Rng2, Rng3 and Rng4 are named ranges
> >
> > Function Function_Name(Code As Integer, Job As Boolean, Country As
> Boolean)
> >
> > Function_Name = SumProduct(--(Rng1 = Code), --(Rng2 = Job), --(Rng3 =
> > Country), (Rng4))
> >
> > End Function
> >
> > Help much appreciated.
> >
> > --
> > Regards,
> >
> > Martin
>
>
>
.

Relevant Pages

  • Re: Sumproduct in UDF
    ... Function Function_Name(Code As Integer, Job As String, Country As String) ... But why are you creating a UDF that just does what SUMPRODUCT does anyway? ... > Function Function_Name(Code As Integer, Job As Boolean, Country As ...
    (microsoft.public.excel.programming)
  • Re: Extracting names and titles
    ... Since this is the programming group, you could also write a UDF or a macro to ... in some cell. ... Function ExtrWord(s As String, ... Dim sTemp As Variant ...
    (microsoft.public.excel.programming)
  • Re: How to transpose and concatenate long list in one column?
    ... If you are looking for a UDF you can try the below. ... strDel As String) As String ... Enter the formula into a sheet cell. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Append values and remove text
    ... To enter this User Defined Function (UDF), ... Ensure your project is highlighted in the Project Explorer window. ... Function ConcatRange(rg As Range, Optional Separator As String = ",") As String ... Dim vAs String ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Stuck with multi function Part 2
    ... The UDF does not have to be volatile if the ranges are specified as ... myCountIf + WorksheetFunction.CountIf ... Note that myCountIf is not limited to 3 ranges. ... Function myCountIf(s As String) ...
    (microsoft.public.excel.programming)