VBA Excel数组错误 [英] VBA Excel array error

问题描述

我必须使用宏生成一个ID，当需要8个数值时，我遇到一些问题。
ID必须包含第1到第8个字符的数值，第9个字母必须是字母。从第十个字符开始，它必须是空格。
这些是我的代码，我确定公式没有问题。

I have to generate an ID using a macro and I'm facing some problems when there are 8 numeric values required. The ID has to include numeric values for the 1st-8th charactera and the 9th must be alphabetic. From the 10th character onwards it has to be spaces. These are my codes and I'm certain that there are no issues with the formula

Function GenerateRB()

strLastCharSelections = "X,W,M,L,K,J,E,D,C,B,A"

intNumber = GenerateRandomNumber(1000000, 9999999)

ReDim a(8)
For i = 1 To 8
a(i) = Mid(intNumber, i, 1)
Next

intTotal = a(1) * 9 + a(2) * 8 + a(3) * 7 + a(4) * 6 + a(5) * 5 + a(6) * 4 + a(7) * 3 + a(8) * 2


intRemainder = intTotal Mod 11

arrstrSplitLastCharSelections = Split(strLastCharSelections, ",")
strLastChar = arrstrSplitLastCharSelections(intRemainder)
GenerateRB = intNumber & strLastChar
End Function

代码在

    ReDim a(7)
For i = 1 To 7
a(i) = Mid(intNumber, i, 1)
Next

intTotal = a(1) * 9 + a(2) * 8 + a(3) * 7 + a(4) * 6 + a(5) * 5 + a(6) * 4 + a(7) * 3

任何帮助将不胜感激，因为我很新这个，谢谢！

Any help will be appreciated as I'm very new to this, Thank you!

推荐答案

我假设 GenerateRandomnNumber will返回指定范围内的数字 - 在这种情况下，为1000000到9999999之间的七位数字。

I'm assuming that GenerateRandomnNumber will return a numeric in the specified range - in this case, a seven digit number between 1000000 and 9999999.

所以当选择 i a（i）= Mid（intNumber，i，1）中的c $ c> th数字，当 i 是1到7 - 但是，当它是8 - 没有第八位数字，所以代码将失败。

So when selecting the ith digit in the statement a(i) = Mid(intNumber, i, 1), there are no issues when i is 1 to 7 - however, when it's 8 - there is no eighth digit, so the code will fail.

这篇关于VBA Excel数组错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！