excel vba子程序调用失败

发布于 2024-11-14 09:27:33 字数 1002 浏览 6 评论 0原文

我有以下问题。我想调用一个 soroutine 来更改单元格范围的背景颜色。使用cells(1,1)计算单元格范围，然后计算地址以接收A1。

在调用子例程之前，我会像这样获得单元格的地址：

Range1 = cells(4, 4).Address(RowAbsolute:=False, ColumnAbsolute:=False)
Range2 = cells(4, CellAmount - 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)

我想我需要这个，因为子例程是这样声明的：

Sub SetBGLightGrey(cells As String)

    range(cells).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15921906
        .TintAndShade = 0
       .PatternTintAndShade = 0
    End With

End Sub

Range 1 和 Range 2 是字符串，我将它连接到范围声明：

RangeArea = """" & Range1 & ":" & Range2 & """"

当我像这样调用我的子例程时这：

Call SetBGLightGrey(RangeArea)

我收到以下错误消息：

“运行时错误'1004'：对象'_Global'的方法'范围'失败。我不明白它，因为如果我使用正确的子例程调用单元格值：

Call SetBGLightGrey("D4:K4")

它有效。它是字符串并且具有相同的值，这不可能吗？

原文

I have the following problem. I want to call a soubroutine for changing the background color for a cell range. The cell range is calculated with cells(1,1) and then the address is calculated to receive A1.

Before the subroutine is called I get the addresses for my cells like this:

Range1 = cells(4, 4).Address(RowAbsolute:=False, ColumnAbsolute:=False)
Range2 = cells(4, CellAmount - 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)

I thought I need this because the subroutine is declared like this:

Sub SetBGLightGrey(cells As String)

    range(cells).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15921906
        .TintAndShade = 0
       .PatternTintAndShade = 0
    End With

End Sub

Range 1 and Range 2 are strings and I concat it to a range declaration:

RangeArea = """" & Range1 & ":" & Range2 & """"

When I call my subroutine like this:

Call SetBGLightGrey(RangeArea)

I get the following error-message:

"Run-time error '1004': Method 'Range' of object '_Global' failed. I don't understand it because if I call the subroutine with the correct cell values:

Call SetBGLightGrey("D4:K4")

it works. It is string and of the same value. This simply cannot be can it?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

半﹌身腐败 2024-11-21 09:27:33

您不需要在 RangeArea 周围加上引号。

RangeArea = Range1 & ":" & Range2

但是，为什么您要将范围作为字符串传递，然后将它们转换回范围呢？始终传递范围对象。

Sub SetBGLightGrey(byval cells as range)
  With cells.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 15921906
    .TintAndShade = 0
    .PatternTintAndShade = 0
  End With
End Sub 

SetBGLightGrey range(cells(4, 4), cells(4, CellAmount - 1))

You do not need quotes around RangeArea.

RangeArea = Range1 & ":" & Range2

But then, why would you want to pass ranges around as strings and then convert them back to ranges? Pass the range objects all the time.

Sub SetBGLightGrey(byval cells as range)
  With cells.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 15921906
    .TintAndShade = 0
    .PatternTintAndShade = 0
  End With
End Sub 

SetBGLightGrey range(cells(4, 4), cells(4, CellAmount - 1))

回复收藏 0 原文

~没有更多了~