Application.Caller read-only?

Question

I'm not able to change any calling cell attributes via Application.Caller.  This leads me to believe that Application Caller is read-only, and that we have to extract the Workbook & sheet name, then access the cell via Workbook("WorkbookName").Sheet("SheetName").etc. .  Is this correct?  My test code is below.

<...start code...>

Function AsnWrongColr() As Variant

Debug.Print

Debug.Print "Function AsnWrongColr()"

Debug.Print "-----------------------"

Debug.Print "ActiveSheet.Name="""; ActiveSheet.Name; """"

#If 0 Then

    '''Use this section if invoking from cell formula in A7

    'Confirm that we are at the right sheet

    Debug.Print "Application.Caller.Parent.Name="""; _

        Application.Caller.Parent.Name; """"

    AsnWrongColr = "Blah" 'Return value for caller to display

    'Application.Caller.Offset(1, 1) = "Bleh" 'Test access to neighbour

        'Comment out cuz execution doesn't go beyond this line

    Application.Caller.Interior.Color = vbGreen

        'Try set calling cell color

    Debug.Print "Caller color "; Application.Caller.Interior.Color

        'This will show that the cell color remains unchanged

#Else

    '''Use this section if just running the macro

    Range("A7").Interior.Color = vbGreen

#End If

Debug.Print "A7 color "; Range("A7").Interior.Color

Debug.Print "Note: vbGreen is "; vbGreen

Debug.Print

End Function

<...end code...>

Answer 1

You appear to be asking the same question multiple times, that is confusing.

A user-defined function cannot change any properties of a cell except the value of the cell that contains the formula.

Answer 2

Paul wrote:

I'm not able to change any calling cell attributes via Application.Caller.  This leads me to believe that Application Caller is read-only [...].  Is this correct?  My test code is below.

[....]

Function AsnWrongColr() As Variant

Certainly Application.Caller is read-only.  I think you mean that you believe the properties of Application.Caller are read-only.

I don't believe that is any different for Application.Caller than for any other Range object.  Some properties are read-only; others are not.

However, I believe the problem you are encountering is:  a VBA function, unlike a VBA sub(routine), is not permitted to modify the workbook directly.

Answer 3

Paul wrote:

Yes, two of my threads were related, one being a more explored version of the other.  How do I delete the earlier thread?

You cannot after there have been responses.  But generally, when you click Edit, one of the choices is Delete Post.

Answer 4

Yes, two of my threads were related, one being a more explored version of the other.  How do I delete the earlier thread?

Or perhaps it's worthwhile keeping around in case others ask questions along the lines of the first thread, and will have a greater chance of finding the answer?  I'm not sure how the knowledge archival and retrieval works for this forum, but I assume it's something along the lines of a word similarity search based on the users query.

Answer 5

Hans wrote:

You appear to be asking the same question multiple times, that is confusing.

I agree with Hans in principle.  I understand that with each new posting, you were refining the problem as you explored the it and learned on your own.  That's admirable.  But if you realized that that is what you were doing in effect, it would have been better to post your refinements as responses to your original thread -- or to post a response in older threads to indicate that you are abandoning them in favor of newer threads.  In fact, you might have been able to delete the older threads if no one had yet responded.

Something to keep in mind for the future, since you seem to be posting quite a lot of questions along these lines ("what can't I do this in VBA?").