My 2 cents,
the first function seems fine, you could make it a little DRYer by just setting the compareMethod in your if statement and then have only 1 complicated line of logic. And if you are doing that, you might as well put the Cstr's there.
Public Function StringContains(haystack, needle, Optional ByVal caseSensitive As Boolean = False) As Boolean
Dim compareMethod As Integer
If caseSensitive Then
compareMethod = vbBinaryCompare
Else
compareMethod = vbTextCompare
End If
'Have you thought about Null?
StringContains = (InStr(1, CStr(haystack), CStr(needle), compareMethod) <> 0)
End Function
Notice as well that I love the idea of searching for needles in haystacks, I stole that from PHP.
For StringContainsAny, you are not using the code you wrote for StringContains, you repeat it. If you were to re-use the first function, you could do this:
Public Function StringContainsAny(haystack, ByVal caseSensitive As Boolean, ParamArray needles()) As Boolean
Dim i As Integer
For i = LBound(needles) To UBound(needles)
If StringContains(CStr(haystack), CStr(needles(i)), caseSensitive) Then
StringContainsAny = True
Exit Function
End If
Next
StringContainsAny = False 'Not really necessary, default is False..
End Function