时间戳

首页 > 解决方案 > 将当前选定的单元格设置为要处理的范围

excel - 将当前选定的单元格设置为要处理的范围

问题描述

我正在使用 Visual Studio 构建一个 Excel 插件并将我的 Excel VBA 代码转换为 VB.Net 代码。

我无法弄清楚如何通过在 Excel 中选择的单元格上的插件运行宏。

此代码在单元格 A1 上执行。如何将其更改为适用于所有选定的单元格？

Imports Microsoft.Office.Tools.Ribbon
Imports Microsoft.Office.Interop.Excel

Public Class Ribbon1

    Private Sub Ribbon1_Load(ByVal sender As System.Object, ByVal e As RibbonUIEventArgs) Handles MyBase.Load

    End Sub

    Private Sub BtnFontToggle_Click(sender As Object, e As RibbonControlEventArgs) Handles BtnFontToggle.Click

            Dim ActiveWorksheet As Microsoft.Office.Interop.Excel.Worksheet =
            Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets(1)

            Dim Worksheet As Microsoft.Office.Tools.Excel.Worksheet =
            Globals.Factory.GetVstoObject(ActiveWorksheet)

        Dim CurrentColor
        Dim Selection As Excel.Range = Worksheet.Range("A1")

'******* Instead of selecting cell A1, I want the code to select all active cells (those that are currently highlighted)*****

        'What is the current font color?
        CurrentColor = Selection.Font.ColorIndex

        'Change font color based on current font color
        'Order Black, Blue, Green, Red
        If CurrentColor = 1 Then
            Selection.Font.ColorIndex = 5
        Else
            If CurrentColor = 5 Then
                Selection.Font.ColorIndex = 10
            Else
                If CurrentColor = 10 Then
                    Selection.Font.ColorIndex = 3
                Else
                    If CurrentColor = 3 Then
                        Selection.Font.ColorIndex = 1
                    Else
                        Selection.Font.ColorIndex = 1
                    End If
                End If
            End If
        End If
    End Sub
End Class

​

标签： excelvb.netvisual-studio

解决方案

您应该能够使用Application.Selection 属性来确定当前是否选择了 Excel.Range，如果是，则检索它。请注意，当前选择可能是其他对象，例如 ChartArea。Selection 属性返回 type Object，因此用于TryCast尝试转换为Excel.Range. 如果转换失败，则TryCast返回Nothing。

Dim Selection As Excel.Range = TryCast(Globals.ThisAddIn.Application.Selection, Excel.Range)
If Selection IsNot Nothing Then
   ' you retrieved a Range object
End If

推荐阅读