40
78
Sub test5()
Range("A1").NumberFormat = "$#,##0.000"
Range("A1").Value = 1.23456789
Dim X As Double
'use this function whenever you need to return a cell's value
X = ReturnVal(Range("a1"))
MsgBox X
End Sub
Function ReturnVal(anyCell As Range)
anyCell.Copy
'a sheet named Temp must be in the workbook containing this function
'cell A1 in this workbook should not be formatted
WithThisWorkbook.Sheets("temp").Range("a1")
.PasteSpecialPaste:=xlPasteValues, _
Operation:= xlNone, _
SkipBlanks:=False, Transpose:=False
ReturnVal = .Value
End With
End Function
Microsoft is aware of the above problem and it is discussed in article:
http://support.microsoft.com/support/kb/articles/Q213/7/19.ASP
The article says that this is a
problem with currency or date formatted cells. Excel 2000 and above they have added a
Value2 property that avoids the above problem. Using theValue function in Excel 2000 and
above continues the problem Unfortunately, a macro that uses this property will not work in
Excel 97.
If you are going to work with currency or date formatted cells, then you need to use one of the
solutions above.
9.2.4 Writing Text To The Clipboard
One way to write text to the clipboard is to first write it to a cell and then copy the cell. Another
way is to use code like the following:
Dim ClipData As DataObject
Dim ClipString As String
'declare clipData as a data object
Set ClipData = New DataObject
'put something into the variable clipString
ClipString = "Some text"
'this copies the above string to the data object
ClipData.SetText ClipString, 1
