Define Name use in Macros

I am not sure how or if you can use the Define Name function in a worksheet 
to assign a value in an Macro. I am using the command below to assign a value 
in the worksheet in the macro.  However the user might make changes to the 
worksheet that makes this method invalid.  If I use the Define Name function 
in excel and create a Name can I then us it to assign a value?

Set Total_Assets = Sheets("Balance Sheet").Range("H55")

0
Christian1 (55)
7/14/2005 5:12:18 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
395 Views

Similar Articles

[PageSpeed] 6

You should definitely assign a name (Ctrl-F3) to this cell and all ranges 
your macros used, in case you or your users insert rows, etc.

Set Total_Assets = Sheets("Balance Sheet").Range("TotAssets")


-- 
Jim
"Bill Christian" <Bill Christian@discussions.microsoft.com> wrote in message 
news:9F053576-9105-4F32-8981-8AF995F4AB88@microsoft.com...
|I am not sure how or if you can use the Define Name function in a worksheet
| to assign a value in an Macro. I am using the command below to assign a 
value
| in the worksheet in the macro.  However the user might make changes to the
| worksheet that makes this method invalid.  If I use the Define Name 
function
| in excel and create a Name can I then us it to assign a value?
|
| Set Total_Assets = Sheets("Balance Sheet").Range("H55")
| 


0
jrrech (1933)
7/14/2005 5:20:07 PM
Hi Bill,

Yes, you can and the sintax is identical:

Set Total_Assets = Sheets("Balance Sheet").Range("MyDefinedName")


Regards,
KL



"Bill Christian" <Bill Christian@discussions.microsoft.com> wrote in message 
news:9F053576-9105-4F32-8981-8AF995F4AB88@microsoft.com...
>I am not sure how or if you can use the Define Name function in a worksheet
> to assign a value in an Macro. I am using the command below to assign a 
> value
> in the worksheet in the macro.  However the user might make changes to the
> worksheet that makes this method invalid.  If I use the Define Name 
> function
> in excel and create a Name can I then us it to assign a value?
>
> Set Total_Assets = Sheets("Balance Sheet").Range("H55")
> 


0
7/14/2005 5:22:21 PM
Thanks to Jim & KL.  My macro is now more readable and bullet proof.

"KL" wrote:

> Hi Bill,
> 
> Yes, you can and the sintax is identical:
> 
> Set Total_Assets = Sheets("Balance Sheet").Range("MyDefinedName")
> 
> 
> Regards,
> KL
> 
> 
> 
> "Bill Christian" <Bill Christian@discussions.microsoft.com> wrote in message 
> news:9F053576-9105-4F32-8981-8AF995F4AB88@microsoft.com...
> >I am not sure how or if you can use the Define Name function in a worksheet
> > to assign a value in an Macro. I am using the command below to assign a 
> > value
> > in the worksheet in the macro.  However the user might make changes to the
> > worksheet that makes this method invalid.  If I use the Define Name 
> > function
> > in excel and create a Name can I then us it to assign a value?
> >
> > Set Total_Assets = Sheets("Balance Sheet").Range("H55")
> > 
> 
> 
> 
0
7/15/2005 2:43:03 AM
Reply:

Similar Artilces:

Collections Management
Dynamics GP v9 - Collections Management Users are unable to use "user defined letters" (Word Documents) for mass mailings. They are limited to only using predefined letters, or printing each letter individually for every customer. Error received when users attempt to use a custom letter: -------------------------------------------------- Unhandled script exception: Cannot find report "COL_Reminder_UpcomingDue". EXCEPTION_CLASS_SCRIPT_MISSING SCRIPT_CMD_REPORT -------------------------------------------------- ---------------- This post is a suggestion for Microso...

Using worksheet name as reference
HI I have one master sheet with around 50 rows in it. For each Row there is corrosponding worksheet. e.g. Row #23 will have corrosponding worksheet named '23'. I want to populate some data from worksheet 23 in Row no. 23 in master sheet. How should I refer the sheet 23 automatically using Data 23 in cell in the row ? If type ='23'!B21 I can get it but I want to automate it so I'm refering to cell instead of 23 which is ='A12'!B21 here it gives error because I'm making formating error. Pl. help . thanks. Hi, Try this: =INDIRECT("'"&ROW()...

Using Money 2005 on a second machine
I normally use Money 2005 on my desktop. I have it in a shared folder. I travel quite a lot and need to be able to use Money while I'm away. I installed Money 2005 on my laptop (I checked this would be OK before buying Money) and I synchronise the data file from my desktop to my laptop at least weekly. I only use Money on my laptop or my desktop - never both at the same time. Whenever I try to open my Money data file from my laptop, I get a message that the file needs to be updated and then a second message shortly afterwards telling me that Money can't open the file. The sof...

Application-defined or object-defined error
Hi, I find myself stumped by an incredibly easy piece of code and one that I have used before. I am getting the error: Run-time error '1004': Application-defined or object-defined error I am getting the error when I run the following code: Private Sub Workbook_Open() With Application .ScreenUpdating = False .DisplayAlerts = False Workbooks.Open "\\depot02\rel\www\internal\business_areas\edg\Metrics\Phones\HighHoldTimesDetailed.xls" Workbooks("HighHoldTimesDetailed.xls").Worksheets("data").Cells.Copy _ Workbooks("phoneholdtime...

Copying To Excel Using Macro
I can get the subject, attachment info and date from an email to be copied to excel, but does anyone know a way to get lines from the actual message. Is there some property of the Folder.Items that gets me this? My problem is that I have a huge set of emails whose messages look something like this: Name: John Smith Telephone: 555-555-5555 and I need to write a macro to automatically copy these to a database. Any advice will be appreciated. Thank you. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet me...

Help with a macro
Sorry if this is an inappropriate post for this area, but I'm not getting very much of a response at the Word.vba site. When copying text from the IDE into a Word document, I would like to have the format match the format in the IDE, specifically changing the color of the font of the commented lines. I have written the following which does what I want for a particular line: Selection.Find.ClearFormatting With Selection.Find .Text = "'" .Replacement.Text = "" .Forward = True .Wrap = wdFindContinue .Format = True End...

Using Multidimensional Array
I am using multidimensional array in my function. dim aReturnValue() iIndexValue = 0 If iOccurrence > 0 Then bStringFound = True ' Prepare the return value ReDim Preserve aReturnValue(iIndexValue, 3) aReturnValue(iIndexValue,0) = sMatchedValue aReturnValue(iIndexValue,1) = iLineCount+1 aReturnValue(iIndexValue,2) = iOccurrence aReturnValue(iIndexValue,3) = sListOfMatchFoundItems iIndexValue = iIndexValue + 1 End If Problem is I am getting subscript out of range in ReDim Preserve aReturnValue(iIndexValue, 3) I checked it on the net... I came to know that When usi...

Macro Virus?
I may have picked up some sort of macro virus (Excel 2003) that is writing to File - Properties. How do I search for all Excel Macros on my computer? Where do I look for Macros that run every time Excel is started? What is the file extension for Excel macros? Thank you. Macros exist in workbooks (*.xls, *.xlt, *.xla and anything else the developer wants to use...). You can look in your XLStart folder and under Tools|Addins to start. Chip Pearson has some notes on how to diagnose startup errors at: http://www.cpearson.com/excel/StartupErrors.htm And Jan Karel Pieterse has more notes...

proper name conversion
I have a database that has names of people in one field, in a last name, first name order. (example: Smith, John) How can i seperate the last and first names from one text field into two different fields? Or at least have only the last name. thanks for any help. Howard wrote: >I have a database that has names of people in one field, >in a last name, first name order. (example: Smith, John) >How can i seperate the last and first names from one text >field into two different fields? Or at least have only >the last name. thanks for any help. > > This is air ...

Chart title = Worksheet Name?
I have a workbook where every sheet represents a different day. The title of the sheet is the date e.g. 9-14-06. On each sheet is a chart whose title is same date as on the tab, but formatted slightly differently: Thursday <cr> September 14, 2006. Is there a way to easily link the two so when I update the tab date, the chart date changes also? Thanks, Bill Halper I do hope someone can find a shorter way but here is mine. Firstly, the name on the tab will not be recognized as text by Excel, so wee need to get the sheet name into a cell and then extract year, month day A1 (sheet na...

runtime error 2465: application-defined or object-defined error
I'm trying to use a button on a main form to change the sort order of a sub- subform.When I execute the following, I get runtime error 2465: application- defined or object-defined error. opting to debug, I find the line with orderbyon highlighted. Anybody see what's wrong? Private sub timesort_click() Forms![control]![worklist]![approved].Form.OrderBy = "Forms![control]! [worklist]![approved]![time]" Forms![control]![worklist]![approved].Form.OrderBy0n = True End Sub -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/a...

Mutual Fund Names and Stock Names displayed on multiple lines
In 2005 Deluxe, Portfolio Manager, Group by Investment Type, the same mutual fund is incorrectly listed on multiple lines. And, if I move the cursor over different lines, the names sometime change. The mkt values appear correct, but the fund names are all messed up. Is there an update or service pack ready yet to fix this and/or other problems? -- Greg Have the same problem with money 2005 canadian version the Symbol,Name,Last Price and %Change change to the line the cursor was on before all else stay the same. Rudolf "Greg" wrote: > In 2005 Deluxe, Portfolio Manage...

Defining a variable to = MATCH
Dim thingy As Integer thingy = "=MATCH(""DiceC"",qperiodagentperformance!A:A,0)" ActiveCell.Formula = _ "=INDEX(qperiodagentperformance!D" & thingy & ":D13000,MATCH(""Agen Summary"",qperiodagentperformance!A" & thingy & ":A13000,0))" Range("D26").Select my thingy doesnt seem to work..... no comment Any suggestions on correcting this? Mik -- Message posted from http://www.ExcelForum.com Try with thingy = Application.Match("DiceC", Sheets("qperiodagentperformance"...

VBA Conditional Formating Using Logical Expression
I am new to VBA so please provide as much explanation in your responses as possible as I would love to learn more. I have a set of data (call it range 1) that resides in specific cells (all in one column) and does not change. I have another set of data (call it range 2) that is spread across 5 columns and occupies the same rows as range 1. Range 2 values in each of the 5 columns relate to the Range 1 data in the same row. I need to set up more than three conditions using >, <, = or n/a to determine the color of each of the cells in range 2. As data is entered into th...

Naming a formfield
I am using Word 2003, I have added a formfield with code as shown below, declared the ffield as a formfield but the code is debugging with object variable not set for some reason on the '.Name'. I have successfully used this bit of code in other templates I have created. Can anyone offer me an alternative or solution please? Set ffield = _ ActiveDocument.FormFields.Add(Range:=Selection.Range, _ Type:=wdFieldFormTextInput) With ffield .Name = "contaminant" With .Text...

Order of Names in E-mail differs from that in Contacts (Outlook 2002)
Hi! I'm using Outlook 2002 and my contacts are all in the order of 'Surname, First Name'. When I open up a new message and click on 'To' to address it the names are all in the order of 'First Name Surname'. How can I change the order to 'Surname, First Name'? Thanks in anticipation. Andrew In news:%23DyiAASlFHA.1044@tk2msftngp13.phx.gbl, Andrew Mawby <andrew@mawbya.plus.com> typed: > Hi! > > I'm using Outlook 2002 and my contacts are all in the order of 'Surname, > First Name'. When I open up a new message and click...

how to do self-defined regression in excel?
Hi, I want to do regression ananlysis in excel using a sigmoid function, which is not offered in the general 6 functions in excel(by adding trendline). How can I do it? Thanks. The Analysis ToolPak - VBA addin has a regression function and probably everything else you want. Tools > AddIns then checkmark Analysis ToolPak - VBA Go back to Tools dropdown and it should be listed near the bottotm of the dropdown. >-----Original Message----- >Hi, I want to do regression ananlysis in excel using a >sigmoid function, which is not offered in the general 6 >functions in excel(by a...

using IIF to determine form state
hey all, I have a query that displays the content of a cd. on the main form i work with, the media form can be displayed as a subform of either Forms!frmMain!subfrm or Forms!frmMain!subfrmflt In the criteria for one of the fields, it references a value on the media form ([Text60]) this is how i'm trying to set the criteria: "[Forms]![frmMain]![" &iif(forms!frmmain!subfrmflt.visible=true,"subfrmflt","subfrm") & "]![Text60]" basically throwing an iif in the middle to determine whether the floating form is visible. I know it will work if ...

User-Defined type not defined?
I recently imported all my data into a blank DB now I am getting this error on: Dim wrk As Workspace Is that something to do with me importing into a new DB Thanks for any help...........Bob Sub SelAllNone(Optional SelectAll As Boolean = True) On Error GoTo stoprun Dim sqlStr As String Dim wrk As Workspace Dim db As Database Set wrk = DBEngine.Workspaces(0) Set db = CurrentDb sqlStr = "UPDATE [tblHorseInfo] SET [Worksheet] = " & SelectAll & ";" wrk.BeginTrans db.Execute sqlStr, dbFailOnError wrk.CommitTrans Exit_Here: Set wrk = Nothing Set db = Nothing ...

install printers using UNC link
We just moved from Outlook97 to Outlook2000 SP3. In 97, I was able to send a UNC link to a printer and users could install the printer from that link. In the new system, I can not do this. Is there a setting that I need to turn this feature on with? ...

Using Links & inserting rows into Excel
I'm working with several worksheets in an Excel 2003 workbook. I have used the 'Paste Special' & 'Paste Link' feature to successfully link sections of different worksheets. However, if cells or rows are inserted in the 'Master' worksheet, the changes are not made via the link. Is there a way to overcome this? Thank-you in advance. Angie H. Perhaps something along these lines .. Assume a sheet named: Master In the slave sheet Put in A1: =OFFSET(Master!$A$1,ROWS($A$1:A1)-1,COLUMNS($A$1:A1)-1) Fill across and down to cover the max expected working range...

user defined fields 01-12-10
I have created a user defined field "GHINno". Using copied code I can emumate user defined fields and find "GHINno". I have code the Sets folder to the "Folder"- "Set MyContact = MyFolder.Folders("Seven Hills Mens Club")". But I can't figure out how to get to the value(s) in "GHINno", either the first value or loop through all values. Any help with UserProperty or Userproperties or whatever would be appriciated. Joe See http://www.outlookcode.com/article.aspx?ID=38 for info on property syntax. You can use a F...

Can't find source of Application-defined or object-defined error!?
I won't post all of the code here because it's too extensive but here's a watered down version (all variables declarations are not listed): Dim i as integer Dim PortName As String Dim ReviewSht As String Dim ModelSht As String Dim HoldingsSht As String Dim Model As Range PortName = Right(ActiveSheet.Name, Len(ActiveSheet.Name) - 15) ReviewSht = "Price Weight - " & PortName ModelSht = "Model Data - " & PortName HoldingsSht = "Holdings - " & PortName Application.ScreenUpdating = False With Worksheets(ModelSht).Range("A9") Range...

Range names list via ADO
Hello, I'm trying to get the range names list via ADO.Net, but can't get informations about this :-( Do you have an idea for me ? Many thanks. Christophe ...

"too many fields defined" error
I am trying to add a field to my table and keep getting an error message "too many fields defined" and it will not let me add the new field. Does anyone know what I have done to get this error and how I can fix it so I can add fields to my database? How many fields do you have in your table? The maximum number for a single table is 255. Note: it's rare that you would need more than 30 fields in a table if you've properly normalized your table structure. -- Ken Snell <MS ACCESS MVP> "Rachel" <Rachel@discussions.microsoft.com> wrote in ...