如何用VBA获取工作表级带公式定义名称的计算值？

The error occurs because shtSched.Range("Blob") tries to treat "Blob" as a range object, but it's actually a worksheet-level named formula that evaluates to a string value, not a cell range. The Range() method expects a range reference, hence the 1004 runtime error.

Below are two actionable solutions, plus additional code optimizations:

Solution 1: Directly Compute the Blob Value (Simplest & Fastest) #

Since you know the underlying formula for "Blob" (=$J$1&B$2&$I3), you can calculate its value directly in VBA relative to each cSched cell. This avoids evaluation overhead and is highly performant:

Modify the loop section of your code:

'Work through cells in shtSched, compare to data source
For Each cSched In rSched
    x = 0
    'Directly compute Blob value relative to the current cell
    BlobSched = shtSched.Range("J1").Value & _
                shtSched.Cells(2, cSched.Column).Value & _
                shtSched.Cells(cSched.Row, "I").Value
    
    For Each cData In dBlob
        x = x + 1
        If cData.Value = BlobSched Then
            'do something
        Else
            'do something else
        End If
    Next
Next

Solution 2: Evaluate the Named Formula Relative to cSched (Uses Named Range) #

If you want to retain the named range (so changes to "Blob" automatically reflect in your code), use Application.ExecuteExcel4Macro to evaluate the formula relative to cSched without activating cells:

First, add this helper function to clean up the evaluation logic:

Function EvaluateRelativeToCell(namedFormulaName As String, refCell As Range) As Variant
    Dim formulaText As String
    formulaText = refCell.Parent.Names(namedFormulaName).RefersTo
    'Escape double quotes in the formula string
    formulaText = Replace(formulaText, """", """""""")
    'Use Excel 4.0 EVALUATE function to compute relative to the reference cell
    EvaluateRelativeToCell = Application.ExecuteExcel4Macro("EVALUATE(""" & formulaText & """," & refCell.Address(External:=True) & ")")
End Function

Then update your loop to use this function:

'Work through cells in shtSched, compare to data source
For Each cSched In rSched
    x = 0
    'Evaluate Blob relative to the current cell using the named formula
    BlobSched = EvaluateRelativeToCell("Blob", cSched)
    
    For Each cData In dBlob
        x = x + 1
        If cData.Value = BlobSched Then
            'do something
        Else
            'do something else
        End If
    Next
Next

Additional Code Improvements #

1. Use Long instead of Double for row/column counts (they are integer values):

   Dim cols As Long
   Dim rows As Long
   

2. Qualify ranges properly to avoid relying on the active sheet:

   Set rSched = shtSched.Range(shtSched.Range("Start"), shtSched.Range("Start").Offset(rows - 1, cols - 1))
   

3. Optimize Inner Loop with a Dictionary (reduces O(n*m) time complexity to O(n)):
   Add this setup code before your main loop:

   Dim blobDict As Object
   Set blobDict = CreateObject("Scripting.Dictionary")
   'Populate dictionary with data blob values for fast lookups
   For Each cData In dBlob
       If Not blobDict.Exists(cData.Value) Then
           blobDict.Add cData.Value, cData.Row 'Store row number or relevant metadata
       End If
   Next
   

   Replace your inner loop with:

   If blobDict.Exists(BlobSched) Then
       'do something using blobDict(BlobSched)
   Else
       'do something else
   End If
   

内容的提问来源于stack exchange，提问作者gurs