VBA三维距离计算函数报‘编译错误: Expected: =’求助

问题描述 #

需要编写VBA函数CalcDist，从名为"Joint Coordinates"工作表的"JC"表中，根据两个关节编号查找对应的GlobalX/Y/Z坐标，计算两点间的3D距离。当前代码编译时提示compile error: Expected: =，调试无果。错误代码如下：

Function CalcDist(JointValue1 As Variant, JointValue2 As Variant) As Variant
    Dim ws As Worksheet
    Dim tbl As ListObject
    
    Dim jointCol1 As ListColumn
    Dim jointCol2 As ListColumn
    
    Dim globalXCol1 As ListColumn
    Dim globalYCol1 As ListColumn
    Dim globalZCol1 As ListColumn
    
    Dim globalXCol2 As ListColumn
    Dim globalYCol2 As ListColumn
    Dim globalZCol2 As ListColumn
    
    Dim jointCell1 As Range
    Dim jointCell2 As Range
        
    Dim globalXVal1 As Double
    Dim globalYVal1 As Double
    Dim globalZVal1 As Double
    
    Dim globalXVal2 As Double
    Dim globalYVal2 As Double
    Dim globalZVal2 As Double
    
    
    'create ref to the listobject table JC
    Set ws = ThisWorkbook.Worksheets("Joint Coordinates")
    Set tbl = ws.ListObjects("JC")
    
    'set columns by their header name
    Set jointCol1 = tbl.ListColumns("Joint")
    Set jointCol2 = tbl.ListColumns("Joint")
    
    Set globalXCol1 = tbl.ListColumns("GlobalX")
    Set globalXCol2 = tbl.ListColumns("GlobalX")
    
    Set globalYCol1 = tbl.ListColumns("GlobalY")
    Set globalYCol2 = tbl.ListColumns("GlobalY")
    
    Set globalZCol1 = tbl.ListColumns("GlobalZ")
    Set globalZCol2 = tbl.ListColumns("GlobalZ")
    
    'find match in the joint col for point1 and point2
    Set jointCell1 = jointCol1.DataBodyRange.Find(what:=JointValue1, LookIn:=xlValues, lookat:=xlWhole)
    Set jointCell2 = jointCol2.DataBodyRange.Find(what:=JointValue2, LookIn:=xlValues, lookat:=xlWhole)
    
    'if a match is found get the values from GlobalX, GlobalY, and GlobalZ columns
    If Not jointCell Is Nothing Then
    globalXVal1 = tbl.DataBodyRange.Cells(jointCell1.row - tbl.HeaderRowRange.row, globalXCol1.Index).Value
    globalXVal2 = tbl.DataBodyRange.Cells(jointCell2.row - tbl.HeaderRowRange.row, globalXCol2.Index).Value
    
    globalYVal1 = tbl.DataBodyRange.Cells(jointCell1.row - tbl.HeaderRowRange.row, globalYCol1.Index).Value
    globalYVal2 = tbl.DataBodyRange.Cells(jointCell2.row - tbl.HeaderRowRange.row, globalYCol2.Index).Value
    
    globalZVal1 = tbl.DataBodyRange.Cells(jointCell1.row - tbl.HeaderRowRange.row, globalZCol1.Index).Value
    globalZVal2 = tbl.DataBodyRange.Cells(jointCell2.row - tbl.HeaderRowRange.row, globalZCol2.Index).Value
    
    End If
    
    'math
    CalcDist = Sqr((globalXVal2 - globalXVal1) ^ 2 + (globalYVal2 - globalYVal1) ^ 2 + (globalZVal2 - globalZVal1) ^ 2)
End Function

另外，用于求和单关节坐标的函数RetGS可正常运行，代码参考：

Function RetGS(JointValue As Variant) As Variant
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim jointCol As ListColumn
    Dim globalXCol As ListColumn
    Dim globalYCol As ListColumn
    Dim globalZCol As ListColumn
    Dim jointCell As Range
    Dim globalXVal As Double
    Dim globalYVal As Double
    Dim globalZVal As Double
    'set worksheet where table JC is located
    Set ws = ThisWorkbook.Worksheets("Joint Coordinates")
    'find table object by its name
    Set tbl = ws.ListObjects("JC")
    'set columns by their header name
    Set jointCol = tbl.ListColumns("Joint")
    Set globalXCol = tbl.ListColumns("GlobalX")
    Set globalYCol = tbl.ListColumns("GlobalY")
    Set globalZCol = tbl.ListColumns("GlobalZ")
    'find the match in the joint col
    Set jointCell = jointCol.DataBodyRange.Find(what:=JointValue, LookIn:=xlValues, lookat:=xlWhole)
    
    If Not jointCell Is Nothing Then
        MsgBox "match found at row: " & jointCell.row & ", joint value: " & jointCell.Value
    Else
        MsgBox "no match found"
    End If
        
    'if a match is found get the values from GlobalX, GlobalY, and GlobalZ columns
    If Not jointCell Is Nothing Then
        globalXVal = tbl.DataBodyRange.Cells(jointCell.row - tbl.HeaderRowRange.row, globalXCol.Index).Value
        globalYVal = tbl.DataBodyRange.Cells(jointCell.row - tbl.HeaderRowRange.row, globalYCol.Index).Value
        globalZVal = tbl.DataBodyRange.Cells(jointCell.row - tbl.HeaderRowRange.row, globalZCol.Index).Value
    'err handling
    MsgBox "GlobalX: " & globalXVal & ", GlobalY: " & globalYVal & ", GlobalZ: " & globalZVal
    'math
    RetGS = globalXVal + globalYVal + globalZVal
    Else
    RetGS = "no match found"
    End If
End Function

错误原因分析 #

1. 变量名拼写错误：代码中If Not jointCell Is Nothing Then里的jointCell未定义，实际需要判断jointCell1和jointCell2是否都找到——函数必须获取两个关节的坐标才能计算距离。
2. 冗余变量定义：jointCol1和jointCol2、globalXCol1和globalXCol2等均指向同一列，重复定义完全没必要，只会增加代码冗余。
3. 缺失错误处理：未考虑其中一个或两个关节未找到的情况，此时会用默认0值计算，得到错误结果。

修正后的代码 #

Function CalcDist(JointValue1 As Variant, JointValue2 As Variant) As Variant
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim jointCol As ListColumn
    Dim globalXCol As ListColumn
    Dim globalYCol As ListColumn
    Dim globalZCol As ListColumn
    Dim jointCell1 As Range
    Dim jointCell2 As Range
    Dim rowIndex1 As Long, rowIndex2 As Long
    Dim globalXVal1 As Double, globalYVal1 As Double, globalZVal1 As Double
    Dim globalXVal2 As Double, globalYVal2 As Double, globalZVal2 As Double
    
    ' 初始化工作表和表对象
    Set ws = ThisWorkbook.Worksheets("Joint Coordinates")
    Set tbl = ws.ListObjects("JC")
    
    ' 获取列对象（无需重复定义同一列）
    Set jointCol = tbl.ListColumns("Joint")
    Set globalXCol = tbl.ListColumns("GlobalX")
    Set globalYCol = tbl.ListColumns("GlobalY")
    Set globalZCol = tbl.ListColumns("GlobalZ")
    
    ' 查找两个关节的位置
    Set jointCell1 = jointCol.DataBodyRange.Find(what:=JointValue1, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    Set jointCell2 = jointCol.DataBodyRange.Find(what:=JointValue2, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    
    ' 检查两个关节是否都找到
    If jointCell1 Is Nothing Or jointCell2 Is Nothing Then
        CalcDist = "关节未找到"
        Exit Function
    End If
    
    ' 计算数据行索引（相对于表的起始行）
    rowIndex1 = jointCell1.Row - tbl.HeaderRowRange.Row
    rowIndex2 = jointCell2.Row - tbl.HeaderRowRange.Row
    
    ' 获取坐标值
    globalXVal1 = tbl.DataBodyRange.Cells(rowIndex1, globalXCol.Index).Value
    globalYVal1 = tbl.DataBodyRange.Cells(rowIndex1, globalYCol.Index).Value
    globalZVal1 = tbl.DataBodyRange.Cells(rowIndex1, globalZCol.Index).Value
    
    globalXVal2 = tbl.DataBodyRange.Cells(rowIndex2, globalXCol.Index).Value
    globalYVal2 = tbl.DataBodyRange.Cells(rowIndex2, globalYCol.Index).Value
    globalZVal2 = tbl.DataBodyRange.Cells(rowIndex2, globalZCol.Index).Value
    
    ' 计算3D距离
    CalcDist = Sqr((globalXVal2 - globalXVal1) ^ 2 + (globalYVal2 - globalYVal1) ^ 2 + (globalZVal2 - globalZVal1) ^ 2)
End Function

优化建议 #

1. 使用Match函数提升效率：Find函数适合模糊查找，对于精确匹配的关节编号，用WorksheetFunction.Match更高效，还能直接获取行索引：

   On Error Resume Next
   rowIndex1 = WorksheetFunction.Match(JointValue1, jointCol.DataBodyRange, 0)
   rowIndex2 = WorksheetFunction.Match(JointValue2, jointCol.DataBodyRange, 0)
   On Error GoTo 0
   

2. 类型约束：如果关节编号是数字类型，可将参数JointValue1和JointValue2定义为Long或Integer，避免类型转换问题。
3. 避免硬编码表头名：将表头名定义为常量，方便后续维护：

   Const JOINT_COL_NAME As String = "Joint"
   Const GLOBAL_X_COL_NAME As String = "GlobalX"
   ' ... 其他常量
   

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