vba 在文本单元格中使用字符串创建超链接会修剪 0
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20843045/
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
Creating a hyperlink with a String in Text cell trims 0s
提问by EplusL
I created a sub to create hyperlinks in my Excel 2010 workbook, and it works for all my purposes except one.
我创建了一个子来在我的 Excel 2010 工作簿中创建超链接，它适用于我的所有目的，除了一个。
I have a use case where the sub is supposed to create a link with a task number for the text value, with the format "00.00". Passing a string parameter as the hyperlink's text value trims trailing 0s, i.e. If I pass "12.10" as my task number, the variable watcher shows a consistent "12.10" value in the debugger, but the cell shows "12.1", even if I preformat the whole column to be a "Text" cell.
我有一个用例，其中 sub 应该为文本值创建一个带有任务编号的链接，格式为“00.00”。传递字符串参数作为超链接的文本值修剪尾随 0，即如果我传递“12.10”作为我的任务编号，变量观察器在调试器中显示一致的“12.10”值，但单元格显示“12.1”，即使我将整列预格式化为“文本”单元格。
Before you suggest treating the task number as a number: it looks like a number but it isn't. It's a concatenation of two things and needs to be a string in all instances of its use.
在您建议将任务编号视为数字之前：它看起来像一个数字，但实际上不是。它是两件事情的串联，在它的所有使用实例中都需要是一个字符串。
Here is the sub:
这是子：
sSheet: Sheet where the link will be addedsColumn: Column of the hyperlink cellsRow: Row of the hyperlink cellsTargetSheet: link destination sheet namesAddress: link destination cell addresssText: link text to display (this is my problematic parameter)
sSheet：将添加链接的工作表sColumn: 超链接单元格的列sRow: 超链接单元格的行sTargetSheet：链接目标工作表名称sAddress: 链接目标单元格地址sText：要显示的链接文本（这是我的有问题的参数）
Sub CreateLink(sSheet As String, sColumn As String, sRow As String, sTargetSheet As String, _
sAddress As String, sText As String)
Dim sSubAddress As String
If sTargetSheet = "" Then
sSubAddress = ""
ElseIf sAddress <> "" Then
sSubAddress = "'" & sTargetSheet & "'!" & sAddress
End If
With Sheets(sSheet)
.Hyperlinks.Add Anchor:=.Range(sColumn & sRow), _
Address:="", _
SubAddress:=sSubAddress, _
TextToDisplay:=sText
End With
End Sub
This creates any link I want, whether they point to a cell or activate a macro through the Worksheet_FollowHyperlink call.
这将创建我想要的任何链接，无论它们指向单元格还是通过 Worksheet_FollowHyperlink 调用激活宏。
In the case that causes a problem, I want to set a cell with a hyperlink with text format "99.99".
在导致问题的情况下，我想设置一个带有文本格式“99.99”的超链接的单元格。
I have hard set the whole column where those links are created to be Text, and call the Sub by passing the value parameter as String, like this (the original variable is also a string but Callforces me to cast as String anyway).
我已经将创建这些链接的整个列硬设置为文本，并通过将 value 参数作为字符串传递来调用 Sub，就像这样（原始变量也是一个字符串，但Call无论如何我都强迫我强制转换为字符串）。
Call CreateLink("Tasks", "A", CStr(nextRow), "", "", CStr(nextTaskNumber))
I don't know how to force the .Hyperlinks.Add sub to pass the value as is. I watch the variable and until the very end nextTaskNumberhas all its trailing 0s, but the cell just loses it.
我不知道如何强制 .Hyperlinks.Add 子按原样传递值。我观察变量，直到最后nextTaskNumber都有它的所有尾随 0，但单元格只是丢失了它。
How can I make sure the cell does not trim the 0?
如何确保单元格不会修剪 0？
回答by Dmitry Pavliv
try to to change cell format to text format:
尝试将单元格格式更改为文本格式：
With Sheets(sSheet)
.Range(sColumn & sRow).NumberFormat = "@"
.Hyperlinks.Add Anchor:=.Range(sColumn & sRow), _
Address:="", _
SubAddress:=sSubAddress, _
TextToDisplay:=sText
End With
