CellSave (SimpleSave)

Saves value to cell without deformation and converting
Used to be called SaveCell_StupidExcel
Opposite of CellRead (or SimpleLookup)

CodeFunctionName
What is this?

Public

Tested

Original Work
Function CellSave(CellValue, CellAddress, Optional Wb = "This", Optional Shee = "Active")
CellSave = SimpleSave(CellValue, CellAddress, Wb, Shee)
End Function
Function SimpleSave(CellValue, CellAddress, Optional Wb = "This", Optional Shee = "Active")
' Saves a value to cell without letting Excel converting them into dates or numerics
' Returns 1 if change made, 0 if not
' Excel converts 4-21 into April 21st date, and 1E-1 into 0.1
' This function will force saving it as string by adding '
' And then it changes the value passed to have the ' before it, in case we need to use it later
' Change only happen if Excel does that automatic change, if Excel did not change that, it will be saved as usual
If Wb = "This" Then Wb = ThisWorkbook.Name
If Shee = "Active" Then Shee = Workbooks(Wb).ActiveSheet.Name
SimpleSave = 0
Workbooks(Wb).Worksheets(Shee).Range(CellAddress).Value = CellValue
R42 = Workbooks(Wb).Worksheets(Shee).Range(CellAddress).Value
If IsNumeric(R42) And CellValue > "" Then
If Left(CellValue, 1) < > "'" Then
If CStr(CDbl(R42)) < > CStr(CellValue) Then
CellValue = "'" & CellValue
Workbooks(Wb).Worksheets(Shee).Range(CellAddress).Value = CellValue
SimpleSave = 1
End If
End If
ElseIf IsDate(R42) And CStr(CellValue) > "" Then
If Left(CellValue, 1) < > "'" Then
If CStr(CDate(R42)) < > CStr(CellValue) Then
CellValue = "'" & CellValue
Workbooks(Wb).Worksheets(Shee).Range(CellAddress).Value = CellValue
SimpleSave = 1
End If
End If
End If
End Function

CellValue, CellAddress, Optional Wb, Optional Shee

Views 3,139

Downloads 1,286

CodeID
DB ID