vba Excel 单元格值作为字符串不会存储为字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16820553/
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
Excel cell value as string won't store as string
提问by BgreenDSI
I can't get this code here to grab cell content and store as string. I get a double:54.6666666667instead of N03:DM:(example cell contents).
我无法在此处获取此代码来获取单元格内容并将其存储为字符串。我得到一个 double:54.6666666667而不是N03:DM:（示例单元格内容）。
If I use Cstr(Sheet1.Cells(i, 5).Value)I still get same result.
如果我使用Cstr(Sheet1.Cells(i, 5).Value)我仍然得到相同的结果。
Any help would be appreciated.
任何帮助，将不胜感激。
Option Explicit
Private Sub GetAddress()
Dim varAdd As String
Dim i As Integer
For i = 2 To 327
If varTag = Sheet1.Cells(i, 2).Value Then
varAdd = Sheet1.Cells(i, 5).Value
varAdd = Left(varAdd, 7)
Sheet3.Cells(incR, 2).Value = varAdd
Exit For
End If
Next i
End Sub
Sheet screenshot 
工作表截图
回答by
Use Range("A1").Textinstead of .Value
使用Range("A1").Text代替.Value
post comment edit:
Why?
Because the .Textproperty of Range object returns what is literally visible in the spreadsheet, so if you cell displays for example i100l:25he*_92then <- Textwill return exactly what it in the cell including any formatting.
The .Valueand .Value2properties return what's stored in the cell under the hoodexcluding formatting. Specially .Value2for date types, it will return the decimal representation.
发表评论编辑：
为什么？
因为.TextRange 对象的属性返回电子表格中字面上可见的内容，所以如果您的单元格显示例如i100l:25he*_92<-Text将准确返回它在单元格中的内容，包括任何格式。
在.Value和.Value2属性返回什么是储存在电池盖下排除格式化。特别是.Value2对于日期类型，它将返回十进制表示。
If you want to dig deeper into the meaning and performance, I just found this articlewhich seems like a good guide
如果您想更深入地了解含义和性能，我刚刚发现this article这似乎是一个很好的指南
another edit
Here you go @Santosh
type in (MANUALLY) the values from the DEFAULT(col A) to other columns
Do not format column A at all
Format column B as Text
Format column C as Date[dd/mm/yyyy]
Format column D as Percentage
now,
paste this code in a module
另一个编辑
在这里你去@Santosh
输入（手动）从DEFAULT（col A）到其他列的值
根本不要格式化列A 将
B
列格式化为文本 将C列格式化为日期[dd/mm/yyyy]
格式化列
现在D 为百分比，
将此代码粘贴到模块中
Sub main()
Dim ws As Worksheet, i&, j&
Set ws = Sheets(1)
For i = 3 To 7
For j = 1 To 4
Debug.Print _
"row " & i & vbTab & vbTab & _
Cells(i, j).Text & vbTab & _
Cells(i, j).Value & vbTab & _
Cells(i, j).Value2
Next j
Next i
End Sub
and Analysethe output! Its really easy and there isn't much more i can do to help :)
和Analyse输出！这真的很容易，我无能为力：)
.TEXT .VALUE .VALUE2
row 3 hello hello hello
row 3 hello hello hello
row 3 hello hello hello
row 3 hello hello hello
row 4 1 1 1
row 4 1 1 1
row 4 01/01/1900 31/12/1899 1
row 4 1.00% 0.01 0.01
row 5 helo1$$ helo1$$ helo1$$
row 5 helo1$$ helo1$$ helo1$$
row 5 helo1$$ helo1$$ helo1$$
row 5 helo1$$ helo1$$ helo1$$
row 6 63 63 63
row 6 =7*9 =7*9 =7*9
row 6 03/03/1900 03/03/1900 63
row 6 6300.00% 63 63
row 7 29/05/2013 29/05/2013 41423
row 7 29/05/2013 29/05/2013 29/05/2013
row 7 29/05/2013 29/05/2013 41423
row 7 29/05/2013% 29/05/2013% 29/05/2013%
