UDFs And Defined Names

Feb 9, 2009

I have a column J that is named Premium. If I use the function =INT(Premium) in K4 then it returns the integer for the value in J4. If I do the same thing with a UDF then the function argument receives all the values in column J. The author of the function says this is how UDFs work by design. Is there a way to use defined names with UDFs and have it work the way it does with built in functions?

View 5 Replies


Using Defined Names In VBA?

Aug 6, 2012

Trying to use a Defined Name as the cell reference in VBA. The idea is to change the value of a couple of other cells base on the value of the cell "FreightRow". I am starting with a Message box just to make sure the code was reading the cell reference.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim FR As Range
Set FR = ThisWorkbook.Names("FreightRow").RefersTo
If FR = 3 Then
MsgBox ("Help")
End If
End Sub

View 7 Replies View Related

Using Defined Names From Different Workbooks?

Sep 15, 2014

Is it possible to use defined names from other workbooks? I would like to use this defined name as part of a VBA code.

Example: ActiveChart.SeriesCollection(1).Values = Range(Name1)

where Name1 is a defined name from a different workbook

View 3 Replies View Related

Remove All Defined Names

Oct 18, 2008

I use Vlookup's a lot so i define my range with specific names a lot. But when i want to clear the range names via Insert>Name>Define>"Select the range name then click delete. but instead of clicking on each range name, i would like to to delete all.

I can create a macro to do this but some days i use different range names that other days so the below will not work exactly.

View 2 Replies View Related

Using VBA To Create Defined Names?

Jan 13, 2013

Ive created the following code which I want to use to create a name in excel but it doesnt work.

when i put =INDIRECT i get an error and with out the = it places a text string in with speech marks on it e.g. defined name reference in excel reads ="INDIRECT('Health and Safety'!$AP$3)"

How do I get rid of those speech marks? the cell reference will change each time the macro runs and so will the sheet reference.

myC = ActiveCell.Address
Dim strNAME As String
strNAME = "YAXIS" & "_" & "HS" & Chartnumber
strSHEET = ActiveSheet.Name
ActiveWorkbook.Worksheets(ActiveSheet.Name).Names.Add Name:=strNAME, _
RefersToR1C1:="INDIRECT('" & strSHEET & "'!" & myC & ")"

View 4 Replies View Related

Defined Names In Workbooks

Feb 24, 2009

I am currently overhauling a massive workbook, that previously relied on an absurd amount of named ranges in order to run vlookups. I no longer need these named ranges and would like to delete them to unclog the workbook. Currently I only see a way to delete them one by one. Is there a way to mass delete all of them?

View 9 Replies View Related

How To Delete All Defined Names From A Workbook

Nov 11, 2005

How to delete all defined names from a workbook

View 14 Replies View Related

Defined Names Vs Reference Cells?

Mar 23, 2012

I have several SumIf formulas that references a Cell in a Main Reference Tab. This is useful to me as I have over 100 tabs of data points and having a reference cell with that text is easier to change one time vs 100 times.

My question is this: Will Defined Names be "faster" than using a reference cell? With over 100 tabs, there's a lot of formulas and I'm looking for a faster way to get the same output without Excel Memory overload.

View 9 Replies View Related

Alphabetize Defined Range Names?

Oct 23, 2013

Why can't I alphabetize all of my defined name ranges in the Name box?

View 8 Replies View Related

Copy From 'Refers To' In Defined Names

Sep 22, 2006

This thread is related to this thread. Offset, Match, Max Formula In the Insert - Name - Define window:

If one wants to copy a formula from the "Refers to" box and the formula extends past the right side of the box, how does one use "select all" or mark the whole formula from beginning to end so it can be copied without messing up the formula. At the moment, when I try this, it changes the formula to include the active cell of the worksheet that's open when I use the Ctrl + c etc. I have tried Ctrl + Ins and Ctrl + a and Ctrl + c. None of these are working for me.

View 8 Replies View Related

Defined Names Not Recognised In Formula

Jun 1, 2007

I have a situation where I copy a worksheet to further on in the same workbook (essentially, using it as a template to create new sheets). I have certain cells that contain references to named ranges, e.g. on NewSheet1, cell $A$1 contains =StudyNo. Unfortunately, it shows as #NAME?. But, if I go to the formula bar and press enter, it resolves the name properly. I thougt maybe that automatic calculation was on manual but it is not. Why is this happening and what can I do to resolve it.

View 3 Replies View Related

Populate Combobox With Defined Names

Mar 4, 2008

I want to be able populate a combobox with all defined ranges in the workbook?

View 4 Replies View Related

Conditional Formatting Based On Defined Names

Jun 9, 2009

I would really appreciate if someone could help me with the following problem:

I wrote this code, but for some reason it doesn't work. I am completely new to VBA and have no idea what went wrong:

View 14 Replies View Related

Rotate Range, Keep Defined Names Consistent.

Dec 1, 2009

as the title says, i need to rotate a range but to keep all defined names (single cell or range) consistent.

lets say i have the following cells filled:

1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
now i have a few defined ranges. lets say:
range name, refers to
mycell1row1, A1
mycell90roanything, B1
mysomething, C1
mysomthingrange, A1:F1


what i need in the end is this:

1 1 1 1 1 1 1
2 2 2 2 2 2 2
3 3 3 3 3 3 3
4 4 4 4 4 4 4
and for defined names:
mycell1row1, A1
mycell90roanything, A2
mysomething, A3
mysomthingrange, A1:A7

View 9 Replies View Related

Chart Data Series Refer To Defined Names

Jan 15, 2014

I'm trying to use some defined names to create charts. Though it works at the very begin, some extremely weird things happen when I try to develop further .....

Attached please find the test file, which simplifies my real case but shows the same problem.


In the test file, three names are defined as below:

Line1: "=Xvalue*1"
Line2: "=Xvalue*2"
Xvalue: "=ROW(SampleChart!$A$1:$A$25)"

the chart is very sample: "=SERIES(,Test.xlsx!Xvalue,Test.xlsx!Line1,1)"

So far, it works very well. However, I just simply can't add the second line into the chart, for example, "=SERIES(,Test.xlsx!Xvalue,Test.xlsx!Line2,2)"

I canNOT even change "Line1" to "Line2" in the "SERIES" formula!

View 5 Replies View Related

Create A Formula Which Sums The Values Associated With Several Defined Names

Aug 6, 2007

I am looking to create a formula which sums the values associated with several defined names. For example, I have a workbook with the following defined names SalesPerson1Total, SalesPerson2Total, etc. and these amounts are all sourced from multiple tabs. The amount of defined names (i.e. 'SalesPersons') is variable, therefore, I want the formula to read Sum the values of all defined names which are named with the following convention 'SalesPerson(X)Total'.

View 9 Replies View Related

Defined Names Referring To Nonexistent Range Won't Pick Up Once Existing

Feb 17, 2010

I have created 500+ defined names that refer to worksheets that do not exist (yet). When I add the previously nonexistent worksheets, the defined names that refer to them are not "live" and do not work in functions where that name is used. If you go into the defined names and click on the ones that refer to the now existent worksheet, they start to work, but I was hoping there was a way around this step (e.g. a simple macro that says "update all defined names").

I created all the names so that the person who will eventually be using this will only have to add an appropriately named worksheet and put the data in...then everything will automatically work. I do not think adding names is difficult, but the person who will be using this does, so I want to avoid that person mucking around in my names.

View 8 Replies View Related

Excel 2013 :: Replacing Values In Existing Functions With Newly Defined Names

Jan 6, 2014

I'm trying to simplify a spreadsheet i've been given by defining names for certain values. I'm using Excel 2013. Is there any way to have all of the newly defined names I've created for cells automatically be inserted into all of the formulas that exist in the spreadsheet?


Old formula in one sheet of a workbook looks up a cell in another sheet with the value of ='sheet1!A1

I've given the value of cell A1 in Sheet1 a name of dgwd.

How do I get every formula in the workbook that references 'sheet1!A1 to change the value within that formula to dgwd?

View 4 Replies View Related

#NAME - Error On 2 UDFs But Not 3rd

Dec 24, 2007

I have 3 UDFs that I created as Excel addins. They are all loaded at run time,
but the first two return the #NAME? error when I try to use them... The third
(Age3) works fine...

Function Age(DoB As Date)
Age = Int((Date - DoB) / 365.25)
End Function

Function Age2(DoB As Date)
Age2 = Int((Date - DoB) / 365.25)
End Function

Function Age3(DoB As Date)
Age3 = Int((Date - DoB) / 365.25)
End Function

View 9 Replies View Related

UDFs To Other Users

Feb 27, 2009

Although I'm able to write UDFs and distribute addins, one thing I've found is that if you use a UDF formula on a sheet on one machine, save it, then open it on another, Excel doesn't automatically look in the local .xla for the formula. In fact it tries to find it on the network (expecting to locate the originating user's machine, I suspect) and then throw a strop....

View 9 Replies View Related

How To Create UDFs

May 26, 2009

I know this is a mess - I am just learning how to create UDFs. I am trying to extract first and last name from an email address is this format - John.Smith@abc.com. This is what I have and of course it does not respond . .

View 9 Replies View Related

Assigning UDFs To Catergories

Jul 13, 2006

I have a addin that contains user defined functions. I am using the MacroOptions command to assign the functions to categories. The addin loads without any problem in Excel 2003. In Excel 2002 and Excel 2007 Beta, if I open Excel and load the addin using the menu the addin loads OK. But when I try to open Excel with the addin already installed I get the following error message: Run-time error '1004':
Method 'MacroOptions' of object'_Application' failed. how to aviod this error message in Excel 2002 and Excel 2007 Beta?

View 2 Replies View Related

Jumps To UDFs In Addin Module

Jan 20, 2013

When I Step through (Using the f8 Key) the below code -- Comments Explain my problem/Question


Sub SetUpTable()
Application.Calculation = xlCalculationManual ' Without entering this line the Macro in the next line or two), jumps to and begins running a UDF in a VBE ADDIN
'Module marked as Volitile
For TheYear = 1 To 5
Cells(1, TheYear + 1).Value = 1990 + TheYear


View 9 Replies View Related

Excel 2010 :: Registering Add-in With UDFs

May 14, 2014

Windows 7, Excel 2010

I have an add-in with UDFs. I want explanation of the function arguments in the function wizard and also be able to click for Help in the wizard. So I register the functions like this:

Application.MacroOptions macro:=Range("afsfunctienaam")(i), _
Description:=Range("afsfunctiebeschrijving")(i), _
Category:=Range("afsfunctiecategorie")(i), _
HelpContextID:=Range("afshelpcontextid")(i), _
HelpFile:=HelpPad, _

Where Param is the address of an array with argument descriptions, as text (like "A1:A4").

If I run the xlsm file, it works fine.

But if I load the xlam file in the add-ins dialog, the path to the Help file seems to be lost; I get directed to Excel's general online Help. The explanation of the arguments still work. Probably because they are already in the registry. But shouldn't the Help path be held in the registry as well?

View 1 Replies View Related

UDFs Recalculating And Changing Cells

Aug 18, 2009

I have been asked to look at moving a very large set of pricing sheets from Excel 2000 to Excel 2003. In the progress of this I have found that the functionality for a UDF in a cell to change/recalculate any other cells has been removed in Excel XP, whereas it was available in Excel 2000. The amount of work required to refactor the code is very large, and before starting on it it would be good to find out if there is a workaround.

The Sheet works in the following way:-User enters values, clicks a button to run a Macro,-Macro calculates all the ranges required to return a price-In many of the cells it is calculating are Functions which go and look at a large number of cells to calculate the price, sometimes writing to other Cells and sometimes having to recalculate other cells-As it is such a large sheet we can't simply calculate all cells before we tell it to price, as they are not all needed and so aren't calculated unless needed for performance reasons.It would be a lot of work to move the functionality from the Functions to the initial macro call, though this would solve the problem. Is there any other workaround?Macro security is set to low.The Errors I receive are the following:The UDF will hit a line like Range("DataRange").Calculate and this will raise a "Calculate method of Range class Failed" error. If it hits a line like Range("DataRange").Value = 1 this will raise a "Application-defined or object-defined error"If it hits a line like ActiveWorkbook.Names.Add Name:="This_Name", RefersToR1C1:="=Sheet1!R8C2" then it also raise a "Application-defined or object-defined error".Any of these run from a UDF in Excel 2000 works.I have a sample workbook if this isn't clear

View 9 Replies View Related

Replace All Defined Range Names In The Sheet That Start With "Street" To "Road"

Jun 14, 2008

I want to replace all defined range names in the sheet that start with "Street" to "Road"

For example I have 50 defined names in the sheet as such "Street-01", "Street-02"... all through "Street-50"

I want to change them all in vba to Road-01", "Road-02" etc.

View 12 Replies View Related

Application Defined Or Object Defined Error When Creating A Pivot Table

Oct 1, 2009

I keep running into this error when I try to create a pivot table.

The debugger highlights the following line:

View 4 Replies View Related

Excel 2003 :: Stuck On User-defined Type Not Defined Error?

Oct 9, 2012

Trying to convert an Excel 2003 macro to work in Excel 2007.

The problem line is

Dim MyDataObject As DataObject

I suspect the problem is a Missing Reference, but I cannot figure out which one. I have the same ones (in 2007) as 2003 except for one which is not showing

Microsoft Forms 2.0 Object Library

Is this the one it needs? It is called something else in 2007?

The ones I do have ticked are

Visual Basic For Applications
Microsoft Excel 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Object Library
Microsoft ADO Ext. 2.8 for DDL and Security
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.8 Library
Microsoft Scripting Runtime
Microsoft XML v2.6

View 8 Replies View Related

Set Bold To Font On Another Worksheet: Application Defined Or Object Defined Error

Aug 25, 2006

Call movedata(1, rrow, ecol, erow)
'Contact Person
Call movedata(26, rrow, ecol, erow)
Worksheets("new"). Range(Cells(erow, ecol), Cells(erow, (ecol - 2))).Font.Bold = True
Call movedata(2, rrow, ecol, erow)
'Street Address
Call movedata(3, rrow, ecol, erow)
Call movedata(4, rrow, ecol, erow)
' zip
Call movedata(5, rrow, ecol, erow)
Worksheets("new").Range(Cells(erow, ecol)).Font.Bold = True
' speed dial
Call movedata(6, rrow, ecol, erow)

the first time font.bold is set to true, it completes w/o error. The bolded line returns 'application defined or object defined error 1004'. Ive tried activating the new sheet immediately before setting bold (like the first time it gets set) but it still errors.

View 5 Replies View Related

Application-defined Or Object-defined Error :: Add Named Range At Run-time

Oct 4, 2006

Trying to add a named range at run-time

Here's what I have so far ...

View 9 Replies View Related

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