CODEWITHSUNDEEP
excelvbafunction
Dumitru Daniel
Dumitru Daniel

Reputation: 543

VBA Faulty function InstrRev = Instr

I've been trying to find the extension from a file structure, and for that I tough about using InstrRev(filename, ".") but instead of returning 4, it's returning 30, the same way as Instr does ...

Anyone know of a weird error that would cause excel to give out the same result to 2 opposite functions?

Kind regards, Daniel

Upvotes: 8

Views: 5821

Answers (2)

Elias Daoud
Elias Daoud

Reputation: 29

The function InStrRev finds the sting2 inside the string1 from the end is true, but it's return for the found position is from the beginning and not from the end.

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33682

The difference between Instr and InstrRev is the direction it is looking for a certain Sub-String. Instr is looking from the start to the end, while InstrRev is looking from the end of the string to the beginning. The result character position is always counted from the beginning of the string (from left to right).

I guess in your example, you have only one "." in your path, that's why the result is the same.

Let's consider a case you are looking for the "Clean" file name, so you are looking for the last \, then you will find the InStrRev function very useful (see code below for example).

For your case: if you want to find the extension name, let's say result is 31, then use the Mid function : 

ExtensionStr = Mid(FullName, dotRes + 1, Len(FullName) - dotRes) ' < --- returns "xlsm"

(dotRes = 31, ExtensionStr is a string representing the clean extension name)

Code Example

Sub Instr_InstrRev()

Dim instrRes As Variant
Dim instrRevRes As Variant
Dim dotRes As Variant
Dim ExtensionStr As String
Dim FullName    As String

FullName = "C:\Users\Radoshits\Desktop\SO2.xlsm"
instrRes = InStr(FullName, "\") ' <-- returns 3
instrRevRes = InStrRev(FullName, "\") ' <-- returns 27

' to answer your post how to find the "clean" extension name
dotRes = InStr(FullName, ".") ' <-- returns 31
ExtensionStr = Mid(FullName, dotRes + 1, Len(FullName) - dotRes) ' < --- returns "xlsm"

End Sub

Upvotes: 13

Related Questions