help References in a User Function

[Will]

Hi gang.

I am stuck on asomething that has become quite curly. Yet it seems
like it should be 'obvious'. I

The VBA code itself: "Highlight( )" works great. Except in the
specific context I _want_. This is what I want to do.

I have a user function that takes a cell reference (As Range). I want
to change the display attributes of the range given. For simplicity
conside the simple example below. The Sub works and the Function does
not work.

Can someone point me to a place that explains what I need to do so I
can use the Range from within the Function. My debugging message boxes
all reprot that "thisRange" is a range, and good to go.

Except that it doesn't "work" via the function.


Thanks in advance,
Will.

==========================[ example ]============


Function setHighlight( thisRange As Range )

Call Highlight( thisRange )

End Function

'-----------------

Sub macroHighlight( thisRange As Range )

Call Highlight( thisRange )

End Sub

'-----------------

 

[Chip Pearson]

Will,

Two things immediately come to mind. Since you didn't post the code for
Highlight, I can only take a guess. First, the Function setHighlight doesn't
set a return value. Should it?

Next, if you are calling the function setHighlight from a worksheet cell,
you should remember than VBA code, whether called directly or indirectly
from a worksheet cell, cannot change anything. It can only return a value.
If you attempt to change any part of the Excel environment, the function
will immediately exit and you'll get a #VALUE function.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Will]

Thanks Chip

I was afraid it is something like ...

c> you should remember than VBA code, whether called directly or
indirectly
c> from a worksheet cell, cannot change anything. It can only return a
value.

The setHighlight() returns a nice string saying "done". The required
effect is to _change_ the reference.

Is there some way to affect changes from inside a function? Say launch
another macro or something?

Thanks for pointing me out of a dead-end.

Cheers,
Will.