Reads or set Numberformatting for a cell or range.
Can pass sheet and workbook name.
If "ChangeTo" argument is passed, it will change the format of number to be displayed for that cell, otherwise, it will return the format.
"General" or 1
"Number", "#" or 2
"Text", "@" or 3
Function ANmaCellFormat(CellAddress, Optional ChangeTo = "DoNotChange", Optional Shee = "Active", Optional WB = "This")
' Returns format of cell, or change it to new format
' Returns
' Number Format = "#" > Number | "@" > Text | "General" > General
' = 0 failed to read
' = 1 General, change it to...
' = 2 Number, not Currency, change it to...
' = 3 Text
' ChangeTo
' = "DoNotChange", do not change keep it
' = "General" or 1 > > Make it General
' = "Number", "#" or 2 > > Make it Number format
' = "Text", "@" or 3 > > Make it Text
'
Rett = 0
If WB = "This" Then WB = Thisworkbook.name
If Shee = "Active" Then Shee = Workbooks(WB).ActiveSheet.name
Foo = Workbooks(WB).Worksheets(Shee).Range(CellAddress).Numberformat
Rett = Foo
If Foo = "General" Then Rett = 1
If Foo = "#" Then Rett = 2 ' Number
If Foo = "@" Then Rett = 3 ' "Text"
If ChangeTo = "DoNotChange" Then
' Do not Change, keep it
Else
If Val(ChangeTo) = 1 Or ChangeTo = "General" Then
Foo2 = "General"
ElseIf ChangeTo = "#" Or ChangeTo = "Number" Or Val(ChangeTo) = 2 Then
Foo2 = "#"
ElseIf ChangeTo = "@" Or ChangeTo = "Text" Or Val(ChangeTo) = 3 Then
Foo2 = "@"
Else
Foo2 = ChangeTo
End If
Workbooks(WB).Worksheets(Shee).Range(CellAddress).NumberFormat = Foo2
Rett = ChangeTo
End If
ANmaCellFormat = Rett
End Function
' Returns format of cell, or change it to new format
' Returns
' Number Format = "#" > Number | "@" > Text | "General" > General
' = 0 failed to read
' = 1 General, change it to...
' = 2 Number, not Currency, change it to...
' = 3 Text
' ChangeTo
' = "DoNotChange", do not change keep it
' = "General" or 1 > > Make it General
' = "Number", "#" or 2 > > Make it Number format
' = "Text", "@" or 3 > > Make it Text
'
Rett = 0
If WB = "This" Then WB = Thisworkbook.name
If Shee = "Active" Then Shee = Workbooks(WB).ActiveSheet.name
Foo = Workbooks(WB).Worksheets(Shee).Range(CellAddress).Numberformat
Rett = Foo
If Foo = "General" Then Rett = 1
If Foo = "#" Then Rett = 2 ' Number
If Foo = "@" Then Rett = 3 ' "Text"
If ChangeTo = "DoNotChange" Then
' Do not Change, keep it
Else
If Val(ChangeTo) = 1 Or ChangeTo = "General" Then
Foo2 = "General"
ElseIf ChangeTo = "#" Or ChangeTo = "Number" Or Val(ChangeTo) = 2 Then
Foo2 = "#"
ElseIf ChangeTo = "@" Or ChangeTo = "Text" Or Val(ChangeTo) = 3 Then
Foo2 = "@"
Else
Foo2 = ChangeTo
End If
Workbooks(WB).Worksheets(Shee).Range(CellAddress).NumberFormat = Foo2
Rett = ChangeTo
End If
ANmaCellFormat = Rett
End Function
CellAddress, Optional ChangeTo = "DoNotChange", Optional Shee = "Active", Optional WB = "This"
Views 630
Downloads 50
CodeID
DB ID