Using An Array As Input To A Custom Function
Apr 22, 2009Does 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:
View 14 Replies
ADVERTISEMENT
Custom Function With Variable Number Of Input Parameters?
May 10, 2013how 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.
Passing A Range To Be Read As An Array To A Custom Function
Jul 25, 2006code to pass a range say (A1:A3) as an arguement among other arguements to a custom function and then reading this as an array inside the custom funtion
View 4 Replies View RelatedCustom Change Macro's Range Value, From Cells Input.
Oct 17, 2009The 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
Input/output Values Into/from An Array
Nov 6, 2009I have a long list of values, a few thousand lines, that I need to input into an array so I can run a formula on the values in the array and then take those results and put them into another array which I can then call to output down a different column. Is there a faster way to accomplish this than the typical For Loop with the activecell.value and activecell.offset commands, that just takes forever.
View 15 Replies View RelatedUse An Array From One Procedure As Input For Second Precedure
Feb 28, 2010There's probably a post for this, I just can't find it.
I need help "reading" an array from a previously used procedure. The second procedure is dependent on the values from the first, however, I can't seem to "import/read" the array from my first procedure.
I know the code is executing correctly in the first procedure, i can output it to a worksheet. The output data isn't of any particular interest, it would be "taking up space" on my worksheet, but the values in the array are important for latter caclulations. I would prefer the array be "hidden".
Ex.
public delta( 0 to 360) as double
sub first()
code...
delta(theta) = x0 - x
worksheets("output").cells(theta, 1).value = delta(theta) 'Just to make sure the code is working
end sub
sub second()
?What code goes here to read the previous array delta()
end sub
Custom Function To Run A Macro
Aug 11, 2004I 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, 2009Custom average function. can this be done with Worksheet functions:
View 4 Replies View RelatedCustom Function For An Algorithm
Jan 28, 2009This 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.
Custom Addresses Function
Jun 14, 2009Imagine 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)
and got the answer.
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, 2011My 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, 2009I 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, 2009I 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, 2007I 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.
View 9 Replies View RelatedHow To Use VBA Array To Combine User Input Form
Jun 19, 2013I have a form created using the instructions here [URL] ..... however I only have two boxes that I need completing. What I need to do is combine both inputs from the form into one cell. The code below shows this working but I am unable to get the array to work.
Essentially I need to get the array to drop in the old number and new number into the new cell so it looks something like this:
Old Number - 1234
New Number - 6543
Value in cell 1234 6543
The code I am using is set out below:
VB:
Private Sub submitmeterswap_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim myarray As Variant
Set ws = Worksheets("x")
[Code]....
Using Data From Input Box In Array Formula In Macro
Nov 10, 2011I often have to research long sheets of data, which requires filling data in on my research sheet from several different sources. The research sheet has a file # in A and a vendor # in C. The data sources have the file #s & vendor #s in varying columns, but I use a pivot table to end up with file # in A, vendor # in B and the needed data in C.
The length of the research sheet varies from month to month - often 800-900 rows, so you can imagine how tedious it is to find the correct file #/vendor # combination to plug the data in. (Many of the rows on the research sheet will not have a row on the data source sheet.)
I managed to get an array formula in one of the columns on the research sheet that actually DOES work:
Code:
{=INDEX('Acct Activity'!C$1:C$7,MATCH(A7&C7,'Acct Activity'!A$1:A$7&'Acct Activity'!B$1:B$7,0))}
But the problem is that it returns #N/A in any row that doesn't have any data. This messes up the sum formula in the farthest right column. So, I tried to incorporate an ISERROR in with the formula, but couldn't get it to work; it left all the cells blank.
In the meantime, I got to thinking that, since there are up to 3 columns requiring data on the research sheet & each column's data comes from a different source, wouldn't it be great to have a macro where I could use a couple of Input Boxes: 1 that would let me click on the column where the data NEEDS to go & 1 that would let me tell it (either by typing the worksheet name or by clicking on the worksheet " PREFERRED " where the data come from)!
So, I spent the entire day yesterday trying to make any of that work & can NOT figure it out! I got the macro to pop up the first input box, & can key in the column letter, but clicking OK doesn't do anything. Yesterday, I had it so I could click OK & the box would go away, but nothing else happened & the second box wouldn't work right.
THEN, I started trying to figure out how to do an array formula in a macro & my head nearly exploded! I have a terrible time trying to understand written descriptions if they're in "tech speak", so it was all greek to me.
Here is all the farther I got with the macro:
Code:
Sub ClearingRsch()
' Jenny 10092011
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
[Code] ......
Array To Check User Input Match
Feb 22, 2007This is what i want to do: SEE ATTACHEMENT
•Write codes to pop up an InputBox to ask the user for a customer name.
•The program uses the user’s input to check whether the name is on the list. If it is, display an msgbox saying that the customer name is on the list, and the corresponding cell will be indicated in boldface and in blue. Otherwise, an msgbox will be displayed saying that the customer name is not on the list.
Dim the customer list as an array (string var. type) and Dim Found as Boolean. You will need the If-Then construction and For-Next or Do-While/Until loop too). Create a button to run and another to restore the formatting to its original style.
This is what i did so far and still having problems with it:
Option Explicit
Sub customers()
Dim Arr()
Dim R As Integer
Dim C As Integer
Dim ReturnColumn As Boolean
Changing Macro To Custom Function
Nov 19, 2008I 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.
View 5 Replies View RelatedCustom Function Select Case
Nov 5, 2012I have a custom function that will tell me which quarter it is based on a date entered into a cell. It works except if the cell is empty is still returns the last Case but not my Case else. If the cell is blank I wanted the function to not return anything.
Function QuarterMonth(InputDate As Date)
Dim MonthNumber As Integer
MonthNumber = Month(InputDate)
Select Case MonthNumber
Case 1
QuarterMonth = "Q1 - 13"
[Code] ...........
How To Refer To Cell In Which Custom Function Is Used
Feb 8, 2013Basically i want to use a UDF to count cells in a range of a certain colour.
But i want to make it easy for the user to be able to change the colour the function counts, so i thought i could ask them to colour the cell in which the function is written.
Is there a way to tell the function to pick up the Interior.ColorIndex of the cell it is written in?
Use Application.caller.interior.colorindex
Referencing Cell Above In Custom Function
Apr 27, 2008I’m trying to write a custom function that always references the cell above it but I can’t figure out the proper syntax to do so i.e.
Function Multiple_Cell_Above()
Multiple_Cell_Above = cellabove * 10
End function
Using The AutoFilter/Custom Filter Function
Feb 3, 2010I am trying to use the AutoFilter/Custom function in Excel (it is available under the Data Menu). It offers me two conditions/criteria that I can apply using and/or. For eg:
Filter:
does not begin with - 3
and / or
does not begin with - 9.
I want to add a third 'and' criteria .. is it possible, and if so, how?
The column that I am trying to filter has numbers formatted as text.
Custom Interpolate/Extrapolate Function
Aug 22, 2008I was using the macro provided by shg in post#6 of this thread: Interpolate Two-dimensional Array? ...
View 6 Replies View RelatedCustom Function Not Reading Variable
Oct 18, 2006A 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
Clear Cells With Custom Function
Jan 10, 2007I have a User Defined Function, one section attempts to clear the contents of some cells but it doesn't? (I've commented the line in question).
Function getCommission(rng As Range) As Currency
Application.Volatile
Application. ScreenUpdating = False
If rng.Offset(0, -1).Value <> "Yes" Then
Dim numTrucks As Long
numTrucks = rng.Offset(0, -10).Value
On Error Goto zero
Custom Function Preceded With Add-in Path
Aug 6, 2007I have created an addin (myAddin.xla) with custom functions in it. In a given cell, my funtion would look something like "=myCustomFunction(A1, B5)".
Periodically, while I'm working with the spreadsheet, I'll do something to a cell and the formulas in all the cells which used a custom function immediately get reset to something along the lines of "=C:...Application DataMicrosoftAddInsmyAddin.xla'!myCustomFunction(A1, B5)". The reset function also causes the function not to work properly and I get a "#NAME?" error in the cell.
I haven't yet figured out what sequence of actions causes the reset (it happens infrequently, but often enough to be a major problem) but it happens while I'm working with the document (i.e., it doesn't seem to be linked to saving/closing/reopening/etc). After I notice the reset, if I go to each cell that uses a custom function and delete everything from "C:... to myAddin.xla!" in the formula bar and hit enter, the function works normally as before.
(Technically, my Windows XP Pro system uses synchronization with a server at work, so the file path is not "C:...Application DataMicrosoftAddInsmyAddin.xla'!" but rather "\myServerNamemyDirectoryDocuments and SettingsmyUserNameApplicationDataMicrosoftAddInsmyAddin.xla'!". I'm not sure if this reset error has anything to do with being connected to the server or not, but figured I'd mention it anyway.)
Custom Function To Rank Two Ranges
Oct 4, 2007i have a function ("function1") that takes a range as an input:
function1 (a As Range) As Double
i have another function ("function2") that internally creates an array "a" that I need to be the input for function1. I tried, inside function2,:
...
function1(a)
...
but of course (?) it does not work...
Custom Function To Interpolate Table
Oct 25, 2007Actually, I've created the user defined function (UDF) to interpolate (both linear and bilinear). It's just, I keep getting this annoying error that says "A value used in the formula is of the wrong data type." But here's the kicker...I converted the UDF into a subroutine for trouble shooting, and I was able to step through the entire code and get the correct output.
Function itcinter(efpd As Single, pwr As Single) As Variant
Dim rnge, mtrnge As Range
Dim w, x, y, z, xx, yy, b As Single
Dim scenario, a As Integer
Dim J As Variant
scenario = Worksheets("Input").Range("B1").Value
pwr = pwr / 100#
If (scenario = 1) Then
Worksheets("ITC").Select
'Make table into a range for VLookUp
Set rnge = Worksheets("ITC").Range("A3", [A3].End(xlDown).End(xlToRight))
Set mtrnge = Worksheets("ITC").Range("A3", [A3].End(xlDown))
'If the given value does not match a table value exactly
On Error Resume Next.......................
Pass TextBox To Custom Function (UDF)
Mar 10, 2008I have a simple function defined in one of my worksheets (Sheet1):
Function AddFuel(fuel As String)
MsgBox fuel
End Function
How would I be able to call this from a form button event?
Private Sub CommandButton1_Click()
Sheet1.AddFuel(TextBox1)
End Sub
Everytime I try running this code I receive the error: Run-time error '1004'; Application-defined or, Object-defined error. I've even tried Application.Run("Sheet1.AddFuel", TextBox1) but still no luck. I think this is a pretty common question but I couldn't find any answer to it on the forums.
Find Value In Array Based On Partial Input From User
Feb 22, 2013I am trying to find a value in an array based on partial input from a user.
The user will enter the last four digits of a number.
Then the formula should find the number in a list and return that complete number
I will explain using example.
Here is the list of numbers (array):
3003280197
3003283233
3003625456
User input= 3233
formula output = 3003283233