1

Excel 2007

I need to put a value into a known column for the caller's row...

Function ProjectDaysThisMonth(theDate As Date) As Double

    Dim d1 As Date
    Dim d2 As Date
    Dim rngCaller As Range

    ' start and end of month
    d1 = theDate - Day(theDate) + 1
    d2 = Application.EoMonth(d1, 0)

    ' where-ever this function was called from the sheet
    Set rngCaller = Application.Caller

    ...

    ' workdays_thismonth was set to the # of days of month from where this function was called on the sheet

    ' remaining_days was set to previous months work and work done this month

    ' now once you're in a certain certain scenario 
    ' put the finish date into a known column for this row:
    Worksheets("Plan").Cells(rngCaller.Row, 4).value = DateAdd("d", workdays_thismonth + remaining_days, d1)

    ' above, she blows up good... no worky
    ...

    End Function

When I try to set the value of another cell, it simply falls apart. Should I use the Evaluate function? Something like...

 rngCaller.Parent.Evaluate "setValue(" & rngCaller.Address(False, False) & "," & newDateValue & ")"

Or is there a cleaner way??

Community
  • 1
  • 1
Robert Achmann
  • 1,986
  • 3
  • 40
  • 66
  • How is it getting called? Can't set to `activecell`? – findwindow Dec 28 '15 at 19:45
  • What does "it simply falls apart" mean. Can you give an error message? – Thomas Hilbert Dec 28 '15 at 19:45
  • 1
    You can't use an UDF to change the value of a different cell than the caller, without some funky sidestepping. See [here](http://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet). It shows how to change the color, but it could be modified to change the value. So you are on the right track with evaluate to call the sub which will then change the value. – Scott Craner Dec 28 '15 at 19:54
  • @ThomasHilbert - fall apart - the function exists and effectively restarts so the Excel app sometimes thinks I'm calling the function recursively and tells me that, or just shows #Value! Error – Robert Achmann Dec 29 '15 at 14:40
  • @findwindow - it's just called via cell =ProjectDaysThisMonth(G1) for example, if the column is in G – Robert Achmann Dec 29 '15 at 14:41
  • @ScottCraner - I think I'm thinking about this the wrong way. I need to have a separate function to run for that column's field and not try to update it in this function – Robert Achmann Dec 29 '15 at 14:42

0 Answers0