homepage Welcome to WebmasterWorld Guest from 54.237.57.53
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Local / Foo
Forum Library, Charter, Moderators: incrediBILL & lawman

Foo Forum

    
VBA code is driving me bananas
named ranges
DrDoc

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



 
Msg#: 3947478 posted 7:14 am on Jul 7, 2009 (gmt 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?

 

ang_bain

5+ Year Member



 
Msg#: 3947478 posted 9:04 am on Jul 8, 2009 (gmt 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

DrDoc

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



 
Msg#: 3947478 posted 11:03 pm on Jul 8, 2009 (gmt 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.

DrDoc

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



 
Msg#: 3947478 posted 4:42 am on Jul 9, 2009 (gmt 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.

swa66

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



 
Msg#: 3947478 posted 10:23 am on Jul 9, 2009 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Local / Foo
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved