python调用vbs脚本_第158讲 VB/VBS/VBA——别再傻傻分不清

首先来讲讲这三种语言的区别：

VB: Visual Basic, 是Microsoft公司开发的一种通用的基于对象的程序设计语言，为结构化的、模块化的、面向对象的、包含协助开发环境的事件驱动为机制的可视化程序设计语言。

VBS: VBScript, 是默认的ASP语言，还可以用在Windows脚本编写和网页编码中。尽管它的语法类似于VB，但是它却是一种完全不同的语言。VBS 不使用 VB运行库运行，而是由Windows脚本主机解释执行。

VBA: Visual Basic for Applications, 包含在微软的应用程序中(如Microsoft Office)，是一种集成在office后台适合自动化处理重复繁琐任务的宏语言。

来聊聊这三种语言的关系，VBS和VBA的语法和VB几乎一样，但是也有区别，VBS和VBA其实都是Visual Basic的派生语言。我们先来看看三种语言的运行环境。

VB, 应该是一种比价老的语言了，多年前大学的教学语言。VB6 是可编译可解释的语言，可以作为真正的编译型语言，也可以作为编译-解释混合型语言。VB 的最后一个版本是微软于1998年发布的6.0版本，之后就被基于 .NET 平台的VB.NET所取代。既然现在有了Python, VB大可不必学了。它的编译-解释器如下图所示。这个图已经成为经典的历史了。

VBS: 这是一种windows自带的解释型脚本语言，当你安装了windows，你就安装了这种语言。大概很多人还不知道这个解释器在哪里。它就在C:\Windows\System32 这个路径一般默认为系统的PATH环境变量。

对，它的解释器就是wscript.exe, 类比python的解释器python.exe你就懂了，如图:

VBA: 即是集成在office中的一种宏语言，这个语言天生的优势是集成在office中，对于搞自动化很实用，在四大，投行，咨询，会计公司，VBA可以说是他们的最爱，也是最适合实用的。Python现在也很强大，唯一的缺点是微软还没把Python集成在office里面，导致很多功能受限。处理Excel, Word, PPT都可以用VBA实现自动化, 有时候你处理文档用了一天，可能别人就几分钟的事情。比如你的老师不喜欢你的PPT中的字体，而你的PPT已经做了一百多页了，难道你要一页一页改吗，有了VBA，可能就是分分钟的事情。

既然vbs和python都是脚本语言，有了解释器和环境变量，就可以直接在dos系统下运行了。我们在D盘下建立两个脚本

比如运行VBScript: 

比如运行Python:

比如运行Java

只不过vbs的解释器是windows自带的, python和java的解释器和编译器需要自己安装。现在应该明白vbs是一种什么语言，作为一种windows历史遗留的解释型脚本语言，唯一的优势是集成在windows中，可能比不上高大上的Python。微软也正在准备将python作为windows自带的脚本语言。

VB系列语言虽然现在用的人少了，但是现在office产品依旧保留着VB特色，对于经常用office的人还是很实用的。目前流行的编程语言还是这些，比如C，Java，Python，C++，C#，Visual Basic, JavaScript，PHP，R，SAS, SQL。当然还有一些很年轻的语言，比如Kotlin, Rust, Julia等。

最后给大家带来一份VBA脚本，如果你手动做以下事情可能需要大半天的时间，用这个脚本可能只要几分钟。(最后加个macro, 分别call)

'_author_: weineng zhou'Date: 2020-05-31'function: 给每个sheet header 加上CONTENT的单元格的引用内容Sub Ref_header()Dim row As Integerrow = 7Do While ThisWorkbook.Sheets("CONTENT").Cells(row, 1) <> ""        For i = 1 To Sheets.Count            If ThisWorkbook.Sheets(i).Name = ThisWorkbook.Sheets("CONTENT").Cells(row, 1) Then                    ThisWorkbook.Sheets(i).Cells(2, 4) = "=CONTENT!B3"            ThisWorkbook.Sheets(i).Cells(3, 4) = "=CONTENT!B4"            ThisWorkbook.Sheets(i).Cells(4, 4) = "=CONTENT!B" & row            ThisWorkbook.Sheets(i).Cells(5, 4) = "=CONTENT!C" & row            ThisWorkbook.Sheets(i).Cells(6, 4) = "=CONTENT!D" & row            ThisWorkbook.Sheets(i).Cells(7, 4) = "=CONTENT!E" & row            ThisWorkbook.Sheets(i).Cells(8, 4) = "=CONTENT!F" & row            ThisWorkbook.Sheets(i).Cells(9, 4) = "=CONTENT!G" & row            ThisWorkbook.Sheets(i).Cells(10, 4) = "=CONTENT!H" & row            ThisWorkbook.Sheets(i).Cells(11, 4) = "=CONTENT!I" & row                        End If        Nextrow = row + 1LoopEnd Sub' VBA Scripts'_author_: weineng zhou'Date: 2020-06-11'function: 给CONTENT中每个ADaM添加超链接Sub Hyperlink_content()Sheets("CONTENT").SelectDim row As Integerrow = 7Do While ThisWorkbook.Sheets("CONTENT").Cells(row, 1) <> ""    For i = 1 To Sheets.Count        If ThisWorkbook.Sheets(i).Name = ThisWorkbook.Sheets("CONTENT").Cells(row, 1) Then                'MsgBox (ThisWorkbook.Sheets("CONTENT").Cells(row, 1))                       Range("A" & row).Select                        Selection.Borders(xlDiagonalDown).LineStyle = xlNone            Selection.Borders(xlDiagonalUp).LineStyle = xlNone            With Selection.Borders(xlEdgeLeft)                .LineStyle = xlContinuous                .ColorIndex = 0                .TintAndShade = 0                .Weight = xlThin            End With            With Selection.Borders(xlEdgeTop)                .LineStyle = xlContinuous                .ColorIndex = 0                .TintAndShade = 0                .Weight = xlThin            End With            With Selection.Borders(xlEdgeBottom)                .LineStyle = xlContinuous                .ColorIndex = 0                .TintAndShade = 0                .Weight = xlThin            End With            With Selection.Borders(xlEdgeRight)                .LineStyle = xlContinuous                .ColorIndex = 0                .TintAndShade = 0                .Weight = xlThin            End With            With Selection.Borders(xlInsideVertical)                .LineStyle = xlContinuous                .ColorIndex = 0                .TintAndShade = 0                .Weight = xlThin            End With            With Selection.Borders(xlInsideHorizontal)                .LineStyle = xlContinuous                .ColorIndex = 0                .TintAndShade = 0                .Weight = xlThin            End With                'Selection.Hyperlinks.Delete                        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _                "ADaM%20Domain%20Mapping%20Specifications_v1.0.xlsx", SubAddress:=ThisWorkbook.Sheets(i).Name + "!A1", _                TextToDisplay:=ThisWorkbook.Sheets(i).Name            End If    Nextrow = row + 1LoopSheets("CONTENT").SelectFor n = 1 To 50    If ThisWorkbook.Sheets("CONTENT").Cells(n, 1) = "Codelist" Then                Range("A" & n).Select        Selection.Hyperlinks.Delete        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _            "ADaM%20Domain%20Mapping%20Specifications_v1.0.xlsx", SubAddress:="Codelist" + "!A1", _            TextToDisplay:="Codelist"        End IfNextEnd Sub' VBA Scripts'_author_: weineng zhou'Date: 2020-06-17'function: 给Excel每个sheet添加超链接Sub Hyperlink_sheet()Dim row As Integerrow = 7Do While ThisWorkbook.Sheets("CONTENT").Cells(row, 1) <> ""    For i = 1 To Sheets.Count        If ThisWorkbook.Sheets(i).Name = ThisWorkbook.Sheets("CONTENT").Cells(row, 1) Then                        Sheets(i).Select                        For x = 1 To 200                            If UCase(ThisWorkbook.Sheets(i).Cells(x, 1)) = "CONTENT" Then                                        Range("A" & x).Select                                        Selection.Hyperlinks.Delete                    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _                        "ADaM%20Domain%20Mapping%20Specifications_v1.0.xlsx", SubAddress:= _                        "CONTENT!A" & row, TextToDisplay:="CONTENT"                    Application.Goto Reference:="Hyperlink_sheet"                                End If                            Next                    End If    Nextrow = row + 1LoopEnd Sub' VBA Scripts'_author_: weineng zhou'Date: 2020-06-17'function: 批量改变字体、大小Sub FontLoop()    For i = 1 To Sheets.Count                '选择每个sheet        Sheets(i).Select        '选择每个单元格，相当于Ctrl+A全选        Cells.Select                With Selection.Font            .Name = "宋体"            .Strikethrough = False            .Superscript = False            .Subscript = False            .OutlineFont = False            .Shadow = False            .TintAndShade = 0            .ThemeFont = xlThemeFontMinor        End With        With Selection.Font            .Name = "Arial"            .Strikethrough = False            .Superscript = False            .Subscript = False            .OutlineFont = False            .Shadow = False            .TintAndShade = 0            .ThemeFont = xlThemeFontNone        End With        With Selection.Font            .Size = 8            .Strikethrough = False            .Superscript = False            .Subscript = False            .OutlineFont = False            .Shadow = False            .TintAndShade = 0            .ThemeFont = xlThemeFontNone        End With                '退出全选状态，激活单元格A1        Range("A1").Select            NextEnd Sub