retrieving data from table but getting "compile error: expected ="

Question

i'm trying to write a function that calculates a distance between two points that have x,y,z coordinates. I have a pre-defined table called "JC" that is located in sheet called "Joint Coordinates". the table has many headers, but the ones i'm interested in are titled [Joints], [GlobalX], [GlobalY], [GlobalZ].

I want my function to look up two joint numbers in [Joints] column of table "JC" and store the corresponding coordinates as variables, then the function has a math section to make the distance calc between two points.

below is the code I have, but I get "compile error: Expected: =" and i've spent a couple of days trying to debug it without success

all suggestions are welcome. thank you!

    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

Another function I wrote to just SUM x,y,z coordinates works good without errors. this is the code I used

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

Answer 1

Here's a suggestion for a more-compact approach:

Function CalcDist(JointValue1 As Variant, JointValue2 As Variant) As Variant
    Dim tbl As ListObject, m1, m2, rngX As Range, rngY As Range, rngZ As Range
    
    Set tbl = ThisWorkbook.Worksheets("Joint Coordinates").ListObjects("JC")
    With tbl.ListColumns("Joint") 'locate the search values in "Joint"
        'Match is generally faster than Find
        m1 = Application.Match(JointValue1, .DataBodyRange, 0)
        m2 = Application.Match(JointValue2, .DataBodyRange, 0)
    End With
    
    If Not IsError(m1) And Not IsError(m2) Then 'matched both values?
        'reference X,Y,Z ranges...
        Set rngX = tbl.ListColumns("GlobalX").DataBodyRange
        Set rngY = tbl.ListColumns("GlobalY").DataBodyRange
        Set rngZ = tbl.ListColumns("GlobalZ").DataBodyRange
        'run the calculation
        CalcDist = Sqr((rngX(m2).Value - rngX(m1).Value) ^ 2 + _
                       (rngY(m2).Value - rngY(m1).Value) ^ 2 + _
                       (rngZ(m2).Value - rngZ(m1).Value) ^ 2)
    End If
End Function