Excel is an indispensable tool in the office scene, but tedious repetitive operations can often be a headache. This article will explore how to quickly generate VBA code with the help of DeepSeek to automate Excel operations, through 3 practical cases, you can easily master the skills of using AI to improve office efficiency, say goodbye to the inefficiency and troubles of manual operations, and efficiently complete tasks such as periodic report generation, catalog creation, and complex data integration.
Excel must be a hurdle that every office worker cannot bypass – no matter how awesome the current AI is and how forced the product is, Excel is inseparable from the final practical link. However, Excel’s operations are relatively cumbersome, not to mention a large number of repetitive operation scenarios, which is very annoying.
At this time, we can use VBA tools to complete Excel automation through AI code writing
1. What is VBA?
VBA (Visual Basic for Applications) is a programming language embedded in Microsoft Office applications such as Excel, Word, Access, etc.
In Excel, VBA extends Excel’s native functionality by allowing users to write scripts (called “macros”) to automate tasks, customize functions, process data, create user interfaces, and more.
2. In the era of AI, why should we pay attention to VBA?
If you want to run code, you must have a “compiler”, such as Python, which requires additional installation of conda, cursor, etc
And VBA can be used directly by opening Excel, which is very convenient.
In other words, VBA is deeply integrated with Excel: it can directly and easily control every object (workbook, worksheet, cell, chart, etc.) in Excel to achieve refined control.
How can product managers do a good job in B-end digitalization?
All walks of life have taken advantage of the ride-hail of digital transformation and achieved the rapid development of the industry. Since B-end products are products that provide services for enterprises, how should enterprises ride the digital ride?
View details >
Especially in the current AI era, for repetitive tasks within Excel, DeepSeek can be used to quickly write scripts to achieve automation, and the input and output are relatively high.
3. VBA, Python, SQL: Positioning and selection in Excel data processing
In the field of data processing and automation, in addition to VBA, Python and SQL are also commonly used tools. How do I know which tool to use? VBA First: When automating repetitive tasks (such as report generation, format adjustment, data aggregation) within Excel in the main work scenario, requiring frequent operation of Excel interface elements, or quick implementation of lightweight automation. Python Preferred: When complex data analysis, large data processing, machine learning, or when automation scripts independent of Excel are required. After processing the data through Python, the results are output to Excel. SQL First: When data is stored in a relational database and needs to be queried and managed efficiently. The query results can be imported into Excel for further analysis or presentation.
In practice, these three tools are to be used together:
For example, you can use SQL to extract raw data from a database, Python for complex cleaning and analysis, and finally use VBA to generate formatted reports and add interactive features in Excel.
4. Develop VBA with DeepSeek
A. Open Excel and display the “Development Tools” tab
If the Dev Tools tab is not displayed in the Excel ribbon, follow these steps:
1. Click on the “File” menu and select “Options”.
2. In the Excel Options dialog box, select Customize Ribbon on the left.
3. In the Main Tab list on the right, make sure to check “Development Tools”.
4. Click OK.
B. Entering the VBA Editor (VBE)
To open VBE (Visual Basic Editor): From the “Development Tools” tab: Click the “Visual Basic” button in the ribbon.
c. The VBE interface mainly consists of the following windows: Project Explorer:
Displays all currently open Excel workbooks and the modules, class modules, forms, and sheet objects they contain. Properties Window: Displays the properties of the selected object in Project Explorer.
Code Window: Used to type, edit, and view VBA code. Immediate Window: Used to execute single lines of code, test expressions, and output debugging information.
In fact, you just need to know how to operate it, and it doesn’t matter if you know or don’t know the interface.
After completing the above preparations, you can start writing VBA code with the help of AI.
Practical operation
Case 1: Generate standardized report headers with one click
Background of the problem:
When producing periodic reports such as weekly and monthly reports, it is often necessary to repeatedly set the same header information, such as company name, report title, tabulation date, etc., and unify its format. Manual operations are not only tedious, but also prone to omissions or inconsistent formatting.
Steps:
1. Clarify the requirements and build AI instructions (prompts):
Assume the following requirements: in the active worksheet
Fill in cell A1 with “Example Corporation”,
Fill in the A2 cell with “Monthly Operation Data Report”,
Fill in the A3 cell with the current date.
The text of cells A1 and A2 should be bold, font size 14, and cell areas A1:C1 and A2:C2 should be merged and centered respectively.
Reference Words:
Please write a piece of Excel VBA code to do as follows in the current active worksheet:
1. Enter the text “Sample Corporation” in cell A1.
2. Enter the text “Monthly Operational Data Report” in cell A2.
3. Enter today’s date in cell A3 in the format “YYYY-MM-DD”.
4. Set the text in cells A1 and A2 to bold and adjust the font size to 14.
5. Merge cell areas A1:C1 and center its contents horizontally and vertically.
6. Merge cell areas A2:C2 and center their contents horizontally and vertically.
7. After all operations are completed, a pop-up window prompts “Report header has been generated”.
2. Generate VBA code with DeepSeek:
Sub GenerateReportHeader()
Dim ws As Worksheet
Get the current activity worksheet
Set ws = ActiveSheet
With ws
Fill in the header content
. Range(“A1”). value= “Example Inc.”
. Range(“A2”). value= “Monthly Operational Data Report”
.Range(“A3”).Value=Date
. Range(“A3”). NumberFormat = “YYYY-MM-DD” ‘ sets the date format
‘ Format A1 cells and merge
With .Range(“A1”)
.Font.Bold =True
.Font.Size =14
EndWith
With .Range(“A1:C1”)
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
EndWith
‘ Format A2 cells and merge
With .Range(“A2”)
.Font.Bold = True
.Font.Size = 14
End With
With .Range(“A2:C2”)
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
End With
MsgBox “Report header has been generated”, vbInformationEnd Sub
3. Execute the VBA code in Excel
First, create a new module in two ways:
Method 1: Excel-Development Tools-Macro, give it a name in the pop-up window, and click “Create”
Method 2: Click into the editing interface – Insert – Module
Then in the module below, empty the original content and paste the code given by AI
Finally, run the sub-process and that’s it!!
4. Running effect:
After the macro is executed, the A1:C3 area of the currently active worksheet will automatically generate the header content and format according to the preset requirements.
Code Description:
Sub GenerateReportHeader() and End Sub: Defines the start and end of a VBA process (macro).
Dim ws As Worksheet: Declare a variable named ws of type Worksheet (Worksheet Object).
Set ws = ActiveSheet: Assigns the currently active sheet object to the variable ws.
With ws … End With: A convenient structure that allows the use of dots . The properties and methods at the beginning are for WS objects by default.
. Range(“A1”). Value = “Text”: Sets the value of the specified cell (A1 here).
. Range(“A3”). NumberFormat = “YYYY-MM-DD”: Sets the number format of the cell.
. Font.Bold = True: Set the font to bold. Font.Size = 14: Set the font size.
. Range(“A1:C1”). Merge: Merge the specified cell area.
. HorizontalAlignment = xlCenter: Sets content level centering.
MsgBox “Prompt”, vbInformation: A message box with the specified prompt information pops up.
If you think it’s too simple, you can continue to use Deepseek to help us design a complex version:
Replace the original module, or create a new one:
Is it smelly?
Case 2: Automatically generate a worksheet directory with hyperlinks
Background of the problem:
When an Excel workbook contains a large number of worksheets (e.g., sales data for individual months, budget tables for different departments, etc.), manually finding and switching between sheets becomes inefficient.
For example, I now have multiple sheets in Excel, which are sales tables for each month.
Target:
Utilize VBA code to automatically create a worksheet named “Table of Contents”. The worksheet will list the names of all other worksheets in the workbook and create a hyperlink for each name for quick jumping. At the same time, add a “back to table of contents” hyperlink at a specific location in each data sheet (e.g. E1 cell).
Steps:
1. Clarify requirements and build AI instructions:
Please write Excel VBA code to implement the following functions:
1. Check if there is a worksheet named “Table of Contents” in the current workbook.
a. If it does not exist, create a new worksheet called “Table of Contents” at the end of all worksheets.
b. If it exists, empty all contents of the Table of Contents worksheet.
2. Write “Serial Number” in cell A1 and “Worksheet Name” in cell B1 of the “Catalog” worksheet, and bold the font of these two cells.
3. Starting from the second line, generate a list of all other worksheets in the Table of Contents worksheet:
a. A column is the serial number, increasing from 1.
b. B is the name of the corresponding worksheet.
c. The worksheet name in column B should be set to hyperlink, and click to jump to cell A1 of the corresponding worksheet. The link text color is set to blue.
4. Go through all non-“Table of Contents” worksheets in the workbook, create a hyperlink with the text “Back to Table of Contents” in cell E1 of each worksheet, and click to jump to cell A1 of the “Catalog” worksheet. This link text color is also set to blue. If cell E1 already has a hyperlink, remove it first.
5. Turn off the screen update before the code execution starts, and resume the screen update after it ends, and display “Directory generated/updated completed! “.
6. Automatically adjust the column width of column A and B of the Table of Contents worksheet to fit the content.
2. Generate vba code with DeepSeek v3:
Sub CreateOrUpdateTableOfContents()
Dim mainWorkbook As Workbook
Dim tocSheet As Worksheet
Dim ws As Worksheet
Dim rowIndex As Long
Dim sheetCounter As Long
Application.ScreenUpdating =False
Set mainWorkbook = ThisWorkbook
Step 1: Create or reference the Table of Contents worksheet
On Error Resume Next ‘ temporarily ignores errors so that the worksheet can be checked for its existence
Set tocSheet = mainWorkbook.Worksheets(“Directory”)
On Error GoTo 0′ resumes normal error handling
If tocSheet Is Nothing Then
Set tocSheet = mainWorkbook.Worksheets.Add(After:=mainWorkbook.Sheets(mainWorkbook.Sheets.Count))
tocSheet.Name = “Table of Contents”
Else
tocSheet.Cells.ClearContents ‘ Empty if present
End If
Step 2: Set the ‘Table of Contents’ worksheet header
With tocSheet
. Range(“A1”). Value = “Serial Number”
. Range(“B1”). Value = “Worksheet Name”
.Range(“A1:B1”).Font.Bold = True
End With
rowIndex = 2′ directory entry starts on the second line
sheetCounter =0
Step 3: Generate a directory list and hyperlinks
For Each ws In mainWorkbook.Worksheets
If ws. Name <> tocSheet.Name Then ‘Exclude the Directory worksheet itself
sheetCounter = sheetCounter +1
With tocSheet
. Cells(rowIndex, “A”). Value= sheetCounter ‘ serial number
. Cells(rowIndex, “B”). Value = ws. Name ‘ The name of the sheet
‘ to create a hyperlink to the corresponding worksheet
.Hyperlinks.Add Anchor:=.Cells(rowIndex, “B”), _
Address:=””, _
SubAddress:=”‘” & ws.Name & “‘!A1”, _
TextToDisplay:=ws.Name
.Cells(rowIndex, “B”).Font.Color = vbBlue
End With
rowIndex = rowIndex + 1
End If
Next ws
Step 4: Add a ‘Back to Table of Contents’ link to other worksheets
ForEach ws In mainWorkbook.Worksheets
If ws.Name <> tocSheet.Name Then
On Error Resume Next ‘ ignores errors that may not have a hyperlink
ws. Range(“E1”). Hyperlinks.Delete ‘ first deletes old links that may be present at E1
On Error GoTo 0
ws.Hyperlinks.Add Anchor:=ws.Range(“E1″), _
Address:=””, _
SubAddress:=”‘” & tocSheet.Name & “‘!A1″, _
TextToDisplay:=”Return to directory”
ws.Range(“E1”).Font.Color = vbBlue
End If
Next ws
Step 6: Automatically adjust column widths
tocSheet.Columns(“A:B”).AutoFit
Application.ScreenUpdating = True
Step 5: Complete the prompt
MsgBox “Catalog Generated/Updated!” , vbInformation
End Sub
Follow the same operation as in case 1, run it directly, and you can see the directory.
- Application.ScreenUpdating = False: Turning off screen refresh during macro execution can improve execution speed, especially when working with multiple worksheets.
- On Error Resume Next / On Error GoTo 0: Error handling statement in VBA. On error resume next enables the program to continue executing the next line of code when it encounters a runtime error, often used for small errors that are foreseeable and do not affect the main process. On Error GoTo 0 reverts to the standard error handling mechanism.
- Worksheets.Add(After:=Sheets(Sheets.Count)): Add a new worksheet after the last worksheet in the workbook.
- Hyperlinks.Add Anchor:=…, Address:=””, SubAddress:=”‘” & ws. Name & “‘! A1”, TextToDisplay:=… : The core method of adding hyperlinks.
- Anchor: The cell object to which the hyperlink is attached.
- Address: The address to an external file or URL (empty string for internal links).
- SubAddress: A string pointing to the location inside the current workbook, in the format ‘sheet name’! cell address.
- TextToDisplay: Hyperlinked text displayed in cells.
- vbBlue: VBA’s built-in color constant, representing blue.
- Columns(“A:B”). AutoFit: Automatically adjusts the width of a specified column to fit its content.
Through these two introductory cases, we can see the potential of AI in assisting in generating VBA code, allowing users to focus more on the requirements themselves rather than cumbersome syntax details. Case 3: Complex data integration and query
When dealing with data from different data sources or with complex structures, data integration and transformation are often required. In this case, we will use multiple CSV data files to simulate a common data processing scenario: associating fact table data with dimension table data to generate a new table with richer information. This operation is less efficient and error-prone if you manually use Excel formulas (such as VLOOKUP) to process large amounts of data.
Suppose I have multiple tables now, as shown in the figure below, namely area table, user table, product table, and order table.
The order table looks like this:
As you can see, the user and the product have no name, and my need is to let the order form match the corresponding name from the user table and the product table.
This is also a very classic requirement: multi-table matching.
Write a piece of Excel VBA code to consolidate data from multiple Excel files (.xlsx) in a specified folder into a new worksheet in the currently active workbook.
The specific requirements are as follows:
Select the source folder:
At the beginning of the program, a dialog box pops up asking the user to select the folder containing the source Excel file.
If the user does not select a folder, the program is aborted and prompted.
Define the source file name:
Fact Data File Name: “Model-FactSales.xlsx”
Product dimension data file name: “Model-DimProduct.xlsx”
Customer dimension data file name: “Model-DimCustomer.xlsx”
City Dimension Data File Name: “Model-DimCity.xlsx”
Data correlation and population:
The AI-generated VBA code is quite comprehensive: it includes elements such as folder selection, external file reading, dictionary application, and error handling:
Option Explicit ‘ forces all variables to be declared
Sub ConsolidateDataFromExternalFiles_V3()
Dim startTime As Double
startTime = Timer
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManua
Dim folderPath As String
Dim wbSource As Workbook, wbDim As Workbook
Dim wsSource As Worksheet, wsDim As Worksheet
Dim wsConsolidated As Worksheet
Dim dictProducts As Object, dictCustomers As Object, dictCities As Object
Dim factSalesFileName As String, productDimFileName As String, customerDimFileName As String, cityDimFileName As String
Dim arrFactSalesData As Variant ‘ is used to store the raw data of FactSales
Dim arrOutputData As Variant ‘ is used to build the final output data
Dim i As Long, j As Long
Dim lastRowFact As Long, lastColFact As Long
Dim lastRowOutput As Long, lastColOutput As Long
‘— The column number used to store the key key column in the FactSales table —
Dim spuColFact As Long
Dim customerIdColFact As Long
Dim cityIdColFact As Long
‘ — Used to store the column number of the new Name column in the Output array —
Dim productNameColOutput As Long
Dim customerNameColOutput As Long
Dim cityNameColOutput As Long
Dim tempKey As Variant
‘— 1. Select the source folder —
With Application.FileDialog(msoFileDialogFolderPicker)
. Title = “Please select the folder that contains the source Excel file”
.AllowMultiSelect =False
If .Show<>-1Then
MsgBox “No folder selected, operation aborted.” , vbExclamation
GoTo CleanUpAndExit
End If
folderPath = .SelectedItems(1)
If Right(folderPath, 1) <> Application.PathSeparator Then
folderPath = folderPath & Application.PathSeparator
End If
EndWith
‘ — 2. Define the source file name —
factSalesFileName = “Model-FactSales.xlsx”
productDimFileName = “Model-DimProduct.xlsx”
customerDimFileName = “Model-DimCustomer.xlsx”
cityDimFileName = “Model-DimCity.xlsx”
‘— 3.1 Creating or Resetting the “ConsolidatedSales” Worksheet —
On Error Resume Next
Application.DisplayAlerts =False
ThisWorkbook.Sheets(“ConsolidatedSales”).Delete
Application.DisplayAlerts =True
On Error GoTo 0
Set wsConsolidated = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
wsConsolidated.Name = “ConsolidatedSales”
‘ — Read ‘Model-FactSales.xlsx’ data to array —
If Len(Dir(folderPath & factSalesFileName)) = 0 Then
MsgBox “File” > factSalesFileName > ” not found in the specified folder! The operation was aborted. ”, vbCritical
GoTo CleanUpAndExit
End If
On Error GoTo FileOpenErrorFactSales
Set wbSource = Workbooks.Open(folderPath & factSalesFileName, ReadOnly:=True, UpdateLinks:=0)
Set wsSource = wbSource.Sheets(1)
arrFactSalesData = wsSource.UsedRange.Value ‘ Read the entire UsedRange into the array
wbSource.Close SaveChanges:=False
Set wsSource = Nothing
Set wbSource = Nothing
On Error GoTo 0
If Not IsArray(arrFactSalesData) Then
MsgBox “Unable to read data from > factSalesFileName > factSalesFileName”. , vbCritical
GoTo CleanUpAndExit
End If
If UBound(arrFactSalesData, 1) <1Then’ there must be at least a header row
MsgBox factSalesFileName > “Empty or incorrectly formatted.” , vbCritical
GoTo CleanUpAndExit
End If
‘—Determining the column number of the key Key column in FactSales (based on the header name) —
For j = LBound(arrFactSalesData, 2) To UBound(arrFactSalesData, 2)
SelectCaseTrim(CStr(arrFactSalesData(1, j))) ‘ header is on the first row of the array
Case “SPU”: spuColFact = j
Case “Customer ID”: customerIdColFact = j
Case “Region ID”: cityIdColFact = j
End Select
Next j
If spuColFact = 0 Or customerIdColFact = 0 Or cityIdColFact = 0 Then
MsgBox “Error:” > factSalesFileName > one or more key headers (SPU, Customer ID, Region ID) were not found. Please check that the header is correct. ”, vbCritical
GoTo CleanUpAndExit
End If
‘— 4. Loading Dimension Table Data into a Dictionary —
Set dictProducts = CreateObject(“Scripting.Dictionary”)
Set dictCustomers = CreateObject(“Scripting.Dictionary”)
Set dictCities = CreateObject(“Scripting.Dictionary”)
Suppose the Key in the dimension file is in column 1 and Name in column 2. If not, modify the LoadDictionary_V3’s call parameters.
LoadDictionary_V3 dictProducts, folderPath, productDimFileName, 1, 2 ‘ keyCol=1 (SPU), valCol=2 (ProductName)
LoadDictionary_V3 dictCustomers, folderPath, customerDimFileName, 1, 2′ keyCol=1 (Customer ID), valCol=2 (CustomerName)
LoadDictionary_V3 dictCities, folderPath, cityDimFileName, 1, 2 ‘ keyCol=1 (area ID), valCol=2 (CityName)
‘ — 5. Preparing the output array and populating the data —
lastRowFact = UBound(arrFactSalesData, 1)
lastColFact = UBound(arrFactSalesData, 2)
lastColOutput = lastColFact + 3 ‘ Add 3 columns
productNameColOutput = lastColFact +1
customerNameColOutput = lastColFact +2
cityNameColOutput = lastColFact +3
ReDim arrOutputData(LBound(arrFactSalesData, 1) To lastRowFact, LBound(arrFactSalesData, 2) To lastColOutput)
‘ Fill the header row to the output array
For j = LBound(arrFactSalesData, 2) To lastColFact
arrOutputData(LBound(arrFactSalesData, 1), j) = arrFactSalesData(LBound(arrFactSalesData, 1), j)
Next j
arrOutputData(LBound(arrFactSalesData, 1), productNameColOutput) = “ProductName” ‘ or Chinese ‘Product Name’
arrOutputData(LBound(arrFactSalesData, 1), customerNameColOutput) = ‘CustomerName’ or Chinese ‘Customer Name’
arrOutputData(LBound(arrFactSalesData, 1), cityNameColOutput) = ‘CityName’ or Chinese ‘Region Name’
‘ Fill the data rows
For i = LBound(arrFactSalesData, 1) + 1 To lastRowFact ‘ from the second row (data row)
‘ Copy the original data
For j = LBound(arrFactSalesData, 2) To lastColFact
arrOutputData(i, j) = arrFactSalesData(i, j)
Next j
Find and populate the ProductName
tempKey = arrFactSalesData(i, spuColFact)
If Not IsEmpty(tempKey) And dictProducts.Exists(CStr(tempKey)) Then
arrOutputData(i, productNameColOutput) = dictProducts(CStr(tempKey))
Else
arrOutputData(i, productNameColOutput) = “N/A”
End If
Find and populate the CustomerName
tempKey = arrFactSalesData(i, customerIdColFact)
If Not IsEmpty(tempKey) And dictCustomers.Exists(CStr(tempKey)) Then
arrOutputData(i, customerNameColOutput) = dictCustomers(CStr(tempKey))
Else
arrOutputData(i, customerNameColOutput) = “N/A”
End If
‘ Find and populate CityName
tempKey = arrFactSalesData(i, cityIdColFact)
If Not IsEmpty(tempKey) And dictCities.Exists(CStr(tempKey)) Then
arrOutputData(i, cityNameColOutput) = dictCities(CStr(tempKey))
Else
arrOutputData(i, cityNameColOutput) = “N/A”
End If
Next i
‘ — 6. Write an Output Array to the “ConsolidatedSales” Worksheet –
wsConsolidated.Range(“A1”).Resize(UBound(arrOutputData, 1), UBound(arrOutputData, 2)).Value = arrOutputData
wsConsolidated.UsedRange.Columns.AutoFit
CleanUpAndExit:
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
Set wbSource = Nothing
Set wsSource = Nothing
Set wsDim = Nothing
Set wsConsolidated = Nothing
Set dictProducts = Nothing
Set dictCustomers = Nothing
Set dictCities = Nothing
If IsArray(arrFactSalesData) Then Erase arrFactSalesData
If IsArray(arrOutputData) Then Erase arrOutputData
If Err.Number = 0 And folderPath <> “” And spuColFact > 0 And customerIdColFact > 0 And cityIdColFact > 0 Then
MsgBox” data integration completed! Time: ” & Format(Timer – startTime, “0.00”) & ” Seconds”, vbInformation
ElseIf Err.Number <> 0 Then
MsgBox “Error during operation (code: ” & Err.Number & “): ” & Err.Description, vbCritical
End If
Err.Clear
Exit Sub
FileOpenErrorFactSales:
MsgBox “Error when opening file: ” & folderPath & factSalesFileName & vbCrLf > “Error description: ” & Err.Description, vbCritical
Err.Clear
GoTo CleanUpAndExit
End Sub
‘ Auxiliary Function: Loading Dictionary Data from an External File (V3)
Sub LoadDictionary_V3(dict As Object, ByVal filePath As String, ByVal fileName As String, ByVal keyColNum As Long, ByVal valColNum As Long)
Dim wbDim As Workbook
Dim wsDim As Worksheet
Dim arrDim As Variant
Dim r As Long, lastRowDim As Lon
If Len(Dir(filePath & fileName)) =0Then
MsgBox “Dimension File” & fileName > ” not found in the specified folder! The corresponding dictionary will be empty. ”, vbExclamation
Exit Sub
End If
On Error GoTo LoadDictError_V3
Set wbDim = Workbooks.Open(filePath & fileName, ReadOnly:=True, UpdateLinks:=0)
Set wsDim = wbDim.Sheets(1) ‘ always assumes that the data is in the first sheet
lastRowDim = wsDim.Cells(wsDim.Rows.Count, keyColNum).End(xlUp).Row
If lastRowDim > 1 then ‘ have at least columns (except for the header)
Make sure the array range read contains at least keyColNum and valColNum
Dim firstColToRead As Long, lastColToRead As Long
firstColToRead = Application.Min(keyColNum, valColNum)
lastColToRead = Application.Max(keyColNum, valColNum
arrDim = wsDim.Range(wsDim.Cells(2, firstColToRead), wsDim.Cells(lastRowDim, lastColToRead)).Value
Dim actualKeyColInArray As Long, actualValColInArray As Long
actualKeyColInArray = keyColNum – firstColToRead + 1
actualValColInArray = valColNum – firstColToRead + 1
If IsArray(arrDim) Then
For r = LBound(arrDim, 1) To UBound(arrDim, 1)
If Not IsEmpty(arrDim(r, actualKeyColInArray)) Then
If Not dict.Exists(CStr(arrDim(r, actualKeyColInArray))) Then
dict.Add CStr(arrDim(r, actualKeyColInArray)), arrDim(r, actualValColInArray)
End If
End If
Next r
Else’ handles cases where only a single line of data is read and Range returns a non-array
If lastRowDim =2AndNot IsEmpty(arrDim) then ‘ special handling of cases where there is only one row of data
If Not IsEmpty(wsDim.Cells(2,keyColNum).Value) Then
If Not dict.Exists(CStr(wsDim.Cells(2,keyColNum).Value)) Then
dict.Add CStr(wsDim.Cells(2,keyColNum).Value), wsDim.Cells(2,valColNum).Value
End If
End If
End If
End If
End If
wbDim.Close SaveChanges:=False
Set wsDim = Nothing
Set wbDim = Nothing
Exit Sub
LoadDictError_V3:
MsgBox “Failed to open or read file when loading dictionary > filePath & fileName >> fileName.” & vbCrLf & “Error (code: ” & Err.Number & “): ” & Err.Description, vbExclamation
If Not wbDim Is Nothing Then wbDim.Close SaveChanges:=False
Set wsDim = Nothing
Set wbDim = Nothing
Err.Clear
End Sub
Once run, a box will pop up to select folders, let’s select the folders that contain multiple data tables:
After selecting, run, 7s time will help me match multiple columns of the table.
In the process of practice, it is easy to encounter 3 pitfalls: VBA is case-sensitive, for example, in my case, it didn’t work at first, and then I guessed it was this problem, so I had to change it to lowercase (because I was lowercase in the table) On different computers, and even different Office configurations may affect vba, for example, I used VBA on Windows at the beginning, but it can’t be used when I move it to MAC, so sometimes it’s not a code problem, but the environment itself.
Finally, there is the problem of prompts, because it involves business logic or table descriptions, which actually need to be written accurately, and the VBA code given at the end can run correctly.
My solution is to let AI write prompts, including these cases, such a standard description of the action to be done in the prompt is because I asked DeepSeek to give me multiple prompts to choose from, and then I chose the one that matched my expectations to adjust it!
For tables, you can also give graphs directly, but DeepSeek does not support multimodality, you can give Gemini or Claude, which is also a good choice.
