0

I have the following VBA macro in my Excel workbook

Function SetValue(ByRef myRange As Range) As Integer
    SetValue = 5
    
    myRange(1, 1).Font.Color = RGB(255, 0, 255)
    myRange(1, 1).Value = 10
End Function

Setting the font color works; however, setting the value causes my macro to terminate early. i.e., if I step through this in the VBA debugger, it just "ends" on the "Value = 10" line.

I've tried this syntax, but I see the same behavior:

myRange.Cells(1, 1).Value = 10

Also, it's not just the "value" field that I can't modify; I see the same behavior with this:

myRange.Cells(1, 1).Interior.Color = RGB(255, 0, 0)

Any ideas as to what's going on here? MS docs seem to indicate that these parameters ("value" and "interior color") are both readable and writeable. If it matters, this is with Office 365.

Kevin Furrow
  • 1
  • Are you calling this function from a sheet? – GSerg Jul 18 '21 at 13:44
  • 1
    A function can only return a value. It cannot modify the characteristics of the worksheet. Put this routine in a sub. Note that your function is not returning anything. – Ron Rosenfeld Jul 18 '21 at 13:58
  • https://wellsr.com/vba/2016/excel/how-to-change-another-cell-with-a-vba-function-udf/ – Storax Jul 18 '21 at 14:02
  • @RonRosenfeld A function in general can modify the characteristics of the worksheet. Same function called from a worksheet cannot. This function does return `5`. – GSerg Jul 18 '21 at 14:04
  • @Storax https://stackoverflow.com/questions/52698777/excel-office-365-restarts-automatically-while-executing-udf#comment92324771_52698777 – GSerg Jul 18 '21 at 14:05
  • @GSerg: Yes, right, but I've done that before and XL did not crash. But, for sure, using a UDF like that should probably only be the last resort. https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet – Storax Jul 18 '21 at 14:17
  • myRange is set outside function. How are you doing it - what is its value? Sheet not protected perchance? – Chris Maurer Jul 18 '21 at 17:21
  • @GSerg Technically you are correct. If he were calling this function from a VBA macro (or even from the VBA Immediate window) , then, yes, it would have completed successfully with the function returning a value of 5, and the range argument cell showing a value of 10 with the color. Without successful completion, it doesn't return anything (actually returns an error: `#VALUE!`). And yes, it is possible to use a Function within a Sub to alter a cell characteristic, but I don't think that is what is happening here. – Ron Rosenfeld Jul 18 '21 at 17:34
  • @ChrisMaurer `myRange` is the argument to the function. – Ron Rosenfeld Jul 18 '21 at 17:35
  • @GSerg And I don't understand why it changes the FontColor of `myRange(1,1)`, even when called from the worksheet. It seems my initial statement about VBA functions was not sufficiently detailed – Ron Rosenfeld Jul 18 '21 at 17:40
  • Thanks for the replies... Yes, I was trying to call my function from an Excel sheet. I wound up restructuring my code to avoid modifying "other" cells, as it seems like the "backdoors" outlined in the links are things that MS might close in some fashion in the future. – Kevin Furrow Jul 19 '21 at 10:30

0 Answers0