.ThisCell does not work properly when opening new workbook

Question

I have a follow-up question on my previous one which can be found here: user-defined formula shows error when opening new workbook

I adjusted my code as shown below. However, each time I am opening another workbook (with macros), the formula shows an error. What am I missing? I added Set WB = Application.ThisCell.Worksheet.Parent, but the error still appears.

I tried to implement a Do Until error dissipates loop, but this is way too complicated for my machine.

Function cons_market_values() As Double
    Application.Volatile True
    Dim wb As Workbook: Set wb = Application.ThisCell.Worksheet.Parent
    Dim total_market As Double: total_market = 0#
    Dim ws As Worksheet: For Each ws In wb.Worksheets
        If IsError(ws.Cells.Find("Kurswert in Fondswährung", LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Value) Then GoTo NextIteration
        If ws.Cells.Find("Kurswert in Fondswährung", LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Value = "N/A" Then GoTo NextIteration
        Dim foundCell As Range: Set foundCell = ws.Cells.Find(What:="Kurswert in Fondswährung", LookIn:=xlValues, LookAt:=xlWhole)
        Debug.Print foundCell.Offset(0, 1).Value
        If Not foundCell Is Nothing Then
            total_market = total_market + foundCell.Offset(0, 1).Value
        End If
NextIteration:
    Next ws
    cons_market_values = total_market
End Function

Here the code that is used once the new workbook opens. It seems this is causing the error, i.e., that the UDF do not work anymore.

Private Sub Workbook_Open()
'------------------------------------------------
    If Application.WorksheetFunction.Max(ThisWorkbook.Sheets("log").ListObjects("tbl_log_historic").HeaderRowRange.Offset(-1, 0)) <> ThisWorkbook.Sheets("Immobilien weekly").Range("rng_week_one").value - 7 Then
        MsgBox "XXX", vbInformation + vbOKOnly, "Hinweis"
        Call eRoll.rolling_open
    End If

    ThisWorkbook.Sheets("dashboard").Activate
    ThisWorkbook.Sheets("dashboard").Range("D7").Activate
    ThisWorkbook.Sheets("dashboard").Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
''------------------------------------------------
End Sub

The code called eRoll.rolling_open looks as follows:

Sub rolling()
'-------------------------------------
Dim WS As Worksheet: Set WS = ThisWorkbook.Sheets("Immobilien weekly")
Dim tbl As ListObject: Set tbl = WS.ListObjects("tbl_fonds_weekly")
Dim tbl_log_historic As ListObject: Set tbl_log_historic = ThisWorkbook.Sheets("log").ListObjects("tbl_log_historic")
Dim tbl_log_entries As ListObject: Set tbl_log_entries = ThisWorkbook.Sheets("log").ListObjects("tbl_log")
Dim rng_date As Range: Set rng_date = WS.Range("rng_week_one")
Dim j As Integer, rng_found As Range, rng_date_found As Range
'-------------------------------------
tbl_log_historic.ListColumns.Add
tbl_log_historic.HeaderRowRange.Cells(1, tbl_log_historic.Range.Columns.Count).value = rng_date.value - 7
tbl_log_historic.HeaderRowRange.Cells(1, tbl_log_historic.Range.Columns.Count).Offset(-1, 0).value = rng_date.value - 7
Dim n As Integer: For n = 1 To tbl.DataBodyRange.Rows.Count

TryAgain:
On Error Resume Next
    Set rng_found = tbl_log_historic.ListColumns("ISIN").DataBodyRange.Find(tbl.DataBodyRange.Cells(n, tbl.ListColumns("ISIN").Index), LookIn:=xlValues, LookAt:=xlWhole)
    j = tbl_log_historic.ListColumns(CStr(rng_date.value - 7)).Range.Column

If (Not rng_found Is Nothing) Then
    tbl.DataBodyRange.Cells(n, tbl.ListColumns("1").Index).Copy: ThisWorkbook.Sheets("log").Cells(rng_found.Row, j).PasteSpecial Paste:=xlPasteValues
    Else:
        tbl_log_historic.ListRows.Add
        ThisWorkbook.Sheets("log").Cells(tbl_log_historic.Range.Rows(tbl_log_historic.Range.Rows.Count).Row, tbl_log_historic.ListColumns("ISIN").Range.Column).value = tbl.DataBodyRange.Cells(n, tbl.ListColumns("ISIN").Index).value
        GoTo TryAgain
End If
Next n
'-------------------------------------
For n = tbl.ListColumns("2").Index To tbl.Range.Columns.Count
    tbl.ListColumns(n).DataBodyRange.Copy: tbl.ListColumns(n - 1).DataBodyRange.PasteSpecial Paste:=xlPasteValues
    tbl.ListColumns(n).DataBodyRange.ClearContents
Next n
'-------------------------------------
Dim target_row As Integer: target_row = tbl_log_entries.DataBodyRange.Rows.Count + tbl_log_entries.HeaderRowRange.Row + 1
ThisWorkbook.Sheets("log").Cells(target_row, tbl_log_entries.ListColumns("Nutzer").Range.Column).value = Environ("Username")
ThisWorkbook.Sheets("log").Cells(target_row, tbl_log_entries.ListColumns("Datum Eintrag").Range.Column).value = Date
ThisWorkbook.Sheets("log").Cells(target_row, tbl_log_entries.ListColumns("Fonds").Range.Column).value = "Weekly Roll"
ThisWorkbook.Sheets("log").Cells(target_row, tbl_log_entries.ListColumns("Datum").Range.Column).value = rng_date.value
'-------------------------------------
End Sub

Are you sure that is what is failing and not the `.find` method? — Warcupine, Jul 25 '22 at 15:08
If `find` fails to find something it returns an empty object, empty objects have no `.offset` property. Since you are looping through the worksheets and adding the `.find` return directly to the arraylist I find it hard to believe this would ever run without throwing that error. You need to break that into multiple lines and test the return of `.find` — Warcupine, Jul 25 '22 at 15:24
something like this https://stackoverflow.com/questions/42565381/catch-find-errors-in-excel-when-what-is-not-found — Warcupine, Jul 25 '22 at 15:26
Thanks for explaining that further. Actually, I deleted those checks in my code above to reduce the noise for the forum. My checks come first in the ```For Each``` loop before adding something to the array. So, this should actually not be the reason why the code crashes when I am opening a new workbook even though my code above indeed would. — ezvk, Jul 25 '22 at 15:32
Is this in an add-in? If the formula code is in the same workbook as the formula cell then why not use `ThisWorkbook` in place of `Application.ThisCell.Worksheet.Parent` ? — Tim Williams, Jul 25 '22 at 15:53
I added ```Application.Volatile True``` at the top of my function, but it's not the solution to my problem. Any thoughts? — ezvk, Jul 25 '22 at 16:05

score 0 · Answer 1 · answered Jul 25 '22 at 17:15

0

Running your code as-is, I do get an error #VALUE! in the cell on the worksheet. This is because your Find statement is not finding the value. You mention that there is error-checking code in the "full" version of your function, but we can't see that in your post.

My testing shows that simply separating the Find statement into multiple lines (which does NOT slow the function down at all!) and checking for the value seems to fix the error, at least in my test workbooks.

Option Explicit

Function show_market_values() As Double
    Dim wb As Workbook
    Set wb = Application.ThisCell.Worksheet.Parent
    
    Dim arValues As Collection
    Set arValues = New Collection
    
    Dim total_market As Double
    total_market = 0#
    
    Dim ws As Worksheet
    For Each ws In wb.Worksheets
        Dim foundCell As Range
        Set foundCell = ws.Cells.Find(What:="", _
                                      LookIn:=xlValues, _
                                      LookAt:=xlWhole)
        If Not foundCell Is Nothing Then
            total_market = total_market + foundCell.Offset(0, 1).Value
        End If
    Next ws
    show_market_values = total_market
End Function

answered Jul 25 '22 at 17:15

PeterT

8,232
1
17
38

Thanks! I used your code, but it didn't work. I adjusted my initial question above combining your code with my checks which still were necessary. The problem remains, however, when I open a new workbook that contains macros itself. Am I missing something? – ezvk Jul 25 '22 at 19:01
I figured out that the error only appears if the new workbook contains a code that is initiated at the workbook open. Otherwise my UDF does not give an error. At the workbook open of the new workbook I am using ```ThisWorkbook```. – ezvk Jul 25 '22 at 19:11
Running code in another workbook, using `ThisWorkbook`, will not "reset" which workbook any of your code is referencing. But it is highly suspicious that some code running on open would affect this code as well -- to provide any more detailed analysis here, we'll have to see that code also. – PeterT Jul 25 '22 at 19:51
Another comment on your updated code though, my personal habit is to write statements once. In your case, the `Find` statement is forced to run twice (if it does find the target value). That's why my short code example creates an intermediate variable `foundCell` and uses that as the test. – PeterT Jul 25 '22 at 19:53
I added the additional code in my initial question above. Now, it gets quite complex... – ezvk Jul 25 '22 at 19:56

.ThisCell does not work properly when opening new workbook

1 Answers1