How I can find string in excel with vba?

Passing a string argument into a numerical function in EXCEL VBA?

• I'm new to Excel VBA, I'm writing a function in excel vba that will take the number from a text cell and return a number. here is my code: Function coordist(Lat As String) As Long Dim DLat As Long Dim DLong As Long Dim DDeg As Long, DMin As Long, DSec As Long DDeg = WorksheetFunction.Value(Left(Lat, 2)) DMin = WorksheetFunction.Value(Mid(Lat, 4, 2)) DSec = WorksheetFunction.Value(Mid(Lat, 7, 4)) DLat = DDeg + (DMin / 60) + (DSec / 3600) 'DLong = Value(Left(Lon, 2)) + Value(Mid(Lon, 4, 2)) / 60 + Value(Mid(Lon, 7, 4)) / 3600 coordist = DMin End Function When I run this code I get an error that says " a value used in the formula is of a wrong data type" Can any one help with this?

• Answer:

  change: DDeg = WorksheetFunction.Value(Left(Lat, 2)) DMin = WorksheetFunction.Value(Mid(Lat, 4, 2)) DSec = WorksheetFunction.Value(Mid(Lat, 7, 4)) to DDeg = Val(Left(Lat, 2)) DMin = Val(Mid(Lat, 4, 2)) DSec = Val(Mid(Lat, 7, 4))