请求提供从Excel VBA迁移至VB.NET的VSTO Excel文档定制示例

Hey there! I totally get where you're coming from—moving from VBA's built-in editor to VSTO document-level projects in Visual Studio can feel a bit tricky at first, especially since most online resources focus on add-ins instead of workbook-specific customizations. Let’s break this down with concrete, practical examples tailored to your needs.

First: Create a Document-Level Project #

Before diving into code, let’s make sure you set up the right project type in Visual Studio:

• Open Visual Studio and create a new project
• Search for "Excel VSTO Document-Level Project" (ensure you have the Office Developer Tools installed)
• Choose whether to tie the project to an existing Excel workbook or create a new one—this will be your "bound" workbook where the VB.NET code lives

Core VBA → VB.NET Examples #

Below are common VBA tasks you might have, converted to VB.NET for a document-level project.

1. Cell Manipulation & Formatting #

VBA Version

' VBA: Write to a cell and apply formatting
Sub WriteAndFormatCell()
    ThisWorkbook.Sheets("Sheet1").Range("A1").Value = "Hello from VBA"
    ThisWorkbook.Sheets("Sheet1").Range("A1").Font.Bold = True
    ThisWorkbook.Sheets("Sheet1").Range("A1").Font.Size = 14
End Sub

VB.NET Document-Level Version

Open the Sheet1.vb file (auto-generated for your bound worksheet) and add this code:

' VB.NET: Write to a cell and apply formatting (worksheet-specific code)
Public Sub WriteAndFormatCell()
    ' In document-level projects, "Me" refers directly to the bound Sheet1
    Me.Range("A1").Value2 = "Hello from VB.NET (Document-Level)"
    Me.Range("A1").Font.Bold = True
    Me.Range("A1").Font.Size = 14
End Sub

Note: Use Value2 instead of Value in VB.NET to avoid automatic type conversion issues with dates/currency.

2. Worksheet & Workbook Events #

Document-level projects automatically wire up events for your bound workbook/worksheets—no need to manually assign them like in VBA.

Worksheet Activate Event

VBA Version

' VBA: In Sheet1's code module
Private Sub Worksheet_Activate()
    MsgBox "Sheet1 activated!"
End Sub

VB.NET Version

Add this directly to Sheet1.vb:

' VB.NET: Worksheet activate event (auto-bound via Handles keyword)
Private Sub Sheet1_Activate(sender As Object, e As EventArgs) Handles Me.Activate
    MessageBox.Show("Sheet1 activated!", "VSTO Document-Level")
End Sub

Workbook Startup/Shutdown Events

VBA Version

' VBA: In ThisWorkbook's code module
Private Sub Workbook_Open()
    MsgBox "Workbook opened!"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    MsgBox "Workbook closing..."
End Sub

VB.NET Version

Open ThisWorkbook.vb and add:

' VB.NET: Workbook startup event (triggers when the bound workbook opens)
Private Sub ThisWorkbook_Startup(sender As Object, e As EventArgs) Handles Me.Startup
    MessageBox.Show("Workbook loaded successfully!", "VSTO Startup")
    ' Call the worksheet method we created earlier
    Me.Sheets("Sheet1").WriteAndFormatCell()
End Sub

' VB.NET: Workbook shutdown event (triggers when the bound workbook closes)
Private Sub ThisWorkbook_Shutdown(sender As Object, e As EventArgs) Handles Me.Shutdown
    MessageBox.Show("Workbook closing...", "VSTO Shutdown")
End Sub

3. Custom Functions (UDFs) #

Unlike VBA UDFs, document-level projects let you create UDFs scoped to your bound workbook. Here’s how:

VB.NET Version

Add this to Sheet1.vb or ThisWorkbook.vb:

' VB.NET: Custom worksheet function (only available in the bound workbook)
<Microsoft.Office.Tools.Excel.NamedRange("AddNumbers")>
Public Function AddNumbers(num1 As Double, num2 As Double) As Double
    Return num1 + num2
End Function

You can now use =AddNumbers(A1,B1) directly in your bound workbook’s cells.

Key Differences to Note #

• Binding: Document-level code is tied directly to your workbook—when you distribute the workbook, the code travels with it (you’ll need to deploy it as a VSTO package or use ClickOnce).
• IntelliSense: Visual Studio’s IntelliSense is far more robust than VBA’s editor, making it easier to navigate Excel’s object model.
• TFS Integration: Since this is a standard Visual Studio project, you can connect it to TFS just like any other .NET project:
  • Go to Team Explorer → Connect to your TFS server
  • Add the project to source control, check in changes, and collaborate with your team seamlessly

Final Tips #

• Use Globals.ThisWorkbook or Globals.Sheet1 to access your bound workbook/worksheets from other modules if needed.
• Test your code by pressing F5 in Visual Studio—this will launch Excel with your bound workbook and run your VSTO code.
• For more complex logic, you can add additional class modules to your project just like in VBA, but with the full power of .NET libraries.

内容的提问来源于stack exchange，提问作者GED125