VBA的应用两例 | Keep Learning

VBA应用两例与思考

可能是公众号更新频率太低，所以触发了平台的一些机制，所以最近的几篇powerbi文章都被推荐了。

本期更新两个VBA问题的思路以及其中案例关于EXCEL 和Powerbi的应用思考。

案例1 VBA如何搭配windows计划任务来实现自动化

背景

目前我有一个已经写好的excel和VBA程序，这个程序可以实现数据刷新以及自动发送邮件的功能。不足之处是，我必须打开excel手动运行宏。所以我希望配置到windows计划任务里面去，然后设置成每天或每周可以自动执行。

思路分析

VBA有个函数是workbook.open(),他的作用是在文件打开时，要运行的东西。所以我们可以将这些宏放到这里面去运行。然后写一个bat文件，调用计划任务来执行。

这里有一个问题，如果我们在宏执行了自动关闭，并且将文件发送给其他人时，文件会打开运行后就自动关闭。接收的人可能根本看不到具体内容。

解决方案是我们可以用另外一个excel来做中转，即计划任务➡bat➡中转excel➡实际执行的excel 的流程，然后最后关闭所有的文件。
相关的代码如下：
bat脚本，调用excel

1 2	@echo off start "" "C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE" "C:\Users\ttt\Desktop\run.xlsm"

中转excel宏

Private Sub Workbook_Open()
    Workbooks.Open "C:\Users\ttt\Desktop\test.xlsm"
    Application.DisplayAlerts = False
    '调用实际的宏来执行
    Application.Run "'test.xlsm'!filldata"
    ActiveWorkbook.Save
    '运行完关闭整个excel程序
    Application.Quit
    Application.DisplayAlerts = True
End Sub

实际执行的范例宏

Sub filldata()
Application.DisplayAlerts = False
Range("A1") = 1
Application.DisplayAlerts = True
End Sub

在我们调用过程中，可以将需要的宏都在这一个sub里面进行引用。

案例2 VBA如何实现创建枢纽分析表并格式化4

背景

有人提到希望使用宏去将数据切分成不同的文件，然后将切分完的数据进行枢纽分析。切分数据，其实逻辑就很简单，无非就是筛选，复制，新建文件，粘贴保存。而使用VBA创建枢纽分析表，还需要对条件进行不同的筛选。这个属于之前未研究的部分。

具体实现

在搜索和相关资料后，实现的具体步骤如下。我们做一个demo数据。分别有三列。

date	name	cost
202401	abc	10
202402	def	20

Sub create()

Dim pvtCache As PivotCache
Dim pvt As pivotTable
Set wks = Worksheets.Add

Set pvtCache = ActiveWorkbook.PivotCaches.create(SourceType:=xlDatabase, SourceData:=Sheets("raw").Range("A1:C7"))
Set pvt = pvtCache.CreatePivotTable(TableDestination:=wks.Range("A3"), DefaultVersion:=xlPivotTableVersion12)
With pvt
    With .PivotFields("date")
    .Orientation = xlRowField  '添加到行
    .Position = 1
    End With

    
    With .PivotFields("name")
        .Orientation = xlColumnField  '添加到列
        .Position = 1
    End With
    
    With .PivotFields("cost")
        .Orientation = xlPageField  '添加到筛选
        .Position = 1
    End With

 .AddDataField .PivotFields("cost"), "cost total", xlSum
End With
End Sub

在实现过程中，刚开始会一直报错，有部分原因是这个pvtcache问题。可以关闭excel重新打开运行。
另外，针对一些样式设置。下面的语法，可以取消层级的统计和重复显示这个数据（类似tabular）。

With .PivotFields("cost")
   .Orientation = xlRowField
   .LayoutForm = xlTabular
   .RepeatLabels = True
    .Subtotals _
   = Array(False, False, False, False, False, False, False, False, False, False, False, False _
   )
   End With

数据处理的反思

在过去一段时间，我们在分析数据时往往依赖于EXCEL公式和VBA处理。当后面接触了Python和powerbi,慢慢就进行了一些转变。自动化数据处理部分开始使用python搭配windows计划任务来实现，而展示部分使用powerbi来实现。

这样的好处是什么呢？就像上面的枢纽分析表一样，我们用powerbi可能很快就搭建好一个矩阵，放置上对应的筛选器就可以了。如果需要权限限制，我们配上对应的角色控制。而用VBA，往往要写代码，debug很久。很难做到低代码实现。
如下图，可以配置不同的账户，不同的权限。

而自动化Python呢，有各种成熟的库供我们调用，数据的处理速度也要快很多。所以我渐渐的减少VBA的使用频率。当然VBA有其本身的优势，就是无需额外安装，直接就可以调用。但是从目前的趋势来看，微软也慢慢将python加到excel编辑环境里面来了。

最后要说的是，工具虽然便利，同样还要看最终的需求，就像我推荐了半天powerbi，然后客户或者老板就要看excel。那也只能慢慢研究。多备几个锤子，哪个能砸的了钉子就用哪个砸。