Using a UDF in Excel to update the worksheet

by

Posting a response so I can mark my own “question” as having an answer.

I’ve seen other workarounds, but this seems simpler and I’m surprised it works at all.

Sub ChangeIt(c1 As Range, c2 As Range)
    c1.Value = c2.Value
    c1.Interior.Color = IIf(c1.Value > 10, vbRed, vbYellow)
End Sub


'########  run as a UDF, this actually changes the sheet ##############
' changing value in c2 updates c1...
Function SetIt(src, dest)

    dest.Parent.Evaluate "Changeit(" & dest.Address(False, False) & "," _
                        & src.Address(False, False) & ")"

    SetIt = "Changed sheet!" 'or whatever return value is useful...

End Function

Please post additional answers if you have interesting applications for this which you’d like to share.

Note: Untested in any kind of real “production” application.

More Related Contents:

  1. Save excel file multiple times with file names in another workbook.
  2. Sequence of numbers based on start and end value of 2 cells [closed]
  3. How to pause for specific amount of time? (Excel/VBA)
  4. Easiest way to loop through a filtered list with VBA?
  5. Quicker way to get all unique values of a column in VBA?
  6. How to set recurring schedule for xlsm file using Windows Task Scheduler
  7. Change date format using substitute or replace
  8. Parsing an ISO8601 date/time (including TimeZone) in Excel
  9. Delete a row in Excel VBA
  10. VBA: Convert Text to Number
  11. Concatenate cell data into another data if values matches
  12. Iterating through populated rows
  13. How can I run VBA code each time a cell gets its value changed by a formula?
  14. How to convert 32 bit VBA code into 64 bit VBA code
  15. Using VBA to detect which decimal sign the computer is using
  16. Loop through each row of a range in Excel
  17. Fill formula down till last row in column
  18. Outputting Excel rows to a series of text files
  19. How to create a range from two ranges in VBA?
  20. XML parse VBA excel (function trip, & MSXML2.DOMDocument)
  21. If string contains word from list, return value adjacent to list value
  22. every possible combination of the contents of 2 columns in excel
  23. excel vba freeze pane without select
  24. How to delete rows in Excel based on criteria using VBA?
  25. Converting a string representation of a constant into a constant?
  26. VBA WinHTTP to download file from password proteced https website
  27. Function Overloading and UDF in Excel VBA
  28. VBA Excel simple Error Handling
  29. How to do a “Save As” in vba code, saving my current Excel workbook with datestamp?
  30. How to save semi-colon delimited csv file using VBA?

Leave a Comment