最新Excel VBA编程实例.docx
- 文档编号:2159438
- 上传时间:2022-10-27
- 格式:DOCX
- 页数:24
- 大小:17.52KB
最新Excel VBA编程实例.docx
《最新Excel VBA编程实例.docx》由会员分享,可在线阅读,更多相关《最新Excel VBA编程实例.docx(24页珍藏版)》请在冰豆网上搜索。
最新ExcelVBA编程实例
Subdirect_Price()
''定义变量
DimcRowsAsInteger'总行数
DimcColumnsAsInteger'总列数
DimHEADERCOLORINDEXAsInteger'表头的背景色
DimcTempAsInteger'临时计数
DimsTempStringAsString'临时字符串变量
DimiAsInteger'临时计数
DimjAsInteger'临时计数
DimrowIndexAsInteger'临时指示处理到哪里
DimcolIndexAsInteger'临时指示处理到哪里
DimtempRndColorAsInteger'临时生成的颜色
DimTABLENAMEAsString'待处理的表名
DimcolorIndexAsString'颜色索引名字
'表头的背景色
HEADERCOLORINDEX=15
colorIndex=36'颜色从33开始是比较浅的颜色
TABLENAME="direct_Price"
'关闭所有弹出的警告消息
Application.DisplayAlerts=False
'设置需要处理的单元表
Sheets(TABLENAME).Select
'取单元表的总列数与总行数
cRows=Sheets(TABLENAME).UsedRange.Rows.Count
cColumns=Sheets(TABLENAME).UsedRange.Columns.Count
''''
'选择所有的单元格
Range(Cells(1,1),Cells(cRows,cColumns)).Select
'设置该表中所有单元行高为11.25
Selection.RowHeight=11.25
'设置该表中所有单元行高为11.25
Selection.RowHeight=11.25
'设置所有的边框
Selection.Borders(xlDiagonalDown).LineStyle=xlNone
Selection.Borders(xlDiagonalUp).LineStyle=xlNone
WithSelection.Borders(xlEdgeLeft)
.LineStyle=xlContinuous
.Weight=xlThin
.colorIndex=xlAutomatic
EndWith
WithSelection.Borders(xlEdgeTop)
.LineStyle=xlContinuous
.Weight=xlThin
.colorIndex=xlAutomatic
EndWith
WithSelection.Borders(xlEdgeBottom)
.LineStyle=xlContinuous
.Weight=xlThin
.colorIndex=xlAutomatic
EndWith
WithSelection.Borders(xlEdgeRight)
.LineStyle=xlContinuous
.Weight=xlThin
.colorIndex=xlAutomatic
EndWith
WithSelection.Borders(xlInsideVertical)
.LineStyle=xlContinuous
.Weight=xlThin
.colorIndex=xlAutomatic
EndWith
'并且拆分所有的单元格
WithSelection
.MergeCells=False'拆分单格
EndWith
Columns("C:
C").Select
Selection.InsertShift:
=xlToRight
'删除第一列,注意这里必须先拆分单格,再删除第一列,否则一次就会把合并单元格所在列全部删除
Range(Cells(1,1),Cells(1,1)).Select
Selection.EntireColumn.Delete
'Selection.EntireColumn.Delete
'向表头添加一行
Rows("1:
1").Select
Selection.Insert
Columns("A:
A").Select
Selection.ColumnWidth=9.29
Columns("B:
B").Select
Selection.ColumnWidth=6.71
Columns("C:
C").Select
Selection.ColumnWidth=15.29
Columns("D:
D").Select
Selection.ColumnWidth=29.86
Columns("E:
E").Select
Selection.ColumnWidth=12.29
Columns("F:
F").Select
Selection.ColumnWidth=12.29
'''''设定单元格A1:
A2''
'合并A1:
A2单元格
Range("A1:
A2").Select
'将数据写回
WithSelection
.HorizontalAlignment=xlCenter
.VerticalAlignment=xlCenter
.Orientation=0
.AddIndent=False
.IndentLevel=0
.ShrinkToFit=False
.ReadingOrder=xlContext
.MergeCells=True
EndWith
'往该单元格中写入Usage_Var
ActiveCell.FormulaR1C1="Price"
'设置该单元格字体格式
WithActiveCell.Characters(Start:
=1,Length:
=5).Font
.Name="Arial"
.FontStyle="加粗倾斜"
.Size=10
.Strikethrough=False
.Superscript=False
.Subscript=False
.OutlineFont=False
.Shadow=False
.Underline=xlUnderlineStyleNone
.colorIndex=2
EndWith
'单元格设定边框
Selection.Borders(xlDiagonalDown).LineStyle=xlNone
Selection.Borders(xlDiagonalUp).LineStyle=xlNone
Selection.Borders(xlEdgeTop).LineStyle=xlNone
WithSelection.Borders(xlEdgeBottom)
.LineStyle=xlContinuous
.Weight=xlThin
.colorIndex=56
EndWith
Selection.Borders(xlInsideHorizontal).LineStyle=xlNone
WithSelection.Interior
.colorIndex=5
.Pattern=xlSolid
.PatternColorIndex=xlAutomatic
EndWith
'''''设定头两行的内部样式'''''
Range("B1:
B2").Select
Selection.Merge
Range("C1:
C2").Select
Selection.Merge
Range("D1:
D2").Select
Selection.Merge
Range("B1:
D2").Select
'设置头两行行高为11.25
Selection.RowHeight=14.25
WithSelection.Font
.Name="Arial"
.FontStyle="加粗"
.Size=8
.Strikethrough=False
.Superscript=False
.Subscript=False
.OutlineFont=False
.Shadow=False
.Underline=xlUnderlineStyleNone
.colorIndex=xlAutomatic
EndWith
WithSelection
.HorizontalAlignment=xlCenter
.VerticalAlignment=xlCenter
.WrapText=True
.Orientation=0
.AddIndent=False
.IndentLevel=0
.ShrinkToFit=False
.ReadingOrder=xlContext
EndWith
WithSelection.Interior
.colorIndex=HEADERCOLORINDEX
.Pattern=xlSolid
.PatternColorIndex=xlAutomatic
EndWith
Range("B1:
B2").Select
ActiveCell.FormulaR1C1="Type"
WithActiveCell.Characters(Start:
=1,Length:
=4).Font
.Name="Arial"
.FontStyle="加粗"
.Size=8
.Strikethrough=False
.Superscript=False
.Subscript=False
.OutlineFont=False
.Shadow=False
.Underline=xlUnderlineStyleNone
.colorIndex=5
EndWith
Range("E1:
F1").Select
WithSelection.Font
.Name="Arial"
.FontStyle="加粗"
.Size=8
.Strikethrough=False
.Superscript=False
.Subscript=False
.OutlineFont=False
.Shadow=False
.Underline=xlUnderlineStyleNone
.colorIndex=5
EndWith
WithSelection
.HorizontalAlignment=xlCenter
.VerticalAl
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 最新Excel VBA编程实例 最新 Excel VBA 编程 实例
![提示](https://static.bdocx.com/images/bang_tan.gif)