Help me converting this code from Java to VBA

Question

Hi,

I need a to create a VBA function that extract a text from a string I wrote this java method

public static String extract(String str) {

    String s = str.substring(str.indexOf("'"),str.lastIndexOf("'"));
    String fg = s.substring(s.indexOf("'")+1, s.indexOf("to")-2);
    String sg = s.substring(s.indexOf("to")+4);

    return sg;

}

which do what I want but in VBA I can't find equivalent to indexOf and lastIndexOf And most importantly how to mimic java substring using indexes of characters without using length to extract as in VBA. could anyone help me.

Thanks in advance.

Answer 1

The equivalent methods to indexOf and lastIndexOf are inStr and inStrRev.

See http://www.techonthenet.com/excel/formulas/index_vba.php for example.

Answer 2

idx = str.indexOf("'");

// is equivalent of

idx = InStr(str, "'")

and

idx = str.lastIndexOf("'");

// is equivalent of

idx = InStrRev(str, "'")

and

str = str.substring(start, end)

// is equivalent of

str = Mid(str, start, end - start)

Answer 3

Equivalent for "substring" is "mid" function.