Welcome to WebmasterWorld Guest from 54.144.108.92

Forum Moderators: incrediBILL & lawman

Message Too Old, No Replies

VBA code is driving me bananas

named ranges

     
7:14 am on Jul 7, 2009 (gmt 0)

Senior Member

WebmasterWorld Senior Member drdoc is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Mar 15, 2002
posts:6807
votes: 0


I have a very simple VBA code, running inside an Excel macro.
The macro itself is a function, used once in the workbook.

The function takes one argument -- a named range.

This works:

MsgBox ThisWorkbook.Names("NameOfRange").RefersToRange.Value

The value of the named range is accurately displayed.

This does NOT work:

ThisWorkbook.Names("NameOfRange").RefersToRange.Value = "foo"

Nothing gets printed to the named range, and the function itself returns "#VALUE!".

Does anyone have any ideas at all as to what the problem might be?

9:04 am on July 8, 2009 (gmt 0)

New User

5+ Year Member

joined:June 12, 2009
posts:28
votes: 0


hi DrDoc it looks like a type prob to me. Have you tried casting the ThisWorkbook.Names("NameOfRange").RefersToRange.Value to a string...something like if cstr(ThisWorkbook.Names("NameOfRange").RefersToRange.Value) = "foo" then
blah..blah..blah
else

write out boohoo!
end if

11:03 pm on July 8, 2009 (gmt 0)

Senior Member

WebmasterWorld Senior Member drdoc is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Mar 15, 2002
posts:6807
votes: 0


Thanks, but it unfortunately doesn't work.
The value is also already a string (when I check the type).

Doing anything else you could normally do on a range works (.AddComment, for example) ... but not changing the value.

4:42 am on July 9, 2009 (gmt 0)

Senior Member

WebmasterWorld Senior Member drdoc is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Mar 15, 2002
posts:6807
votes: 0


Well, turns out that when the code was run as a function, or executed by a function, it would not work. Having it as a normal sub that triggers based on a given condition (even if said condition is triggered by a function), it works.

Awkward.

10:23 am on July 9, 2009 (gmt 0)

Senior Member

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Aug 7, 2003
posts:4783
votes: 0


And then to think of all the decisions people make based on calculations made using excel.
*Shudders*
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members