1

In Excel's VBA I want to create a formula which both takes the value from the source cell and the format.

Currently I have:

Function formEq(cellRefd As Range) As Variant
    'thisBackCol = cellRefd.Interior.Color
    'With Application.Caller
    '    .Interior.Color = thisBackCol
    'End With
    formEq = cellRefd.Value
End Function`

This returns the current value of the cell. The parts that I have commented out return a #VALUE error in the cell. When uncommented it seems the colour of the reference is saved however the Application.Caller returns a 2023 Error. Does this mean that this is not returning the required Range object?

If so how do I get the range object that refers to the cell that the function is used? [obviously in order to set the colour to the source value].

AER
  • 1,549
  • 19
  • 37
  • 2
    Functions used as a UDF can only return a value - by themselves they cannot change anything else about the cells they're called from or other cells. – Tim Williams Aug 10 '16 at 02:59
  • No other way around this? This seems like a massive, and very odd flaw in the application of VBA. There may be a good reason but it seems like an inconsistency in the way VBA treats the range object. – AER Aug 10 '16 at 03:09
  • Could I call a separate sub that does this outside of the function? Or could I approach this from any other way? I'm guessing that this may be to do with the continuous updating that Excel does when the source cell changes value. – AER Aug 10 '16 at 03:12
  • 1
    Here's an approach which seems to be usable, but you will need to test to make sure it doesn't have any unintended consequences: http://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet/23437280#23437280 – Tim Williams Aug 10 '16 at 03:36
  • @TimWilliams our link is great. A few notes, you can't use application caller presumably as that refers to other aspects of the sheet and a self-cell reference when used as in the example creates a circular reference which freezes Excel – AER Aug 10 '16 at 05:05
  • 2
    If you need to refer to the calling cell (eg using `Application.Caller` or `Application.ThisCell`) then you can do that in the UDF itself, and pass the address as an argument to the code called using `Evaluate`. To be safe it's best to have the UDF return a value to the calling cell, and not have the Evaluate'd code update that cell, or you might well end up in a loop. – Tim Williams Aug 10 '16 at 05:34
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/120565/discussion-between-aer-and-tim-williams). – AER Aug 10 '16 at 05:42

2 Answers2

1

This is the solution I found to the above question using Tim William's magic:

Function cq(thisCel As Range, srcCel As Range) As Variant
    thisCel.Parent.Evaluate "colorEq(" & srcCel.Address(False, False) _ 
    & "," & thisCel.Address(False, False) & ")"
    cq = srcCel.Value
End Function

Sub colorEq(srcCell, destCell)
    destCell.Interior.Color = srcCell.Interior.Color
End Sub

The destCell is just a cell reference to the cell in which the function is called.

The interior.color can be exchanged or added to with other formatting rules. Three extra points to note in this solution:

  1. By keeping the value calculation in the formula this stops the possibility for circular referencing when it destCell refers to itself. If placed in the sub then it continually recalculates; and
  2. If the format is only changed when the source value is changed, not the format as this is the only trigger for a UDF to run and thus change the format;
  3. Application.Caller or Application.ThisCell cannot be integrated as when it refers to itself and returns a value for itself it triggers an infinite loop or "circular reference" error. If incorporated with an Address to create a string then this works though as per Tim William's answer.
Community
  • 1
  • 1
AER
  • 1,549
  • 19
  • 37
  • Am I right, it isn't possible to use this technique to return a value and format this value directly? (e.g. generating text as return value of the function and directly format some words in this text with bold/italic/underline) If this isn't possible, what would be the best workaround for this? – Andy Apr 08 '19 at 01:15
  • @Andy what do you mean by directly "directly"? I got this working 3 years ago, the color copied across, and the value was taken across as well. I used it for marking dodgy answers red and propagating that warning across spreadsheets. – AER Apr 08 '19 at 01:20
  • With "directly" I mean, that the function is returning/building a larger string and I want to format some words of this string and return it to the cell where the formula is stored. Like: "This **is** a _test_ string." Or in other words: Returning text with some formatted words in it. – Andy Apr 08 '19 at 01:30
  • @Andy I see, haven't tried that. As you say, you can't format the string directly. This uses a source cell format and copies that across. You could try recording a macro and see how text is formatted directly and put that in the `Evaluate` as that is the only way to "sneak" in formatting. – AER Apr 08 '19 at 01:39
  • Otherwise I'm not sure sorry – AER Apr 08 '19 at 01:39
  • Basically it would work like this: https://stackoverflow.com/questions/11644566/excel-vba-set-bold-specific-text-of-string-into-the-cell but as it is a return-value, the solution (if it exists) must return the string AND then "trigger" the formatting sub. I'll try it together with https://support.microsoft.com/en-us/help/213612/how-to-run-a-macro-when-certain-cells-change-in-excel and a dynamic named range which covers the cells with the formula. – Andy Apr 08 '19 at 01:45
  • 1
    Remark: Worksheet_Change isn't working when the result of a formula is updating. It looks like Worksheet_Calculate should be used (see https://www.extendoffice.com/documents/excel/4618-excel-vba-run-macro-when-cell-formula-result-changes.html) – Andy Apr 08 '19 at 02:15
1

Here's one approach showing how you can still use ThisCell:

Function CopyFormat(rngFrom, rngTo)
    rngTo.Interior.Color = rngFrom.Interior.Color
    rngTo.Font.Color = rngFrom.Font.Color
End Function



Function formEq(cellRefd As Range) As Variant
    cellRefd.Parent.Evaluate "CopyFormat(" & cellRefd.Address() & "," & _
                         Application.ThisCell.Address() & ")"
    formEq = cellRefd.Value
End Function
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • To use between worksheets and workbooks include that in `CopyFormat` as Worksheet and Workbook name objects. – AER Aug 10 '16 at 07:19