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

View 6 Replies


ADVERTISEMENT

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.

View 5 Replies View Related

Macro Causing Custom Function To Fire

Sep 3, 2007

I have a large file, part of which amongst other things calculates life expectancy from a range of q(x) values (proportion of people that die moving from age (x) to (x+1). Life expectancy is calculated using a user-defined function (below).

My problem is that whenever I run a macro that changes the file, even parts of the file that don't affect the cells using the life function, it jumps into the life function. (An example: copying and pasting values on a different sheet). This is a hassle when stepping through other macros using F8, not to mention the time cost.

Some further possibly necessary information: one macro uses the GoalSeek application to set the target cell that contains the life function

By the way, this didn't use to happen in older versions of a similar file. When running the GoalSeek macro to change target life expectancy it did, but not for any other macro.

Here is the function:

Function life(data As Range)

' Aims to calculate Life expectancy from Qx values
' It assumes first value of Q is Qb, then Q0 to Qmax

Dim Nobs As Integer
Dim j As Integer, i As Integer
Dim q() As Double
Dim L() As Double
Dim T As Double, le As Double

Nobs = data.Rows.Count

View 9 Replies View Related

Custom Average Function

Dec 11, 2009

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

View 4 Replies View Related

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.

View 3 Replies View Related

Custom Addresses Function

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)

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.

View 12 Replies View Related

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)

View 3 Replies View Related

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

View 9 Replies View Related

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.

View 5 Replies View Related

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.

View 9 Replies View Related

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:

View 14 Replies View Related

Custom Function Select Case

Nov 5, 2012

I 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] ...........

View 9 Replies View Related

How To Refer To Cell In Which Custom Function Is Used

Feb 8, 2013

Basically 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

View 2 Replies View Related

Referencing Cell Above In Custom Function

Apr 27, 2008

Ií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

View 9 Replies View Related

Using The AutoFilter/Custom Filter Function

Feb 3, 2010

I 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.

View 9 Replies View Related

Custom Interpolate/Extrapolate Function

Aug 22, 2008

I was using the macro provided by shg in post#6 of this thread: Interpolate Two-dimensional Array? ...

View 6 Replies View Related

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

View 3 Replies View Related

Clear Cells With Custom Function

Jan 10, 2007

I 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

View 5 Replies View Related

Custom Function Preceded With Add-in Path

Aug 6, 2007

I 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.)

View 4 Replies View Related

Custom Function To Rank Two Ranges

Oct 4, 2007

i 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...

View 9 Replies View Related

Custom Function To Interpolate Table

Oct 25, 2007

Actually, 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.......................

View 4 Replies View Related

Pass TextBox To Custom Function (UDF)

Mar 10, 2008

I 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.

View 2 Replies View Related

Using INDIRECT Function To Create Custom Validation

Jun 28, 2013

I have a worksheet that uses the INDIRECT function as part of cell validation to generate a custom 'name' range, this name then references a bunch of sheets that contain the actual range where the values for the validation list are stored. For example: ValidationExample.xlsx

Name = Color
Values = Blue, Red, Green

Name = Shape
Values = Square, Circle, Triangle

So what this allows me to do is in the first cell, I can define two options such as Color and Shape. Once one of these is selected, the next cell will have a formula for the validation as "=INDIRECT(A1)" then I will define two named ranges called 'Color' and 'Shape'. This will then show me the list of items in the cell based on previous selection. An example of this is attached to this posting above.

When the formula is written into the validation, an error message is generated saying that the formula will generate an error. Even with this message, the method works effectively to provide a blank list if nothing in the first cell is selected, or a list dependent on the selection of the first cell.

In this case, what I want to do is generate a macro that will populate the validation for a cell when new records are added. This won't always copy down from previous cells because the way I add records is through use of a macro and I generally find using the format painter in vba to be a fairly sloppy way of getting formats from other cells. So I go through a series of validation additions to each cell in the worksheet to get this validation created (I have no written this into the workbook attached).

VB:
Dim i As Long
Dim lastRow As Long
Dim sht As worksheet

[Code] .....

As I read in previous posts, Formula1 cannot contain an actual formula, only names and lists of items. Since each of the cells will have a changing reference, thus the INDIRECT function would need to change to reflect this, The 'Secondary' name consists of the following:

"=INDIRECT(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN()), 0,-1)))"

Which of course does not work due to the error when using INDIRECT in the first place (though the Address() function appropriately finds the cell with the validation in it's appropriate address). I can think of other ways to do this, such as having validation lists change in with VBA on a SelectionChange or Change event, but that's a lot of code that is subject to change when the named ranges have changes to them (which is fairly frequent). Is there any way to get the .Validation.Add method to ignore errors in the name supplied to it.

For reference, my code is this:

VB:
Private Sub cmdRefreshValidation_Click()
'Re-enters validation parameters to all cells to allow selections
Dim i As Long
Dim lastRow As Long
Dim sht As Worksheet

[Code] .....

View 1 Replies View Related

How To Create Custom Copy And Paste Function

Jan 15, 2013

How would I create a shortcut to automatically copy the contents of cell H1 to cell K1, in other word I would like to hit a shortcut key and have the contents of that cell pasted 3 cells to the right on the same line. I have to do this over and over again down the worksheet. I am working on taxes and want to copy values over to the expenses column as I find them.

I would also like to know if it is possible while having cell D1 highlighted I could have a shortcut created that would copy the value three cells over in H1 to cell K1. That would be the fastest, but I don't know if it is possible?

View 2 Replies View Related

Custom VBA Function - Automatic Calculating Values

Feb 27, 2014

I'm having problems with a custom VBA Function, regarding with the automatic calculating values.

Basically i have a function that receives an input parameter, cell address, and returns a string, changing the cell value.

View 4 Replies View Related

Setting Range.Formula With Custom Function

May 18, 2009

I recently set up some functions based on Chip Pearson's tutorial for referencing worksheets from Formulas. (http://www.cpearson.com/excel/sheetref.htm)

The problem I'm now having is that I can't use VBA to set these functions in place; it returns a syntax error.

For example, I want I37 on most sheets to have the same formula. So I have the following:

View 3 Replies View Related

Write A Custom Made IFERROR Function

Aug 6, 2009

how to write a custom made IFERROR function which works on Excel 2003 that mimics as closely as possible the Excel 2007 IFERROR function without using any array formula.

View 2 Replies View Related

Custom Worksheet Function And Calculation Dependency

Jan 29, 2010

I have written a custom function to be called from worksheet cells. The function is basically a wrapper function for VLOOKUP on a single table. It makes cell formulas shorter, easier to read and self-documenting. Here's a much simplified version of the function:

View 3 Replies View Related

Custom Function To Get Drive Time Between Two Points

Sep 21, 2011

I ran across a great article on performing Drive Time Calculations between two addresses. The only problem is that I can not seem to get it to work properly. Here is a link to the article cause I want full credit to be given to the person that created it.

[URL]

Here are the steps I have taken so far.

1. Downloaded Microsoft Internet Transfer Control reference (msinet.ocx) per [URL]

2. Registered the msinet.ocx

3. Selected Microsoft Internet Transfer Control as a Reference in Visual Basic.

4. Copied the custom function for DriveTime into a module.

5. Went to sheet that contained addresses (Column A contains PointA, Column B contains PointB)

6. In C2 I entered the formula =DriveTime(A2,B2)

7. After entering the formula I get a error (#VALUE)

View 9 Replies View Related

Custom Function To Find Heading And Subheading

Feb 24, 2014

I am having a situation where I have heading of certain names or items and it is spread across 3-5 cells (merged as one) and underneath these cells there is subheading (each item occupying single independent cell). I just want a custom function that can LOOKUP the heading and then give me values corresponding to subheading of choice. I have attached relevant image for understanding. For example if in any other sheet or in the same sheet i want to lookup something FIRST by "Quantity" (main heading) and SECOND by subheading then how can i do the same?

Image is below:

View 1 Replies View Related







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