Replicate SUMPRODUCT in VBA to create UDF which is much shorter to simplify appearance of formulae

Robnrolla

New Member
Joined
Jul 16, 2017
Messages
13
Hello,

I would like to know how to create a UDF in VBA that replicates a SUMPRODUCT formula. Is this possible and how can I do it? I am new to VBA so not familiar enough with the code to create this myself.

For example - UDF - HO() = SUMPRODUCT((Item Index range=X)*(Date Index Range=Y)*(Numbers Range))

Thanks,

Rob
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

You should take a look at Evaluate ... in order to work ou the result of your Sumproduct formula ...

Hope this will help
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,321
Office Version
2010
Platform
Windows
is somethign like this what you are looking for:
Code:
Function ho(r1 As Range, r2 As Range, r3 As Range)
 ho = Application.WorksheetFunction.SumProduct(r1, r2, r3)
End Function
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,907
Messages
5,489,654
Members
407,703
Latest member
Chibuzo

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top