Welcome to WebmasterWorld Guest from 18.104.22.168 , register , free tools , login , search , pro membership , help , library , announcements , recent posts , open posts Become a Pro Member
VBA code is driving me bananas named ranges DrDoc msg:3947480 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.
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 msg:3948268 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 msg:3948775 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 msg:3948937 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.
swa66 msg:3949101 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*