|
最近一直在研究VBA，写报表的工作。将所得的知识，经验总结一下，与大家分享。
工具篇，VBA
1.EXCEL一个最好用的功能就是录制宏，当一个函数拿不准要怎么写，用什么函数表示的时候，录制就帮了大忙了。
2.VBA相对于录制的宏，可以更精确，更聪明，逻辑更清晰，执行也更有效。特别是它的循环和递归，直接解放了人的双手。
3.VBA的基本操作
（1）定位：
基本单位：sheets(页)，Rows(行），Columns(列），cells(单元格），RANGE(一个范围的单元格）
相对于每个基本单位，我们都可以选择跳转（select),清空（clear),取消合并（UnMerge),合并（Merge).删除（delete),激活（activate),复制（copy），粘贴（paste)
其实它并不局限于当前的某行某列，更加有效的方法是在规则上的控制。比如：某页的最后一行，某页的最后一列。
这样的方式就会更加灵活。
LastRow = Sheets('名称“).Range('a65536').End(xlUp).Row '取数据表的最后一行数据的行号
Lastcol = Sheets('名称').Cells(6, 255).End(xlToLeft).Column '取数据部的第六行的最后一列的数据。
(2) 时间
如果你想定位时间，比如今天
1.直接用EXCEL的函数。并且只取其值，不取函数。
Range('D6').Select
ActiveCell.FormulaR1C1 = '=Today()'
Range('D6') = Range('D6').Value
2. VBA函数DATE.
Range('D6').Select
Range('D6').Value=date
（3）.排版
列宽
Columns('C').ColumnWidth = 5
Columns('B').ColumnWidth = 22
Range(Columns(4), Columns(40)).ColumnWidth = 9
行宽
（4）.循环
while
........
wend
优点：当有两个表，或者两个变量的时候，该while特别好用。
for i =0 to 18 step 2
......
next
优点：特别适合一张表。
（5）条件判断
IF .....THEN..... ENDIF条件判断的利器
多个条件判断用case when
Select Case LRegion
Case 'N'
LRegionName = 'North'
Case 'S'
LRegionName = 'South'
Case 'E'
LRegionName = 'East'
Case 'W'
LRegionName = 'West'
End Select
（6） 统计
1 COUNTIF. Application.CountIf(Range(Cells(8, 3), Cells(32, 3)), 'Up') 统计范围内”UP“的个数。
2 VLOOKUP.Application.VLookup(Cells(mNum, 2), Worksheets('UMP关键key值').Range('A:I'), 9, 0) 查询函数。
（7）创建新页并起名
Sheets.Add After:=Sheets(Sheets.count)
aa = Sheets('名称').Cells(38, i)
Sheets(Sheets.count).Name = aa
Sheets(Sheets.count).Select
（8）连字符&。具有无比的粘性，将字符和数字连在一起。
aa = Sheets('UMP关键key值').Range('I' & i)
ab = Sheets('UMP关键key值').Range('J' & i)
ac = aa & ' - ' & ab
(9)空白行删除
Range('a1:a' & LastRow).SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
（10）图表的制作和定位。
Set ab = Range('I' & 2 * i & ':g' & 2 * i + 1)-----位置
Set bbb = ActiveSheet.ChartObjects.Add(0, 0, 0, 0)
bbb.Chart.ChartType = xlPie --饼图， bbb.Chart.ChartType = xlDoughnut --圆环
bbb.Chart.SetSourceData Source:=Range('F' & 2 * i & ':G' & 2 * i + 1)---数据源
bbb.Chart.SetElement (msoElementLegendNone)
圆环的大小尺寸
bbb.Chart.SeriesCollection(1).Select
bbb.Chart.ChartGroups(1).DoughnutHoleSize = 70
If Cells(2 * i, 5).Value > 0 Then
bbb.Chart.ChartStyle = 4
Else
bbb.Chart.ChartStyle = 3
End If-----颜色分布
With bbb
.Top = ab.Top
.Left = ab.Left
.Width = ab.Width
.Height = ab.Height
End With
(11)函数取整。
t = Int(Cells(2 * i, 3) / Cells(2 * i, 4))
（12）加文本框
Set cd = Sheets('图表').Range('C' & i + 5 + (i - 1) * 10 & ':c' & i + 6 + (i - 1) * 10)-位置
Set ddd = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 0, 0, 0) --创建文本框
With ddd
.Top = cd.Top
.Left = cd.Left
.Width = cd.Width
.Height = cd.Height
End With
ddd.Select
Selection.ShapeRange.Line.Visible = msoFalse -无框
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = Int(Abs(Sheets('数据').Cells(2 * i, 5).Value) * 100) & '%'
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Font
.NameComplexScript = '+mn-cs'
.NameFarEast = '+mn-ea'
.Fill.Visible = msoTrue
.Fill.ForeColor.ObjectThemeColor = msoThemeColorDark1
.Fill.ForeColor.TintAndShade = 0
.Fill.ForeColor.Brightness = 0
.Fill.Transparency = 0
.Fill.Solid
.Size = 13 -字体
.Name = '+mn-lt'
End With
（13）添加箭头
For i = 1 To lastrow - 1
Sheets('图表').Activate
Set mn = Sheets('图表').Range('C' & 6 + (i - 1) * 11) -位置
If Sheets('数据').Cells(2 * i, 5).Value > 0 Then
Set ooo = ActiveSheet.Shapes.AddShape(msoShapeUpArrow, 0, 0, 0, 0) -上箭头
With ooo
.Top = mn.Top
.Left = 158.25
.Width = 3.6
.Height = 33
End With
ooo.Select
Selection.ShapeRange.ShapeStyle = msoShapeStylePreset10 红色
Else
Set ooo = ActiveSheet.Shapes.AddShape(msoShapeDownArrow, 0, 0, 0, 0) 下箭头
With ooo
.Top = mn.Top
.Left = 158.25
.Width = 3.6
.Height = 33
End With
ooo.Select
Selection.ShapeRange.ShapeStyle = msoShapeStylePreset9
End If
Next
（14） 组合
For i = 1 To lastrow - 1
Sheets('图表').Activate
If Sheets('数据').Cells(2 * i, 5).Value > 0 Then
ActiveSheet.Shapes.Range(Array('TextBox ' & (i + 15), 'Up Arrow ' & (30 + i), 'Chart ' & i)). _
Select
Else
ActiveSheet.Shapes.Range(Array('TextBox ' & (i + 15), 'Down Arrow ' & (30 + i), 'Chart ' & i)). _
Select
End If
Selection.ShapeRange.Group.Select -组合
Next
（15）文本
Range('A3').Value = '数据提取时间：' & Int(Now() - 1) & ' 17:00 至 ' & Int(Now()) & ' 17:00'
VBA 之所以比其他的编程语言简单，第一是数据都在excel的单元格中，调试起来比较的简单。
第二就是它的语言录制功能。函数的使用比较简单。
第三就是EXCEL和PPT的交互做的非常好，EXCEL数据的更新可以直接在PPT中体现。
(16) 趋势
http://hy-chou./2012/11/excel.html
VBA的缺点是只是针对单个文档做的，所以微软在之后的版本可能不会对他升级了。js为将来的趋势。
(17)表格颜色 Cells(i, 14).Interior.ColorIndex = 45
For i = 32 To lastrow12
If Range('N' & i).Value = green(0) Or Range('N' & i).Value = green(1) Or Range('N' & i).Value = green(2) Or Range('N' & i).Value = green(3) Or Range('N' & i).Value = green(4) Or Range('N' & i).Value = green(5) Then Cells(i, 14).Interior.ColorIndex = 10
If Range('N' & i).Value = yellow(0) Or Range('N' & i).Value = yellow(1) Then Cells(i, 14).Interior.ColorIndex = 45
If Range('N' & i).Value = '现货降低，周转提升' Then Cells(i, 14).Interior.ColorIndex = 3
next
表格颜色对照表
http://www.360doc.com/content/11/0126/08/395863_89065041.shtml
（18）arrary 的定义，起到数组的作用
green = Array('现货提升,周转降低', '现货提升,周转提升', '现货提升,周转稳定', '现货稳定,周转降低', '现货稳定,周转提升', '现货稳定,周转稳定')
yellow = Array('现货降低,周转降低', '现货降低,周转稳定')
|
