Pass Multiple Non-contiguous Ranges Into A User Defined Function

Dec 23, 2009

I am trying to call a function that calculates forecast error (wMAPE). This function needs to be able to handle passing in non-contiguous ranges. I can't seem to figure out how to do that.



Function wMAPE(Forecasts As Range, Actuals As Range, Weights As Range) As Variant
Dim Denominator As Double
Dim Numerator As Double
Dim i As Long
Dim Fcst As Variant
Dim Act As Variant
Dim Wt As Variant

If Forecasts.Cells.Count Actuals.Cells.Count Then MsgBox ("Error: Arrays not same size")
If Forecasts.Cells.Count Weights.Cells.Count Then MsgBox ("Error: Arrays not same size")

Denominator = 0............

View 9 Replies


ADVERTISEMENT

Pass Ranges To User Defined Function

Dec 28, 2006

I am trying to write a User Defined Function in VBA to perform a simple two-dimensional table lookup. I have the temperature distribution in a solid given in a table in Sheet2. The first column of the table contains the time values, the first row contains the spacial values (radii), and the intersections contain temperature values. These ranges are named times, radii, and temperatures. In Excel, I can perform the lookup using: =index(temperatures, match(time, times, 1), match(radius, radii, 1))

But how do you do this in VBA? More specifically, how do you deal with passing ranges to the WorksheetFunctions?

Function temperature_ref(time_range As Range, radii_range As Range, temperatures_range As Range, time As Double, raduis As Double)

r = WorksheetFunction.Match(time, time_range, 1)
c = WorksheetFunction.Match(radius, radii_range, 1)
temperature = WorksheetFunction.Index(temperatures_range, r, c)
End Function

This function would be called (from any worksheet) as..................

View 4 Replies View Related

Pass Values Between Two Non Contiguous Ranges

Nov 20, 2008

I have Workbooks("A") and Workbooks("B") open.

Workbooks("A") contains Sheets("Sheet1"). Range("mySource").
Workbooks("B") contains Sheets("Sheet1").Range("myTarget").

Both ranges have been created by joining multiple ranges, in this way:

Union(Range("C1:C13"), Range("K1:K2"), Range("K5:K9"), Range("K14"), Range("Q6"), _
Range("I18"), Range("B20:P20"), Range("B24"), Range("C26:E26"), Range("C29"), _
Range("B34:B40"), Range("B44:Q50")).Name = "mySource" 'or "myTarget"

So: both ranges contain the same number of cells with the same addresses, and they have been added in the same order. However, if now I try to pass all values from mySource to myTarget, in this way:

Workbooks("B").Sheets("Sheet1").Range("myTarget").Value = _
Workbooks("A").Sheets("Sheet1").Range("mySource").Value

the result is a complete mess. Only the first "subrange" of mySource ("C1:C13") is passed to myTarget, and pasted in each of its "subranges", sometimes by rows and sometimes by columns...

View 2 Replies View Related

User Defined Function For Summing Multiple Criteria

Aug 5, 2008

I am in need of creating a User Defined Function in Excel that will sum numeric values based on two text criteria. I have a large set of data in an Excel worksheet that includes a column for " Market Type" and a column for "Location ID". I would like to develop a User Defined Function that allows a new or inexperienced user of the template to use the User Defined Function to select first a "Market Type" then select a "Location ID" and get the sum of the amount those values represent. A SUMPRODUCT function in Excel works for this, but can be awkward or intimidating for a new or inexperienced user. For that reason, I am specifically looking for a User Defined Function that will simply the formula for them while at the same time maintaining the degree of accuracy I need.

View 9 Replies View Related

Pass Element Of User-Defined Data Type

Oct 17, 2007

I have an array with structure, i.e. User Defined Type. Assume the user defined type has two elements: Element1 and Element2 and array name is Array.

So the definition is:

Dim Array(1 To 10) As UserType

and access to elements is

Array(5).Element1

The problem is that I need to pass the whole set of Element1 or Element2 to a function. Should it have been two separate arrays, it would not be a problem. But because of the user defined structure I have no idea how to pass a single element.

I hope there is another solution rather than to use loops. I have many arrays like this with complex structures. I simply can not replicate all of them.

View 9 Replies View Related

Show User-Defined Date Ranges

Dec 26, 2006

Based on the attached spreadsheet, how can I place a button for users to select a range of dates from Business Date column. I am stumped cos I have never done this before. They want to be able to select the date range...ie only view data of date ranging from Example : 1 July to 31 December OR 30 June to 31 Aug.

View 4 Replies View Related

Merge Multiple Non-Contiguous Ranges

Jul 20, 2007

if there is a way to select a group of cells, select another group of cells and keep the previously group of cells selected.

Example: ...

View 6 Replies View Related

Dynamic Print Ranges With Multiple Non-contiguous Cells?

Jan 24, 2012

I have a worksheet that has a few ranges and I need a printarea statement that looks like this:

Code:
ActiveSheet.PageSetup.PrintArea = "$A$1:$F$26,$G$1:$L$9,$M$1:$P$16,$Q$1:$S$7"

The above works, but each time I generate this worksheet, the ranges for the last row of each area can be dynamic.

So, I tried something like this:

Code:
Sub setPrtArea()
'set the print area
lr1 = Range("F65536").End(xlUp).Row
rngA = Range("$A$1:$F$" & lr1)
lr2 = Range("L65536").End(xlUp).Row
rngB = Range("$G$1:$L$" & lr2)
lr3 = Range("P65536").End(xlUp).Row
rngC = Range("$M$1:$P$" & lr3)
lr4 = Range("S65536").End(xlUp).Row
rngD = Range("$Q$1:$S$" & lr4)
ActiveSheet.PageSetup.PrintArea = rngA & "," & rngB & "," & rngC & "," & rngD

But, it fails. I have looked through many topics on this subject, but nothing seems to fit my scenario. This will pretty much complete my current project if I get this figured out and can export these print areas to pdf without a bunch of blank pages as I get now with no print area set.

View 9 Replies View Related

Transpose Combobox Lists From Multiple Non-contiguous Ranges

Jul 29, 2008

I transposed them into a separate sheet and used that for my lists. I have to share the file with macs.

My pc is on Vista running Excel 2003. The macs are OSX Panther (not Leopard) and running Excel 2003.

Solution (?): if I make the combobox lists programmatically, it will work on the macs?

The UserForm1 with 3 comboboxes:
The comboboxes are not dependent on each other.
For each combobox, I need to have 2 columns.
For each column, I need to transpose 2 x ranges (they are NOT contiguous)

My attempts have been embarassing and futile.

What it should be: ....

View 9 Replies View Related

User Defined Function Function Tooltip

Oct 9, 2007

when you type in a builtin function in the formula bar such as =DAY(), a small callout box appears below telling you the syntax - in this case "DAY(serial_number)". Is it possible to achieve this for a user defined function?

View 9 Replies View Related

Create User Defined Function With IF And AND

Jul 14, 2014

I am trying to create a function using the functions of IF and "And", but for any reason I don't know how to add the Add function

My function written in excel will looks like =IF(AND(A80="00",C80<>"8300"),"yes","no")

LOB Cost Center
00 1000
00 2000
00 8300
01 5000
02 8300

Function Allocation(LOB As Integer, CostCenter as Integer) As String
If LOB = 00 And CostCenter <> 8300 Then
Allocation = "Yes"
ElseIf LOB = 1, 2, 3, 4, 5 Then
Allocation = "No"

End If

End Function

View 3 Replies View Related

User Defined Function Description

Nov 25, 2008

I have created a custom add-in with custom functions and am trying to make it user friendly for other users. By running the following macro I can add functions to different categories, but I was wondering if there was a way to do this automatically so that anyone that adds this add-in will have the functions show up in the proper category without having to manually run this macro.

View 6 Replies View Related

Baffling UDF User Defined Function #Name?

Nov 4, 2009

I can't get a simple function to work. I attached a screen shot of my function. It's very simple -- adds two numbers.

View 4 Replies View Related

How To Use Sum In Writing User Defined Function

May 2, 2013

I am trying to write a function which return the difference of sum of range A and sum or range B. But I keep getting error with my codes.

Code:
Function deltaPipeline(YTDPplComm As Range, LastYearPpln As Range)

deltaPipeline = Sum(YTDPplComm) - Sum(LastYearPpln)

End Function

View 1 Replies View Related

User Defined Function In Another Module

Nov 21, 2007

I am stepping through a sub in module one that for some reason keeps jumping to a user defined function in another module. I've checked and and the function isn't being called.

View 9 Replies View Related

User Defined Function - Colorize

Jun 13, 2006

I'm coding an UDF.

The main thing is, that if in the choosen cell are a specify value, than in another cell the interior color should change.

I don't know how can I change the interior color in function.

The code, buit it's not working:

Function Alerting(rCell As Range, tCell As Range)

If rCell.Value >= 5 Then

Alerting = " alert"
Cells(tCell.Row, tCell.Column).Interior.ColorIndex = 3

End If

End Function

View 3 Replies View Related

Using Add-in For User Defined Function - Speed

Oct 24, 2006

I just hit F5 and use it in Excel with no problem. This time a person who developed the function had me install it as Add-In. I do not know if it is supposed to give any advantages (versus simply typing function in VBE window + activating it by hitting F5 key), but the function works extremely slowly. Is this Add-In

View 6 Replies View Related

Restrict Worksheet Change Event To Multiple Non Contiguous Ranges

Nov 20, 2007

Can you have more than 1 worksheet change event on the same worksheet, if so, how do you name it to prevent the ambiguous name error. What code would I need to select a text value in colums e11:e15 based on the cell value in cell named STATE and place the selected value in cell e16. I have, thanks to this resource, one worksheet change event that selects a numeric value from any column E3,F3:F7 and places that value in cell C4. but the same code doesn't work for the new worksheet change event.

View 4 Replies View Related

Using Array Constants In User-defined In Vba Function

Oct 1, 2009

I have a function in VBA of the type. Function MyFunc(Indx As Integer, k As Long, Rho As Range, A As Range) As Variant .... End Function

which is called as a user-defined function from within the Excel worksheet. When called with the last two arguments being a range (i.e. Result = MyFunc(1,98,A1:A2, B1:B2)) it works fine. However, when I try to directly use an array constant instead of a range (i.e. Result = MyFunc(1,98,{10,11}, {20,30}), it returns a #VALUE error.

I thought I could fix it by redefining the last two arguments as arrays of type double, but this didn't work either (i.e. Function MyFunc(Indx As Integer, k As Long, Rho() As Double, A() As Double) As Variant .... End Function ).

View 3 Replies View Related

User Defined Function In Different Workbooks / Addins

Jun 6, 2012

I created an addin that puts a formula in a cell to make use of a UDF in the addin. It works fine.

But when I make changes to my addin, save it as new name, load the new addin (same as the old but with new code for other things) and then open the workbook that had the UDF working, it now changes from

=customUDFfunction(1,2,3)
to
='C:Documents and SettingshomeuserApplication DataMicrosoftAddInsAddinV123.xla'!customUDFfunction(1,2,3)

The code I'm using to create the formula in the cell is

Cells(x, y).Formula = "=customUDFfunction(1,2,3)"

I'm assuming that when I create the formula in the cell it gets tagged somehow with the addin name and location. How to just put the formula with keeping it specific to the addin that created it?

View 3 Replies View Related

Returning Error From User Defined Function

Feb 7, 2007

A client has a registration workbook for each student which contains 3 types of worksheets, A) Transcript B) Registration and C) Grade sheets. Each file has only 1 Transcript sheet and 1 to many pairs of Registration and Grade sheets.

I have a GPA user defined function which obviously can only be used on a Grade sheet. I would like to return an Error if the user trys to use the udf on a non Grade sheet.

Currently I am just returning 1/0 but the smart tag returns #Name. Can I control the text displayed somehow?

View 9 Replies View Related

To Modify User Defined Function Macro

May 8, 2007

The below macro(found in VBA and MACROS for Excel) works well but I need a few modifications

Option Explicit

Function SortConcat(Rng As Range) As Variant
'Rng —The range of data to be sorted and concatenated.
Dim MySum As String, arr1() As String
Dim j As Integer, i As Integer
Dim cl As Range
Dim concat As Variant
On Error GoTo FuncFail:
'initialize output
SortConcat = 0#
'avoid user issues
If Rng.Count = 0 Then Exit Function
'get range into variant variable holding array
ReDim arr1(1 To Rng.Count)
'fill array
i = 1

1)My array is 128 cells wide(AT3:FQ3) and i get a type mismatch--seems to work well for 36 cells

2) These cells contain dates and numbes---all i want to sort and/or display is the numbers
Note: some numbers are in parenthesis and other are either single or double digits. Example: numbers are (8/8),8,10,(HG6),4,6,etc. And Dates are 04/06 format (no year, and no parenthesis)

3) All empty cells in the array show up in the above SortConcat function as zero, and are un-necessary

4) The final output should be no more than 6 concatenated numbers, so column width is not an issue, and the bubble sort is really not necessary but would be a nice feature

View 9 Replies View Related

User Defined Function With Variable Inputs

Nov 16, 2007

I want to build something like this....

function getdb(a,b,c,d,e,f........x)
getdb = a & "," & b & "," & c &....& x
end function

where x is the total number of variables.

So if in excel, I run getdb (a,b) that it will know that the funciton has only 2 variables but if i run getdb(a,b,c,d,e,f,g,h,i,j) that it knows.

I am trying to avoid getdb(a,b,,,,,,,,,,,,,,,,,,,) as I bet the user will not know how many ","s to use

View 9 Replies View Related

What Triggers A User Defined Function To Calculate

Aug 7, 2009

Suppose I have a UDF: function fubar(rg as range) as variant

and I place: =fubar($B$2:$D$11) in cell $A$1

When will fubar be calulated? Is it once initially, and then whenever there is a change in any of the cells within B$2:$D$11.

A hyperlink to an explanation would be just as welcome as a typed reply

View 9 Replies View Related

Suspend User Defined Function While Debugging

Nov 12, 2006

I am presently trying to debug a large and repetitive piece of code and find that I keep ending up in a function within the same module. I assume this is because the function is being triggered as I manipulate data. If this is the case, does anyone know how to suspend the function while I debug? I guess I could comment it out as I do not need the data it is returning but during normal running, this is not possible and I expect if it is fired off then it will slow things down considerably if my code keeps getting interrupted by interreupts from the function.

View 2 Replies View Related

Trigger Solver By User Defined Function

Mar 3, 2007

how can I generate a user defined function(UDF) that triggers excel's solver? I have a set of 5 constraints and one objective that is to be maximized. I want to perform a senstivity analysis for my objective changing these 5 constraints. So I want the UDF to take all the 5 constraints and return me the optimized value (obtained from excel's solver) for the objective cell.

View 8 Replies View Related

User Defined Function In Array Formula

Jun 18, 2007

I have dates & times in column A1:A20. In B1:B20 I have the corresponding temperatures for each date. I have set up the following dynamic ranges to refer to these ranges.

DateRange refers to A1:A20
TempRange refers to B1:B20

I have also made a user defined function that will determine if a date/time is between two times. Eg., If 21/05/06 07:30 is between "07:00" and "17:00". This function isn't concerned with the date, just if the time falls between the start and end times.


Function BetweenTimes(dDate As String, dStartTime As String, dEndTime As String) As Boolean

dDate = CDate(dDate)
dStartTime = CDate(dStartTime)
dEndTime = CDate(dEndTime)

BetweenTimes = False

'If the end time is before the start time, see if date/time falls between start and end..........

View 9 Replies View Related

User Defined Function For Paste Special

Jun 4, 2008

Is it possible to create a User Defined Function that replicates the "Copy/ Paste Special" function? I tried recording a Macro and using that as the basis for the User Defined Function but it didn't work.

View 7 Replies View Related

User Defined Function Based Upon Page Of Calculations

Nov 1, 2008

Indicate that a user-defined function can only be based upon the calculations that can be placed in a single cell. If you have too many calculations to put them into a single cell, e.g., an entire page of calculations based upon a few starting parameters that eventually yield a single value, then how do you reuse this entire page of calculations?

Is there another Excel mechanism that allows an entire page of calculations to used as a stored procedure?

View 3 Replies View Related

Making User-defined Function Accessible To All Workbooks

Apr 16, 2002

I've got a couple of user-defined functions that I coded in via one workbook (wkb A), but would like these functions to be accessible to any workbook (wkb B...Z). How is this done so that when I start a new blank wkb I can use these user-defined functions there as well?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved