vba 任何等效于 PadLeft/PadRight 的方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12060347/
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
Any method equivalent to PadLeft/PadRight?
提问by Taylor K.
Just wondering, is there any equivalent in VBA to VB .NET's PadLeft and PadRight methods?
只是想知道，VBA 中是否有与 VB .NET 的 PadLeft 和 PadRight 方法等效的方法？
As of right now, whenever I want to take a string and make it a fixed length with leading spaces, I do a For...Next loop based on the string's length.
截至目前，每当我想要获取一个字符串并使其具有前导空格的固定长度时，我都会根据字符串的长度执行 For...Next 循环。
For example, I would use the following code to format a string to 8 characters with leading spaces:
例如，我将使用以下代码将字符串格式化为带有前导空格的 8 个字符：
intOrdNoLen = Len(strOrdNo)
For i = 1 To (8 - intOrdNoLen) Step 1
strOrdNo = " " & strOrdNo
Next
Is there a way to do this same thing in fewer lines in VBA?
有没有办法在 VBA 中用更少的行来做同样的事情？
回答by LittleBobbyTables - Au Revtheitroad
I don't believe there are any explicit PADLEFTor PADRIGHTfunctions, but you can use a combination of SPACEand LEFTor RIGHTto prepend spaces to your string, and then grab the right X number of characters.
我不相信有任何显式PADLEFT或PADRIGHT函数，但是您可以使用SPACE和LEFT或的组合RIGHT在字符串前添加空格，然后获取正确的 X 个字符。
PADLEFT
桨左
strOrdNo = RIGHT(Space(8) & strOrdNo, 8)
If you want a character instead of spaces, you can use STRINGinstead of space (the example below left-pads with X):
如果你想要一个字符而不是空格，你可以使用STRING代替空格（下面的例子用 X 左填充）：
strOrdNo = RIGHT(String(8, "X") & strOrdNo, 8)
PADRIGHT
手电筒
strOrdNo = LEFT(strOrdNo & Space(8), 8)
strOrdNo = LEFT(strOrdNo & String(8, "X"), 8)
回答by Brad
You could use these. Put them in a public module
你可以用这些。将它们放在公共模块中
'NB Fails if input string is longer than the total length
'NB 如果输入字符串比总长度长则失败
Function PadLeft(text As Variant, totalLength As Integer, padCharacter As String) As String
PadLeft = String(totalLength - Len(CStr(text)), padCharacter) & CStr(text)
End Function
Function PadRight(text As Variant, totalLength As Integer, padCharacter As String) As String
PadRight = CStr(text) & String(totalLength - Len(CStr(text)), padCharacter)
End Function
回答by Sulaiman
Since we generally pad on the left side, the Format() function is shorter, simpler:
由于我们通常在左侧填充，因此 Format() 函数更短、更简单：
Format(number, " ")
Format(number, "00")
回答by Ernie Thomason
Format("abc","!@@@@@@") ' width >= 6; pad right side with spaces
Format("abc","@@@@@@") ' width >= 6; pad left side with spaces
回答by Sam
You can also use fixed length strings in VBA:
您还可以在 VBA 中使用固定长度的字符串：
Dim myString As String * 10
myString = "test"
Debug.Print myString, "(" & Len(myString) & ")" '// Prints "test (10)"
although this is only useful for padding on the right.
虽然这仅对右侧的填充有用。
回答by cco
I solved the problem by reassigning variable.
In my code I get data from workbook cell and limit it to 5 char (if necessary fill with enough 0..):
我通过重新分配变量解决了这个问题。
在我的代码中，我从工作簿单元格获取数据并将其限制为 5 个字符（如有必要，填充足够的 0..）：
MB = Right(String(5, "0") & Worksheets("HOME").Range("b3"), 5)
MB = Right(MB, 5)
