首先來講講這三種語言的區(qū)別：
VB: Visual Basic, 是Microsoft公司開發(fā)的一種通用的基于對象的程序設(shè)計語言，為結(jié)構(gòu)化的、模塊化的、面向?qū)ο蟮?、包含協(xié)助開發(fā)環(huán)境的事件驅(qū)動為機(jī)制的可視化程序設(shè)計語言。
VBS: VBScript, 是默認(rèn)的ASP語言，還可以用在Windows腳本編寫和網(wǎng)頁編碼中。盡管它的語法類似于VB，但是它卻是一種完全不同的語言。VBS 不使用 VB運行庫運行，而是由Windows腳本主機(jī)解釋執(zhí)行。
VBA: Visual Basic for Applications, 包含在微軟的應(yīng)用程序中(如Microsoft Office)，是一種集成在office后臺適合自動化處理重復(fù)繁瑣任務(wù)的宏語言。
來聊聊這三種語言的關(guān)系，VBS和VBA的語法和VB幾乎一樣，但是也有區(qū)別，VBS和VBA其實都是Visual Basic的派生語言。我們先來看看三種語言的運行環(huán)境。
VB, 應(yīng)該是一種比價老的語言了，多年前大學(xué)的教學(xué)語言。VB6 是可編譯可解釋的語言，可以作為真正的編譯型語言，也可以作為編譯-解釋混合型語言。VB 的最后一個版本是微軟于1998年發(fā)布的6.0版本，之后就被基于 .NET 平臺的VB.NET所取代。既然現(xiàn)在有了Python, VB大可不必學(xué)了。它的編譯-解釋器如下圖所示。這個圖已經(jīng)成為經(jīng)典的歷史了。
VBS: 這是一種windows自帶的解釋型腳本語言，當(dāng)你安裝了windows，你就安裝了這種語言。大概很多人還不知道這個解釋器在哪里。它就在C:\Windows\System32 這個路徑一般默認(rèn)為系統(tǒng)的PATH環(huán)境變量。
對，它的解釋器就是wscript.exe, 類比python的解釋器python.exe你就懂了，如圖:
VBA: 即是集成在office中的一種宏語言，這個語言天生的優(yōu)勢是集成在office中，對于搞自動化很實用，在四大，投行，咨詢，會計公司，VBA可以說是他們的最愛，也是最適合實用的。Python現(xiàn)在也很強(qiáng)大，唯一的缺點是微軟還沒把Python集成在office里面，導(dǎo)致很多功能受限。處理Excel, Word, PPT都可以用VBA實現(xiàn)自動化, 有時候你處理文檔用了一天，可能別人就幾分鐘的事情。比如你的老師不喜歡你的PPT中的字體，而你的PPT已經(jīng)做了一百多頁了，難道你要一頁一頁改嗎，有了VBA，可能就是分分鐘的事情。
既然vbs和python都是腳本語言，有了解釋器和環(huán)境變量，就可以直接在dos系統(tǒng)下運行了。我們在D盤下建立兩個腳本
比如運行VBScript:
比如運行Python:
比如運行Java
只不過vbs的解釋器是windows自帶的, python和java的解釋器和編譯器需要自己安裝。現(xiàn)在應(yīng)該明白vbs是一種什么語言，作為一種windows歷史遺留的解釋型腳本語言，唯一的優(yōu)勢是集成在windows中，可能比不上高大上的Python。微軟也正在準(zhǔn)備將python作為windows自帶的腳本語言。
VB系列語言雖然現(xiàn)在用的人少了，但是現(xiàn)在office產(chǎn)品依舊保留著VB特色，對于經(jīng)常用office的人還是很實用的。目前流行的編程語言還是這些，比如C，Java，Python，C++，C#，Visual Basic, JavaScript，PHP，R，SAS, SQL。當(dāng)然還有一些很年輕的語言，比如Kotlin, Rust, Julia等。
最后給大家?guī)硪环軻BA腳本，如果你手動做以下事情可能需要大半天的時間，用這個腳本可能只要幾分鐘。(最后加個macro, 分別call)
'_author_: weineng zhou'Date: 2020-05-31'function: 給每個sheet header 加上CONTENT的單元格的引用內(nèi)容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 '選擇每個單元格，相當(dāng)于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 '退出全選狀態(tài)，激活單元格A1 Range("A1").Select NextEnd Sub聯(lián)系客服
