我试图在VBA中调用工作表范围内的命名公式的结果，但无法使其工作。VBA正在做一些工作正常的事情，但这里是有问题部分的简化设置。在名为ThursTest的工作表上，有一个命名范围“Blob”，如果选择单元格B3，其公式为“=$J$1&B$2&$I3”（该公式的结果将取决于ThursTest上选择的单元格，因为第二个组件的列和第三个组件的行是可变的）。我尝试循环遍历该工作表上的一堆单元格，检查Blob的值，然后如果该值与另一个表中的值匹配，则执行一些操作。请注意，“Blob”不是任何这些单元格的公式。我想获取Blob的值，就像它是在活动单元格中输入的一样。
在我的VBA中，我将shtSched定义为Worksheet并设置为ThursTest，将“BlobSched”定义为字符串，并将shtSched.Range（“B3”）激活。我想将BlobSched设置为等于活动单元格的工作表区域“Blob”的值。我尝试了shtSched.Range（“Blob”）.Value、shtSched.Range（“Blob”）.Text和Evaluate（shtSched.Range（“Blob”）），但都失败了，并出现运行时错误1004（对象“_Worksheet”的方法“range”失败）。如何将VBA术语BlobSched设置为等于活动单元格的工作表定义的区域/公式Blob的结果？
下面是我的VBA的摘录版本：

Sub stackoverflowtest1()

Dim shtData As Worksheet 'tab with schedule data
Dim shtSched As Worksheet 'the tab where schedule blocks will be created
Dim tData As ListObject 'the data table "SchedData"
Dim BlobSched As String 'blob as named range in sheet, which is a formula
Dim rSched As Range 'range of Schedule
Dim dBlob As Range 'this is the contents of the Blob column on the data tab WITHOUT the header
Dim cols As Double 'this is the number of data columns per day
Dim rows As Double 'this is the number of data rows per day
Dim cSched As Range 'a single cell on Schedule tab
Dim cData As Range 'a single cell on Data tab
Dim x As Integer 'counter

'Setup
    Set shtData = Worksheets("Data")
    Set shtSched = Worksheets("ThursTest")
    cols = 7
    rows = 123
    Set rSched = Range(shtSched.Range("Start"), shtSched.Range("Start").Offset(rows - 1, cols - 1))

    Set tData = shtData.ListObjects("SchedData")
    Set dBlob = tData.ListColumns("Blob").DataBodyRange
    
'Work through cells in shtSched, compare to data source
    For Each cSched In rSched
        x = 0
        cSched.Activate 'change to activate?  select?  set?
        BlobSched = shtSched.Range("Blob").Value
        'BlobSched = shtSched.Range("Blob").Text
        'BlobSched = Evaluate(shtSched.Range("Blob"))
        
        For Each cData In dBlob
            x = x + 1
            If cData.Value = BlobSched Then
                'do something
            Else
                'do something else
            End If
        Next
    Next

End Sub

字符串