# Custom Function With Variable Number Of Input Parameters?

May 10, 2013

how to create a custom function with an unknown/changing number of input parameters?

Specifically, I need to create a function that will calculate the average of 1,2 or more ranges. I can't use the built-in average function as I then need to use the average in my function.

what I have so far is:

function points(current as range, previous as range)
if previous > current then final=1
if previous < current then final=0
if previous = current then final=2
points=final
end function

This works okay if I only only have 1 range of values. but in my worksheet I need to be able to enter =points(a1,c1:f1,g1) and have the function calculate the average of c1:f1,g1 and compare it to a1.

## Using An Array As Input To A Custom Function

Apr 22, 2009

Does anyone know why you can't specify an array, like an array of Doubles, as an argument or input to a custom function? For example:

## Custom Function Not Reading Variable

Oct 18, 2006

A custom function as shown below is not reading in the value of the parameter Ttorefinance which is an Excel cell value (actually a link in the spreadsheet). Eg when Ttorefinance cell value is 13, the function reads in 0.

Function MarginbyTranche(TfromIssue As Double, TtoRefinance As Double, AssetType As String, Tranche As String) As Double

Dim RmbsAaaMargin As Double
Dim RmbsAa3Margin As Double
Dim RmbsBaa2Margin As Double

Dim CmbsAaaMargin As Double
Dim CmbsAa3Margin As Double
Dim CmbsBaa2Margin As Double

Dim PsAaaMargin As Double
Dim PsAa3Margin As Double
Dim PsBaa2Margin As Double

## How To Change Variable Data Type To String If User Input Is Not A Number

Jun 3, 2014

I have declared a 'long' variable. however; how do I change the variable data type to string if user input is not a number? how do I recognize the user input's datatype?

## Excel 2007 :: GET PIVOT DATA With Variable Parameters

Mar 2, 2012

I have a pivot table that has several row labels and 1 column label. I am trying to create a Ranking table outside of the pivot table because I am using Excel 2007 and can't use the new Rank option in Excel 2010. My difficulty is in being able to rank each subgroup of the row labels.

I have thrown a column for "Target Rank" in the data, just to validate my calculation in the PivotTable worksheet, but it is only correct as long as all Company and Product Name options are selected for a given Sex, Age, and Scen Defn.

My non-dynamic formula is:

=RANK(GETPIVOTDATA("Target",\$G\$5,"Company","AIG a","Product Name","Elite Index UL a","Sex","F","Age",25,"Class","1-Best NS","Scen Defn","Overfunded A"),\$L\$8:\$L\$30).

I have figured out how to make some of the items variable to this degree:

=RANK(GETPIVOTDATA("Target",\$G\$5,"Company",\$I8,"Product Name",\$J8,"Sex","F","Age",25,"Class","1-BestNS","Scen Defn",\$K8),\$L\$8:\$L\$30).

I know can make the \$L\$8:\$L\$30 copyable across columns by changing it to L\$8:L\$30.

My dilemma is several fold.

1. If I filter the Company or Product Name the \$30 in the range no longer applies. Is there a way to make the bottom of each grouping dynamic?

2. Is there a way to make the item entry associated with the "Class" column label a variable so I can copy across columns?

3. Similar questions related to the row labels for Sex and Age?

4. Lastly, I can write an expression that would make my Rank columns blank if the Company column was blank, but is there a way to reset the data range to look at the next block? That is, if "Age" changed (was not blank) that would indicate the top of a block of data.

I have tried to think of ways to use INDIRECT and I don't know VBA well enough to do it in a macro.

ClassValues1 - Best NS4-Nonsmoker1-Best NS4-NonsmokerTarget RankAV 5 RankAV 10 RankTarget RankAV 5 RankAV 10 RankSexAgeCompanyProduct NameScen DefnTarget Target RankAV 5AV 10Target206420125F25AIG aElite Index UL aOverfunded A 1,520 20 12,848 33,607 2,135 9181961618Allianz LPLife Pro+Overfunded A 2,796 9 10,613 26,147 2,847 7221892217American GeneralElite Global PlusOverfunded A 2,810 7 7,930 26,323 2,810 1913617198AmericanGeneralChoice Index GULOverfunded A 1,610 19 11,388 31,409 2,205

[Code]...

## Function To Sum Number Of Rows Based On Variable

Jun 23, 2014

As you can see in the attached excel doc, I have calculated a column for "Number of Claims Per week" and also "Dollar amount Per claim". This was done using VLOOKUP and distributions. That is not the issue.

The issue I am having is creating the "TOTAL" for the week. So for example, if week one generates 4 claims, I need to be able to add up claim amount #1-#4.

It seems as though this would be a basic function, but I can seem to find a way to add up a certain number of cells, based on a variable generated from a random number.

If you take a look a my excel sheet, I am trying to solve for the "Claim Total" column on the far right. This column should be the sum of (Number of claims * The individual amount per claim). Note that if there is 4 claims in that week, I would have to add up claim 1-4 to get that weeks total.

Question2.xlsx‎

## Trim Function Parameters

Apr 26, 2007

what is the function of the (1) within the below trim function.

The value of A2 is "ABC - 123 - Lovely"

Range("A1").Value = Trim( Split(Range("A2").Value, "-")(1))

## Function Parameters And Return - Compile Error

Jun 28, 2014

I have this code snippet..

Code:
Function appointment(ByVal day As String, ByVal hour As Integer) As String
' Insert code to return any appointment for the given day and time.
Return "appointment"
End Function

But I get this error. Compile error: Expected: end of statement

## Variable Sheets, Variable Input

Mar 23, 2009

I run a machine that scans parts and is able to output the scans into Excel. Each part scanned creates a new sheet, and the number of sheets is variable as the number of parts scanned depends on the size of the lot. Also, since each part scanned is going to have a different number of features, the information being output on each sheet will be variable as well.

I am trying to figure out how to write a macro that will find the first feature, find the values for that feature I am looking for, output a max and min into a final sheet, and repeat for each feature, and for each sheet. I hope this is clear.

## Custom Change Macro's Range Value, From Cells Input.

Oct 17, 2009

The code below works fine, I can copy selected area into new sheet. However, I wish to custom change the Range("A1:AO164") value, for example, at cells(1,1) I key in A1, then cells(1.2) I key in A56, then this changes will reflect to the value in this macro to Range("A1:A56"). I tried using this way :

Dim A as string
cells(1,1).text = a
cells(1,2).text = b
Range("a:b").Select

## Custom Number Format For 0 (zero) Number - Make It Center Alignment

May 11, 2014

i am looking for excel custom number format for 0 (zero) number that make center alignment..

for example ;

sample (when type 0 (zero) number)
after custom number format
- (right alignment)
- (center alignment)

how make center alignment with custom number format for 0 (zero) number..

## Input Box Allow To Input The Column Number

Dec 5, 2007

I want to create an input box that will allow you to input the column number it should start in... so instead of it being fixed at A, you can choose column B, C, D all the way to the end if you like.

Sub SplitSheetsNoDelete1()
Application.ScreenUpdating = False
Dim Lmainsheet As String
Dim Lrow As Integer
Dim lcontinue As Boolean

Dim LColamaster As String
Dim lcolatest As String

'Retrieve name of sheet that contains the data
Lmainsheet = ActiveSheet.Name

'Initialize variables
lcontinue = True
Lrow = 2.....................................

## MS Query - Parameters (parameters On A Query But Use A Wildcard To Return All Instances)

Nov 17, 2009

Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.

## Insert A Variable Number Of Rows And Copy And Paste From And To Variable Positions

Aug 8, 2009

On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.

## Use Input Box Variable In More Than One Module / Procedure?

Apr 10, 2013

i know i have done this before but im totally blanking. I want to user the inputvalue in procedures called from controller, but it just shows as empty.

Code:

Sub controler()
Static inputvalue As Long
'other macro's called here
end sub

## Matching Up Fields With Variable Input

Oct 12, 2009

Example:
Order Number from VendorOrder Number from SupplierTracking Number from Supplier12345123451ZABCD13456134561ZBCDE134561ZCDEF14567145671ZDEFG1456714567

In these three orders, the first order nubmer has a 1-to-1 relationship with a single tracking number. In the second, there are two different tracking numbers associated with one number from column A. In the third, there is only one tracking number but three different lines of the same order number that need to be updated with it.

The question is, is there a way to line up data fields head to head even when there are variable inputs like this?

## Filter By Variable Captured From Input Box

Jul 17, 2007

I have a Workbook containing a list of records. I'd like to create some code to

1) Get the user to input their initials when the workbook opens, and capture the response
2) Clear any filters currently applied
3) Apply filters to a non-continous range of columns
4) Apply the input response as the criteria to one of the columns, and 'blanks' to another 2 columns

Private Sub Workbook_Open()
Static varUser As String
'sets varUser variable as String & stores it
varUser = InputBox(Prompt:="Enter Intials (leave blank to view all)", Title:="Login")
'Activates input box to capture value
Dim ContractsSheet As Range
Set ContractsSheet = Worksheets("Contracts").Range("A1:AA999")
ContractsSheet.EntireRow.Hidden = False....................

## Custom Data Entry Form To Post To Separate Worksheets Dependent On A Variable

Nov 25, 2009

I have a custom data entry form which is working fine. The form completes customer data for reviewing at a later date. I have now been asked to change it so it will seperate the data in to customer's who require some documents sent out and some that dont.

Is it possible to have a combobox on the form and if the options on the combobox are Yes and No (original), when either option is selected the data will complete on to a seperate sheet for cases with documents required or documents not required?

## Custom Function To Run A Macro

Aug 11, 2004

I am wanting to create a custom function that i can enter into a cell to run a macro (MyMacro). I do not know how to write a function, but so far i have:

Public Function Run(MacroName As String)
Application.Run MyMacro
End Function

## Custom Average Function

Dec 11, 2009

Custom average function. can this be done with Worksheet functions:

## Custom Function For An Algorithm

Jan 28, 2009

This just a shot in the dark, but does anyone have a custom function that calculates the check digit for a cusip? http://en.wikipedia.org/wiki/CUSIP

The algorithm is listed above.

Jun 14, 2009

Imagine i have 2 columns: Open and Close, both of these got numbers like

1 ---- 2
3 ---- 1
4 ---- 10

and so on. I had to make a function which checks if some number is in between any of those Open and Close numbers and count how many, for example: im searching for number 1.3, so according to previously drawn table i would get answer of 2, because 2 is in between 1----2 and 3-----1, i achieved this by a simple function:

=IF(OR(AND(Bendras!\$J\$1>=Table1[[#This Row],[Open]],(Table1[[#This Row],[Close]]>=Bendras!\$J\$1)),AND((Bendras!\$J\$1<=Table1[[#This Row],[Open]]),(Table1[[#This Row],[Close]]<=Bendras!\$J\$1))),TRUE,FALSE)

this generated an additional column with TRUE and FALSE values which i counted with:

=COUNTIF(Table1[T/F],TRUE)

so now then preparations are ready i need to make a function which would for example if the number i was searching was in 10th and 45th rows find the MAX/MIN values of Close column between those rows(hope i made my self clear)

this is how i was hoping to do that : first of all make a new array of all cell addresses from "Close" column which were "TRUE" from the first function i wrote and when do w/e i like with those cell addresses in other functions.

## Custom Function With IF Statements?

Nov 21, 2011

My co-worker created a function that would calculate the standard deviation according to whatever range is selected by the user.

I was hoping to add an if statement to the formula but am getting a #value! error.

coworker's function:

Function volatility(r As Range, Optional scl As Double = 252) As Double
Dim lr() As Double
rws = r.Rows.Count
ReDim lr(1 To (rws - 1)) As Double
Dim variance As Double

[code]....

What I'd like to do with the function:

=volatility(IF((YEAR(A8:A1036)>=K3)*(YEAR(A8:A1036)

## Calling A Custom Function Within VBA

Sep 9, 2009

I have created a custom Function in excel. I would like to create a custom button so I can run this funtion with a Click of a button unstead of typing it in or clicking on the insert function button. I am not sure if this is possible to have VB call a custom function. Below is my VB for the Custom Function. I basically want the custom button to open the formula in excel.

Public Function CreateFlexstring(Company As String, Cost_Center As String, _
Division As String, Geography As String)

CreateFlexstring = Company & "-" & Cost_Center & "-" & Division & "-" & Geography

End Function

## Conditional Max Custom Function

Jul 10, 2009

I have a column of data with 2 possible values, "H" or "A". The second column contains numbers (unsorted).

How do I write the VBA code to return the Highest number, where the first column is 'H' ?

Eg.

Location Attendance
H 25365
A 17436
A 47252
H 15494
A 37578
H 17549
H 28756
A 59756

This would need to return 28756.

## Built-in Custom Function Not Udf

Feb 27, 2007

I am very proficient at Excel/VBA and have a question about custom/user-defined functions that may be a little more advanced. I understand how to write custom functions and access them through the user-defined functions menu, but I would like to be able to include my function in an add-in that users could simply add, and then access the function via the 'Fx' box at the top of Excel, like they would any other built-in function, instead of having to go to the user-defined functions menu. I guess what I am looking for is how to add 'built-in' functions and not user-defined ones. I just want the user to start typing '=customFunction(' and have the parameters pop-up in tool-tip form, like any Excel built-in function would.

## Input Daily Data - Variable Do Not Move In Formula

Jun 6, 2014

I have create an excel formula that I need to input data daily.

Every day, I need to insert a line and input my new data.

Below is my formula

=IF(V159="Skip","Go",IF(P160>0,IF(COUNTIF(C60:D154,">"&H159+3),"Good"),IF(COUNTIF(C60:D154,"<"&H159-3),"Good")))

The problem is that when I insert a line. Most of my variable will change according except C60:D154

As you can see below

V159 change to V160 = This is good

C60:D154 do not change accordingly. How can I make it change accordingly ?? Why it is not changing when I insert a line ?

=IF(V160="Skip","Go",IF(P161>0,IF(COUNTIF(C60:D154,">"&H160+3),"Good"),IF(COUNTIF(C60:D154,"<"&H160-3),"Good")))

## Series Fill Based On Variable Input Data?

Mar 21, 2014

I have a spreadsheet where the start date is manually entered in Cell B1 and an end date is calculated in Cell B3. I would like to have the dates between the start and end date, with a step value of 7 days show in the rows under Cell B3. How do I get this to work?

## Random Option Selection Based On Variable Input

Jul 23, 2009

I need an action to allow for the random selection of a predetermined series of number codes which represent a given letter.

It is probably easier to look at the attached sheet and the previous discussion to see what I mean.

Previous discussion:
http://www.excelforum.com/excel-work...-of-cells.html

## Changing Macro To Custom Function

Nov 19, 2008

I have a calendar I have developed that highlights the current date and opens by default to the current month. Each worksheet is a seperate month. The current date highlighting is written with a public macro and the opening to current month is a private macro. The problem comes when this workbook is shared and the other users have to agree to accept my macro because of security. I would like to bypass that requirement and just have the workbook stand alone without the permission.