VBA Excel MID Function Array-CodePudding

I'm trying to create a Function to calculate a UPC Check Digit. Part of what I need to do this, is to sum the characters in positions 1,3,5,7,9,11 and multiply by three. So in my code, I want to use the MID function, I assume, but I don't know how to get every other character using it, as the MID function only lets me select one start digit. Like I can write a line that says, "CD = Mid(UPC, 1, 1)", but I need the first "one" in that formula, to be (1,3,5,7,9,11). Do I have to write my Mid function six times for each number and concatenate? Or is there a way to get all those numbers at one time?

CodePudding user response：

You can use this formula:

=SUM(NUMBERVALUE(MID(A1,{1;3;5;7;9;11},1)))*3

Depending on your Excels language you might have to use a comma within the curly brackets.

Or do you need a pure VBA-solution?

CodePudding user response：

This will handle variable length UPC

UPC = "1234567891011"
CD = 0
For i = 1 To Len(code) Step 2
    CD = CD   Mid(UPC, i, 1)
Next

CD = CD * 3