技術文章 » [Skill]VBA零基礎入門及範例：批次將連結轉成圖片

[Skill]VBA零基礎入門及範例：批次將連結轉成圖片

2021-04-11 03:00:56

簡介

VBA（Visual Basic for Applications）是依附在應用程式（例如Excel）中的VB語言。只要你安裝了Office Excel就自動預設安裝了VBA，同樣Word和PowerPoint也能呼叫VBA對軟體進行二次開發而讓一些特別複雜的操作「指令碼化」。

如何開啟VBA

1. 開啟」開發工具「功能（首次使用VBA）

如果你是第一次使用VBA，需要開啟「開發工具」功能。

Window：檔案——選項——自定義功能區——勾選開發工具

Mac：Excel——偏好設定——檢視

image.png

image.png

2. 開啟VBA的三種方式

2.1 開發工具——VisualBasic

image.png

2.2 ALT+F11快捷鍵
2.3 右鍵sheet頁檢視程式碼

image.png

3. VBA介面

image.png

一個簡單的VBA程式

大部分程式入門都會寫一個程式碼輸出「Hello World」，我們寫第一個程式在選定的單元格輸出自己的暱稱。

Sub 插入文字() 'sub定義一個過程
    Selection.Value = "TOMOCAT" '程式碼塊
End Sub '結束一個過程

1. 新建模組

模組方便我們匯出程式碼用於其他的Excel，所以養成良好的程式設計習慣插入模組。

image.png

２. 在指定區域寫程式碼

image.png

3.執行程式碼

下面三種方法實現的功能相同，無須太糾結，選擇最方便的即可。

  • F5執行
  • 執行索引標籤

image.png

  • 執行按鈕

image.png

一點小建議——使用「立即視窗」

如果你用過Rstudio寫R程式碼或者Spyder寫Python程式碼的話，「立即視窗」類似於控制檯，能提示程式碼編譯錯誤和進行實時計算。

1. 開啟「立即視窗

檢視——立即視窗

image.png

2. 在立即視窗輸入程式碼直接作用於excel

選中一個單元格，然後在立即視窗輸入程式碼（不必定義Sub過程），敲擊確認鍵執行：

image.png

可以看到執行後被選中的單元格出現了你的暱稱，到此為止你已經完成了第一個VBA程式。

範例：將URL轉化成圖片

1. 背景描述

現在excel中有多個圖片連結，我們希望將這些連結都轉成圖片。

2. 方法一：同時保留連結和圖片

開發工具——Visual Basic（或者ALT+F11快捷鍵）進入VB介面，然後雙擊sheet1按鈕開啟VB程式設計視窗：

image.png

輸入如下程式碼並儲存：

Sub loadimage()
Dim HLK As Hyperlink, Rng As Range
For Each HLK In ActiveSheet.Hyperlinks  '迴圈活動工作表中的各個超連結
    If HLK.Address Like "*.jpg" Or HLK.Address Like "*.gif" Or HLK.Address Like "*.png" Then  '如果連結的位置是jpg或gif圖片(此處僅針對此兩種圖片型別,更多型別可以通過建立陣列或字典或正則來判斷)
        Set Rng = HLK.Parent.Offset(, 1)  '設定插入目標圖片的位置
        With ActiveSheet.Pictures.Insert(HLK.Address)  '插入連結地址中的圖片
            If .Height / .Width > Rng.Height / Rng.Width Then  '判斷圖片縱橫比與單元格縱橫比的比值以確定針對單元格縮放的比例
                .Top = Rng.Top
                .Left = Rng.Left + (Rng.Width - .Width * Rng.Height / .Height) / 2
                .Width = .Width * Rng.Height / .Height
                .Height = Rng.Height
            Else
                .Left = Rng.Left
                .Top = Rng.Top + (Rng.Height - .Height * Rng.Width / .Width) / 2
                .Height = .Height * Rng.Width / .Width
                .Width = Rng.Width
            End If
        End With
    End If
Next
End Sub

開發工具-宏-執行：

image.png

執行結果：

image.png

2. 刪除連結只保留圖片（插入VB指令碼方式）

新建記事本儲存以下程式碼另存為.bas格式：

'charset GB2312 . Excel 中的圖片連結轉為圖片檔案

Attribute VB_Name = "LoadImage"
Sub LoadImage()
Dim HLK As Hyperlink, Rng As Range
For Each HLK In ActiveSheet.Hyperlinks  '迴圈活動工作表中的各個超連結
    If UCase(HLK.Address) Like "*.JPG" Or UCase(HLK.Address) Like "*.JPEG" Or UCase(HLK.Address) Like "*.PNG" Or UCase(HLK.Address) Like "*.GIF" Then  '如果連結的位置是jpg或gif圖片(此處僅針對此兩種圖片型別,更多型別可以通過建立陣列或字典或正則來判斷)
        Set Rng = HLK.Parent.Offset(, 0)  '設定插入目標圖片的位置
        With ActiveSheet.Pictures.Insert(HLK.Address)  '插入連結地址中的圖片
            If .Height / .Width > Rng.Height / Rng.Width Then  '判斷圖片縱橫比與單元格縱橫比的比值以確定針對單元格縮放的比例
                .Top = Rng.Top
                .Left = Rng.Left + (Rng.Width - .Width * Rng.Height / .Height) / 2
                .Width = .Width * Rng.Height / .Height
                .Height = Rng.Height
            Else
                .Left = Rng.Left
                .Top = Rng.Top + (Rng.Height - .Height * Rng.Width / .Width) / 2
                .Height = .Height * Rng.Width / .Width
                .Width = Rng.Width
            End If
        End With
        HLK.Parent.Value = ""   '刪除單元格的圖片連結
    End If
Next
End Sub

在VB介面右鍵sheet頁選擇匯入檔案：

image.png

image.png

執行效果：
image.png

3. 主動選擇是否開啟圖片

同方法1，但是需要選擇宣告為BeforeRightClick，設定為右鍵時觸發：

With Target
        If Left(.Value, 7) = "http://" Then '如果單元格內容為網址
            '新增網路圖片，並設定為圖片大小位置隨單元格變化而變化
            ActiveSheet.Shapes.AddPicture(.Value, msoCTrue, msoCTrue, .Left, .Top, .Width, .Height).Placement = xlMoveAndSize
            .WrapText = True '單元格設定為自動換行，以隱藏網址
        End If
End With

執行宏後右擊單元格就可以展示圖片。

4. 補充

如果你的Excel未能正確將網址識別成超連結，可以使用如下程式碼：

Sub loadimage()
Dim ranTotal As Range, rng As Range, imageRng As Range '設定三個Range變數
Set rngTotal = Range("o:o")  '選中存放網址的o列
For Each rng In rngTotal     '遍歷所有的o列單元格
    If Left(rng.Value, 7) = "http://" Then '如果單元格內容為網址
        Set imageRng = rng.Offset(, 1)  '存放圖片的地址
        With ActiveSheet.Pictures.Insert(rng.Value)
            If .Height / .Width > imageRng.Height / imageRng.Width Then  '判斷圖片縱橫比與單元格縱橫比的比值以確定針對單元格縮放的比例
                .Top = imageRng.Top
                .Left = imageRng.Left + (imageRng.Width - .Width * imageRng.Height / .Height) / 2
                .Width = .Width * imageRng.Height / .Height
                .Height = imageRng.Height
            Else
                .Left = imageRng.Left
                .Top = imageRng.Top + (imageRng.Height - .Height * imageRng.Width / .Width) / 2
                .Height = .Height * imageRng.Width / .Width
                .Width = imageRng.Width
            End If
        End With
    End If
Next
End Sub