vba设置单元格格式为文本（vba单元格数值格式赋值给变量）

在Excel中进行操作时，很多时候都需要对单元格进行设置，以制作出美观大方的表格。在VBA中，也可使用各种代码对单元格的格式进行设置。

目录

1 用AutoFormat方法设置自动套用格式

2 用Borders集合设置边框线

3 用HorizontalAlignment、VerticalAlignment设置文本对齐格式

4 用InsertIndent方法设置单元格文本缩排

5 用Orientation属性设置文本方向

6 用WrapText属性设置自动换行格式

7 用ShrinkToFit属性设置缩小字体填充

8 用NumberFormatLocal属性设置日期格式

9 自定义函数生成大写金额

10 用Pattern属性设置单元格图案

11 用FormatConditions集合设置条件格式

1 用AutoFormat方法设置自动套用格式

Sub 自动套用格式()

Dim rng1 As Range

Set rng1 = Sheet1.Range("A1").CurrentRegion

rng1.AutoFormat

Set rng1 = Nothing

End Sub

2 用Borders集合设置边框线

Sub 设置边框线()

Dim rng1 As Range

Set rng1 = Sheet1.Range("A1").CurrentRegion

rng1.Borders.LineStyle = xlDouble

Set rng1 = Nothing

End Sub

3 用HorizontalAlignment、VerticalAlignment设置文本对齐格式

Selection.HorizontalAlignment = xlHAlignCenter

Selection.HorizontalAlignment = xlVAlignCenter

4 用InsertIndent方法设置单元格文本缩排

Sub 增加缩排值()

On Error Resume Next

Selection.InsertIndent 1

End Sub

Sub 减少缩排值()

On Error Resume Next

Dim rng1 As Range

Set rng1 = Selection

If rng1.IndentLevel > 0 Then

rng1.InsertIndent -1

End If

Set rng1 = Nothing

End Sub

5 用Orientation属性设置文本方向

Sub 设置文本方向()

Dim i As Integer

i = Application.InputBox(prompt:="输入文字的角度（-90～90）：", Type:=1)

If i >= -90 And i <= 90 Then

Selection.Orientation = i

End If

End Sub

6 用WrapText属性设置自动换行格式

Sub 自动换行()

Selection.WrapText = True

End Sub

7 用ShrinkToFit属性设置缩小字体填充

Sub 缩小字体填充()

Selection.ShrinkToFit = True

End Sub

8 用NumberFormatLocal属性设置日期格式

Sub 设置日期格式()

Dim rng As Range, rng1 As Range

Set rng1 = ActiveSheet.UsedRange

For Each rng In rng1

If IsDate(rng.Value) Then

rng.NumberFormatLocal = "yyyy""年""m""月""d""日"";@"

End If

Next

End Sub

9 自定义函数生成大写金额

Sub 大写金额()

Dim t As Currency, str1 As String

Dim i As Integer, strJ As String, strF As String

Dim rng1 As Range

With ActiveSheet

Set rng1 = Range("IV1").End(xlToRight) '获取最右侧列

t = ActiveCell.Value

With rng1

.Value = t

.NumberFormatLocal = "[DBNum2][$-804]G/通用格式"

.Columns.AutoFit

str1 = .Text

.Clear

End With

i = InStr(str1, ".")

If i > 0 Then

strJ = Mid(str1, i + 1, 1) '获取角部分字符

strF = Mid(str1, i + 2, 1) '获取分部分字符

If strF = "" Then

str1 = Left(str1, i - 1) & "元" & strJ & "角整"

Else

str1 = Left(str1, i - 1) & "元" & strJ & "角" & strF & "分"

End If

Else

str1 = str1 & "元整"

End If

ActiveCell = "人民币" & str1

End With

End Sub

10 用Pattern属性设置单元格图案

Sub 设置单元格图案()

Dim i As Integer

Dim r As Integer, g As Integer, b As Integer

Randomize

On Error Resume Next

For i = 1 To 18

With Selection.Interior

.Pattern = i

r = Int(Rnd * 255)

g = Int(Rnd * 255)

b = Int(Rnd * 255)

.PatternColor = RGB(r, g, b)

End With

MsgBox "下个图案样式"

Next i

End Sub

11 用FormatConditions集合设置条件格式

Sub 设置条件格式()

Dim rng1 As Range

Set rng1 = Sheet1.Range("F3:F13")

'添加条件格式，设置单元格值大于等于2000的格式

With rng1.FormatConditions.Add(Type:=xlCellValue, _

Operator:=xlGreaterEqual, Formula1:=2000)

With .Borders

.LineStyle = xlContinuous

.Weight = xlThin

.ColorIndex = 6

End With

With .Font

.Bold = True

.ColorIndex = 3

End With

End With

'添加条件格式，设置单元格值小于1000的格式

With rng1.FormatConditions.Add(Type:=xlCellValue, _

Operator:=xlLess, Formula1:=1000)

With .Font

.Bold = True

.ColorIndex = 10

End With

End With

End Sub

Sub 清除条件格式()

Cells.FormatConditions.Delete

End Sub

－End－