Split Function in vba

[Bob@Sun]

Hello there,

I am using the Split function in my macro,but having one problem with that.

The split separater that I am using is "," I am using this function in a msgbox where I am entering numbers: 1,2,3,4 etc.. The problem is that if I put a comma after the last number I am getting an error. Can I add some lines that will prevent that.

Many thanks!

[mikerickson]

My Mac doesn't support Split, so I'm not sure on this point, is the error from the Split function or is it from your later trying to process vbNullString as a number?

In either case, you could test for and strip off the trailing "," before Splitting.

Please Login or Register  to view this content.

Also, after the Split, you should use either IsNumeric or Val to protect against entries like "a,1,2,frank".

[Bob@Sun]

Hi, this "If" statement works fine. I need to test for two more things before splitting. Can you please help me with that?

First, I need to make sure that no other delimiters besides the ","(comma) are used. If other delimiters are found I need the code to exit the sub Example: 1.2.3. or 1/2/3/ etc

Second, if more than one consecutive delimiter is found any where in the msgbox, I need the code to exit the sub also. Example: 1,,2,3,4 or 1,2,3,4,,

I need all that, because the sheet is password protected and I have lines before and after this split. If something happens between those two lines, the sheet gets unprotected.

Thanks!

[romperstomper]

How do you determine the delimiter? Is anything that's not a number a delimiter?
I suggest you simply do a split as Mike said, then validate each item. If any are invalid (non-numeric, blank etc) then pop up a message, reprotect and exit.
Also, if you need to ensure that the sheet is reprotected no matter what, you need to have an error handler.