英文:
create sequence number with a defined started number and length vba
问题
我想在VBA/Macro中定义一些特定的ID号码。当用户在消息框中键入特定的数字100并且数字的长度为11时，结果应该是100、101、...、110。非常感谢。
预期结果=SEQUENCE(11,1,1000000,1)
英文:
I would like to define some specific id numbers in VBA/Macro. When the user types in a message box a specific number 100 and the length of the number 11. The outcome should be 100,101,...,110. Really appreciate.
Expected Result=SEQUENCE(11,1,1000000,1)
答案1
得分: 1
以下是您提供的代码的中文翻译部分：
'idGenerator提示用户输入基数和计数，用[,./ -]之一分隔。如果输入无效，将重复提示用户，直到用户退出或输入有效数字。该函数返回一个带有ID的一维数组。正如您在示例中所看到的，我们可以将数组水平或垂直地写入工作表中。
Sub testGenerator()
Dim rslt As Variant
rslt = idGenerator()
If LBound(rslt) = 0 Then Exit Sub
With Application.WorksheetFunction
'如果要将数组复制到行中
Range("H2").Resize(UBound(rslt), 1) = .Transpose(rslt)
'如果要将数组复制到列中
Range("I2").Resize(1, UBound(rslt)) = rslt
End With
End Sub
Public Function idGenerator() As Variant()
Dim answ As Variant, c As Long, ch As String, p As Long, gtmp As String, ctmp As String
Dim idBase As Long, counter As Long, rslt() As Variant
Const separ = "-./, "
Const msg = "请键入ID基数和计数" & vbCrLf & "用空格或以下之一分隔：/，.，-"
'如果lbound(idGenerator)等于0 => 没有用户输入
ReDim idGenerator(0 To 0)
Linput:
answ = Trim(InputBox(msg, "ID生成器"))
If answ = "" Then Exit Function
For c = Len(separ) To 1 Step -1
ch = Mid$(separ, c, 1)
p = InStr(1, answ, ch)
If p > 0 Then
GoTo Lgen
End If
Next
Call MsgBox(msg, vbCritical)
GoTo Linput
Lgen:
gtmp = Trim(Left(answ, p - 1))
ctmp = Trim(Mid$(answ, p + 1))
If Not (IsNumeric(gtmp) And IsNumeric(ctmp)) Then
MsgBox ("请仅输入数字作为ID和计数" & vbCrLf & "用空格或以下之一分隔：/，.，-")
GoTo Linput
End If
idBase = CLng(gtmp)
counter = CLng(ctmp)
ReDim rslt(1 To counter)
For c = 1 To counter
rslt(c) = idBase + c - 1
Next
idGenerator = rslt
End Function
请注意，上述内容是提供的VBA代码的中文翻译，仅包括注释和字符串消息的翻译。
英文:
The idGenerator prompts the user to type the base and the counter separated by one of [,./ -]. If the input is not valid reprompt until the user escape or type valid numbers. The function returns an 1D array with the IDs. After as you can see in my example we can write the array in any position in a sheet horizontally or vertically
Sub testGenerator()
Dim rslt As Variant
rslt = idGenerator()
If LBound(rslt) = 0 Then Exit Sub
With Application.WorksheetFunction
'IF YOU WANT TO COPY THE ARRAY IN ROWS
Range("H2").Resize(UBound(rslt), 1) = .Transpose(rslt)
'IF YOU WANT TO COPY THE ARRAY IN COLUMNS
Range("I2").Resize(1, UBound(rslt)) = rslt
End With
End Sub
Public Function idGenerator() As Variant()
Dim answ As Variant, c As Long, ch As String, p As Long, gtmp As String, ctmp As String
Dim idBase As Long, counter As Long, rslt() As Variant
Const separ = "-./, "
Const msg = "Please type the ID base and the count" & vbCrLf & "separated by space or one of: / , . -"
'if lbound(idGenerator) eq 0 => no user input
ReDim idGenerator(0 To 0)
Linput:
answ = Trim(InputBox(msg, "ID GENERATOR"))
If answ = "" Then Exit Function
For c = Len(separ) To 1 Step -1
ch = Mid$(separ, c, 1)
p = InStr(1, answ, ch)
If p > 0 Then
GoTo Lgen
End If
Next
Call MsgBox(msg, vbCritical)
GoTo Linput
Lgen:
gtmp = Trim(Left(answ, p - 1))
ctmp = Trim(Mid$(answ, p + 1))
If Not (IsNumeric(gtmp) And IsNumeric(ctmp)) Then
MsgBox ("Please for Id and count type only mumbers" & vbCrLf & "separated by space or one of: / , . -")
GoTo Linput
End If
idBase = CLng(gtmp)
counter = CLng(ctmp)
ReDim rslt(1 To counter)
For c = 1 To counter
rslt(c) = idBase + c - 1
Next
idGenerator = rslt
End Function
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论