在 VBA 中创建数字前缀
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5791111/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create number prefix in VBA
提问by F.P
I have several revision numbers I need to create folders from. So, lets say {4, 12, 534}. From this, I need to create folders that can be sorted accordingly;
我有几个修订号需要从中创建文件夹。所以，让我们说{4, 12, 534}。由此，我需要创建可以相应排序的文件夹；
dim s As String
s = Format$(revNum, "000")
4\..
2\..
4\..
So, what I need to is to get the highest number and then see, how many filling zeros I need for every foldername so they are sorted correct after creating them.
所以，我需要的是获得最高数字，然后查看每个文件夹名称需要多少个填充零，以便在创建它们后正确排序。
How can I do this using VBA (Excel)? I tried converting them to strings and then dealing around with String operations, but this didn't quite work out.
如何使用 VBA (Excel) 执行此操作？我尝试将它们转换为字符串，然后处理字符串操作，但这并没有完全奏效。
I hope someone has a nice idea for this...
我希望有人对此有一个好主意......
回答by RolandTumble
The simplest way is with Format(or Format$):
最简单的方法是使用Format（或Format$）：
Dim nums() As String: nums = Split("4,12,534,9999", ",")
Dim i As Long
Dim max As Long
For i = 0 To UBound(nums)
If (Len(nums(i)) > max) Then max = Len(nums(i))
Next
For i = 0 To UBound(nums)
nums(i) = String(max - Len(nums(i)), "0") & nums(i)
Debug.Print nums(i)
Next
Using "000"as the "format string" tells the function to pad with leading zeros, whereas "###"tells it not to pad. The difference between the function versions is the return type: Formatreturns a Variant (String), while Format$returns an actual String.
使用"000"的“格式字符串”讲述了功能与领先的零垫，而"###"不是告诉它垫。函数版本之间的区别在于返回类型：Format返回一个 Variant (String)，而Format$返回一个实际的 String。
回答by Alex K.
Easiest to precompute the max then loop again and pad;
最容易预先计算最大值，然后再次循环并填充；
Select Case Len(folderstring)
Case 1
folderstring = "00" & folderstring
Case 2
folderstring = "0" & folderstring
Case Else
' Do nothing here
End Case
回答by Lance Roberts
I'm not sure why a simple string operation won't work out.
我不确定为什么简单的字符串操作不起作用。
Dim zeroarray(1 To 3) As Variant
zeroarray = Array("00","0","")
folderstring = zeroarray(Len(folderstring)) & folderstring
OR
或者
##代码##