This is a working code, which doesn't require the first and last points to be the same. The function is called IsInside() here.

Code:
Public Function IsInside(Xcoord As Double, Ycoord As Double, Polygon As Variant) As Variant
  Dim x As Long, NumSidesCrossed As Long, m As Double, b As Double, Poly As Variant
  Poly = Polygon
  If (UBound(Poly, 2) - LBound(Poly, 2)) <> 1 Then
    If TypeOf Application.Caller Is Range Then
      IsInside = "#WrongNumberOfCoordinates!"
    Else
      Err.Raise 999, , "Array Has Wrong Number Of Coordinates!"
    End If
    Exit Function
  End If
  For x = LBound(Poly) To UBound(Poly)
    If x < UBound(Poly) Then
      If Poly(x, 1) > Xcoord Xor Poly(x + 1, 1) > Xcoord Then
        m = (Poly(x + 1, 2) - Poly(x, 2)) / (Poly(x + 1, 1) - Poly(x, 1))
        b = (Poly(x, 2) * Poly(x + 1, 1) - Poly(x, 1) * Poly(x + 1, 2)) / (Poly(x + 1, 1) - Poly(x, 1))
        If m * Xcoord + b > Ycoord Then NumSidesCrossed = NumSidesCrossed + 1
      End If
    Else
      If Poly(UBound(Poly), 1) > Xcoord Xor Poly(LBound(Poly), 1) > Xcoord Then
        m = (Poly(LBound(Poly), 2) - Poly(UBound(Poly), 2)) / (Poly(LBound(Poly), 1) - Poly(UBound(Poly), 1))
        b = (Poly(UBound(Poly), 2) * Poly(LBound(Poly), 1) - Poly(UBound(Poly), 1) * Poly(LBound(Poly), 2)) / (Poly(LBound(Poly), 1) - Poly(UBound(Poly), 1))
        If m * Xcoord + b > Ycoord Then NumSidesCrossed = NumSidesCrossed + 1
      End If
    End If
  Next
  IsInside = CBool(NumSidesCrossed Mod 2)
End Function