Assign value to a name in VBA

**SpringLily** · 09-21-2010, 04:28 AM

Dear All,
I used below to assign a value to a name in VBA but, VBA said there is an error 1004 as" application defined or object defined error".

Please Login or Register  to view this content.

Any suggestions? Where can I find some document about using Names in VBA?

Thanks.

Regards,
SpringLily

**davesexcel** · 09-21-2010, 05:00 AM

What is MyValue ?

Check this out
http://www.cpearson.com/excel/DefinedNames.aspx

**SpringLily** · 09-21-2010, 05:10 AM

myValue is defined as double and is predefined value. see below

Please Login or Register  to view this content.

Thanks.

**Andy Pope** · 09-21-2010, 05:13 AM

Macro recorder would have helped point you in the right direction

Please Login or Register  to view this content.

**SpringLily** · 09-21-2010, 05:22 AM

Sorry, Andy. Myvalue is not a predefined name. It is a variable defined in the VBA sub(). I tried the formula. it seems do not work. Thanks anyway. Any other suggestions?

**davesexcel** · 09-21-2010, 05:23 AM

Try this,

Please Login or Register  to view this content.

**SpringLily** · 09-21-2010, 05:25 AM

hi, davesexcel
The name Rvalue has been defined already. Any other suggestions?

**snb** · 09-21-2010, 05:30 AM

Consider this code as an example:

Please Login or Register  to view this content.

**Andy Pope** · 09-21-2010, 05:34 AM

Not sure what the issue is when you are trying it but for me this code sets the existing named range Rvalue to the value contained in the vba variable MyValue.

Please Login or Register  to view this content.

**SpringLily** · 09-21-2010, 05:54 AM

Hi,there
I tried three methods as below:

Please Login or Register  to view this content.

It seems only A works and it changes the value in the cell where Rvalue referes to. The other two change the defination of the "Rvalue" to 0.35. Any suggestions to improve the code?
Thank.

**Andy Pope** · 09-21-2010, 06:01 AM

I misunderstood, I thought your named range was a constant.

Assuming the named range refers to a cell such as A1 then this shoud update the contents of A1

Please Login or Register  to view this content.

If you get the 1004 error it would strongly suggest that thisworkbook does not contain a named range RValue.

**SpringLily** · 09-21-2010, 06:10 AM

Thanks a lot, Andy. It works. Can you predict any problems if I use this line in a function? I used in a function and whenever code goes to this line, it finishes without runing rest of the code and of couse, gives wrong value.

**Andy Pope** · 09-21-2010, 06:13 AM

You don't say but I will guess you are trying to use the function from a worksheet cell. In which can you can not alter the contents of other cells.

**SpringLily** · 09-21-2010, 06:24 AM

You are right. Here is the problem. I have a lookup table built in Excel sheet and the value in the table changes with a parameter calculated from upstream calculations. Additionally, this talbe will be used for different cases in the same sheet and the parameter for different cases are different. Any sugguestion the best way to solve it? Thanks a lot. You are a star!

**Andy Pope** · 09-21-2010, 06:27 AM

You need to post a workbook example of your data layout and formula.

Then you need to explain in detail the input and expected outputs.

**SpringLily** · 09-21-2010, 06:40 AM

Please see attached for the spreadsheet. Thanks.

**Andy Pope** · 09-21-2010, 06:55 AM

An explanation would have been nice, save me having to back audit your formula and code.

The usual way around setting values via worksheet formula is to use the worksheet change event.

But you will have problems as you have multiple formula all wanting to change the contents is CreditTables!Q1. Once you change it for 1 formula you cause the other formula to recalculate.

What you probably need is a table of calculations for fixed Rvalues.

**SpringLily** · 09-21-2010, 07:12 AM

Based on type, different table is chosen for VLOOKUP. For type 3, Rvalue will be used to generate the look up table. Thanks.

**SpringLily** · 09-22-2010, 04:52 AM

Hi, there
I solved the problem by creating a array which contains the third table in VBA. Thanks everyone for your contribution. Regards, SpringLily

Assign value to a name in VBA

LinkBack

Thread Tools

Rate This Thread

Display

Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Re: Assign value to a name in VBA

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions