VBA code to increment numeric portion of a string

[rsf]

Hi, I'd really appreciate some feedback on the attached code.
The code is intended to take an alphanumeric string and increment the numerical part by a specified amount e.g. the string CV01-00123 would be incremented to CV01-00124 (it's going to be used in a data input form for entering sequential sample IDs).
I'd really appreciate some wiser heads looking it over and giving any feedback as to whether there are any pitfalls with what I've done or if there would be better way to do it. Thanks in advance.

 

[arnelgp]

use a table to hold your id numbers, with fields like IDPrefix (string), IDCounter (Long). query this table for the last sequence number, then add a record with an increment sequence number. this record will be your form new id number.

 

[GinaWhipp]

Just not sure why you made it so complicated when a few lines will do. Have a look at...
http://regina-whipp.com/blog/?p=704

 

[rsf]

great - thanks for the pointers.
In my case the prefix cannot be predefined (Gina - in your link it looks to me like the prefix needs to be predefined in the code?).
My code needs to be able to cope with any alphanumeric string that's input and increment the right-most numerical portion if possible.
Hence the idea of the code isolating the rightmost numerical portion of the input string by reading it from right to left until it finds a non-numerical character.
But I did wonder if this was the best way to go about it, so if there's a simpler way, then great! Thanks again.

 

[gemma-the-husky]

if the separator is always a hyphen then you could use split to divide the parts of the string and then select and process the second part. It all depends how reliable the data format is.

 

[rsf]

Unfortunately the separator won't always be hyphen - the input string could be absolutely anything... I'd never heard of the 'split' function though, so that's helpful in itself! Thanks.

 

[gemma-the-husky]

if the data isn't reliable it's much harder.

all you could do is test the length of substrings from the right until you hit a non-numeric char.

much better to enforce the format of the entered string, I think. There are limits to what you can permit users to do. That's why spreadsheets are so difficult. There is no control over the data that gets entered within a column.

 

[GinaWhipp]

My code does not have to be predefined. It was offered as demo of who to do *more for less*. You can make the left or right (or both) dynamic. You can also define the separator based on entity type though that might mean a table needs to be incorporated.