VBA（1）-20200216

前言

最近有一项工作涉及到较多的excel表格处理，具体是在每日股市开盘前更新前一交易日二级市场的一些资金数据（已通过python实现每日从wind中自动获取），但是要在短时间内（九点上班，九点半开盘）完成近二十张图的数据源更新，并变成图片以PPT的形式呈现出来，同时还要相应修改文字内容，工作量还是比较大的。因此，VBA就自然而然的成为了提高这一项工作效率的重要工具：通过VBA实现了图片数据源的自动更新，并与PPT联动，将更新后的图片移动至PPT中相应位置，我只需要修改文字即可。这一次实现拓展了我对于VBA的认知，也让我开始重新重视起VBA。

我对VBA的学习开始于两年前，但只是一些基础和常见的实现，并不算系统。之后的学习，主要是在实际应用场景中逐渐积累（通过看别人的代码+自己录制宏看代码），以实现需求为导向，保存下来了一个又一个.bas文件，但是由于不像python高频使用，许多代码还是需要查阅以前写的宏回忆。因此，希望可以通过这一系列博文的契机进行梳理和记录，以形成自己的VBA Toolkit。

20200216目录
一、获取最后一行/列
二、修改图数据源
三、PPT图片元素相关

一、获取最后一行/列

获取最后一行/列是定位常用的功能，方法主要分为两类，一类是只考虑实际的行列，另一类是全部使用的区域含设置格式但无内容的行列

1.只考虑实际的行列

ActiveSheet.Range("A1048576").End(xlUp).Row
ActiveSheet.Range("IV1").End(xlToleft).Column

Cells.Find(What:="*",SearchOrder:=xlByColumns,SearchDirection:=xlPrevious).Column
Cells.Find(What:="*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

2.使用区域的行/列

仅设置格式没有内容也会统计

ActiveSheet.UsedRange.Rows.Count
ActiveSheet.UsedRange.Columns.Count

Cells.SpecialCells(xlCellTypeLastCell).Row
Cells.SpecialCells(xlCellTypeLastCell).Column

ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column

获取最后一列的字母

Split(ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Address, "$")(1)

获取倒数第二列的字母

col_num =ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).column
Split(Cells(1, col_num - 1).Address, "$")(1)

二、修改图数据源

用于与获取最后一行/列功能结合，自动更新图片，图名称可以通过录制宏查看

ActiveSheet.ChartObjects("图表 1").Activate
ActiveChart.SetSourceData Source:=Range(" 'sheet1'!A:A,'shee1'!" last_col & ":" & last_col )

注意，这里的 Source:=Range()，有时会包括多个非连续区域（如标题与数据行不在连续的行列上），需要用union(range(),range())

三、PPT图片元素相关

1.打开PPT

在运行前，需工具–引用–Mircosoft PowerPoint x.x Object Libray

Set myppt = CreateObject("PowerPoint.Application")
myppt.Visible = True
Set pptfile = myppt.Presentations.Open(ThisWorkbook.Path & "\PPT.ppt")
最后End Sub()前要Set myppt = Nothing

2.获取PPT元素名称

获取第一页ppt，第一个元素的名称
元素的名称中会包括元素的类型，如Picture 1、TextBox 1、Rectangle 1等等，可以通过将所有元素名称输出，根据类型得到想要进行操作的元素名称，通过指定名称进行操作

shape1_name=pptfile.Slides(1).Shapes(1).Name
pptfile.Slides(1).Shapes(shape1_name).Delete

3.粘贴图片到PPT

将excel图片粘贴到ppt一般使用增强型图元文件以保证图片清晰度

pptfile.Slides(1).Shapes.PasteSpecial ppPasteEnhancedMetafile
增强型图元文件

4.设置PPT元素位置

选择所要操作的元素，设置指定位置主要通过三个参数，一是Height/Width，可以指定元素的Size，left指定距离左侧的距离，top指定距离顶部的距离

pptfile.Slides(1).Shapes(shape1_name).Select
With myppt.ActiveWindow.Selection.ShapeRange
    .Height = 130
    .Left = 5
    .Top = 120
End With