Get UDF to recalculate without making it volatile or troubleshooting if it is

[glitterbeth]

I hope I'm phrasing my problem adequately. I'm not a particularly experienced coder, and am really brand new to user defined functions, and this is my first time posting to this forum.

So, I've got a workbook SampleSheet.xlsx and a handy dandy User Defined Function:

Function ConsultantBillings(phase As Range)
'    Application.Volatile (True)
    Dim CBSheet As Worksheet
    Dim SubtotalCostPhase As Range

    For Each CBSheet In ActiveWorkbook.Worksheets
        If InStr(CBSheet.Name, "CB") <> 0 Then
            Set SubtotalCostPhase = CBSheet.Cells.Find(What:=phase.Value, After:=CBSheet.Range("A22"), LookIn:=xlValues, SearchOrder:=xlByRows).Offset(2, 0)
            ConsultantBillings = ConsultantBillings + SubtotalCostPhase.Value
        End If
    Next CBSheet

End Function

If the Application.Volatile line is not commented out, the function works great for the most part, and updates as I add and remove sheets whose names contain "CB" to my workbook and change the information in these sheets. Since different projects will require different numbers of consultant sheets that folks will want to rename to something meaningful, I'm not sure how to pass that information into my function arguments in a way that would force recalculation. Help with this would be ideal.

But, I understand it is generally not a good idea to make UDFs volatile. Also, this brings me problems down the line. After I've done all my calculations, I also want to be able to do some data harvesting, by grabbing specific sheets from all workbooks in a folder and putting the values of the cells into worksheets in a new workbook. I've tried code that does paste special values and that just tries to set the values equal to one another (see code below, for privacy I put "..." instead of my network path in three places), and in either case the consultant billings column of my worksheet zeros out (unless it's not volatile). I can replicate this behavior manually without running all that code below by selecting all the cells in the worksheet, copying, and then trying to paste special --> values into a new worksheet twice in a row. It typically works on the first new worksheet, and then zeros out the consultant billings calculations on the second new worksheet.

All help is appreciated. Thanks.

-Beth

Sub CreateCombinedWorkbook()

'Great unattributed code from Internet is involved (Do while loop).

    Dim resp2 As Integer
    Dim bigWkbk
    Dim wkbk As Workbook
    Dim Filename As String
    Dim Path As String

    Dim wksht As Worksheet
    Dim bigWkshtName As String
    Dim wkshtLastCell As String

    Path = "\\...\CurrentMonth\"
    
'    Application.DisplayAlerts = False
'    Application.ScreenUpdating = False
    
'Check to see if worksheet exists already and instruct user to move it if it does.
    If Dir("\\...\CurrentMonth\_CurrentMonthCombined.xlsm") <> "" Then
        resp2 = MsgBox("Please move existing file _CurrentMonthCombined to another directory and try again.")
        If resp2 = 1 Then
            Exit Sub
        End If
    End If
    
'Make sure this happens after the above Dir command!
    Filename = Dir(Path & "*.xls*")
    Set bigWkbk = Workbooks.Add
    
'Create _CurrentMonthCombined.xlsm Workbook
    bigWkbk.SaveAs Filename:= _
        "\...\CurrentMonth\_CurrentMonthCombined.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

'OPEN EXCEL FILES
    Do While Len(Filename) > 0  'IF NEXT FILE EXISTS THEN
        Set wkbk = Workbooks.Open(Path & Filename)
    
        Application.Run "PERSONAL.XLSB!RenameWorksheets"
        For Each wksht In wkbk.Worksheets
            If (InStr(wksht.Name, "Projections") <> 0) Then
'Copy and paste special values and formats from projections sheet to new sheet in combined workbook
                bigWkshtName = wksht.Name
                wkshtLastCell = wksht.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).AddressLocal
                wksht.Range("H3", "H22").Dirty
                
                wksht.Activate
                With ActiveSheet
                  .EnableCalculation = False
                  .EnableCalculation = True
                  .Calculate
                End With
                
'                wksht.Cells.Copy
                bigWkbk.Activate
                bigWkbk.Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = bigWkshtName
                bigWkbk.Sheets(bigWkshtName).Activate
                ActiveSheet.Range("A1", wkshtLastCell).Value = wksht.Range("A1", wkshtLastCell).Value
                ActiveSheet.Range("A1", wkshtLastCell).NumberFormat = wksht.Range("A1", wkshtLastCell).NumberFormat
                wksht.Cells.Copy
'                Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                bigWkbk.Sheets(bigWkshtName).Range("A1").Activate
                Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                Application.CutCopyMode = False
            End If
        Next wksht
    wkbk.Close True
    Filename = Dir
Loop

'Save, deleting excess sheets if they exist
    bigWkbk.Activate
    Sheets(Array("Sheet2", "Sheet3")).Delete

    ActiveWorkbook.Save
'    Application.ScreenUpdating = True
'    Application.DisplayAlerts = True
End Sub

[romperstomper]

Your issue is almost certainly the use of ActiveWorkbook in the UDF. I assume you want to refer to the workbook the function is in, in which case use ThisWorkbook instead. If however the code is in a separate workbook (eg an add-in) then you could refer to the workbook that contains the 'phase' range by using:

For Each CBSheet In phase.parent.parent.Worksheets

[glitterbeth]

Thank you so much! This solves the problem!