将变体一起添加到VBA中的问题 [英] Problems with adding variants together VBA

问题描述

我对VBA编程以及不同数据类型的工作方式还很陌生，所以我遇到了一个问题.我在输入框中添加了两个不同的数字( rng 和 rng1 ).如果用户按下取消"，程序将关闭并锁定纸张.如果我使用例如一个整数而不是一个变体，我无法运行关闭代码.但是，当我尝试执行 rng + rng1 时，它不会添加它们，而是将它们粘合在一起，即如果 grp = 2 和 grp1 = 3 然后 grp + grp1 = 23 .这弄乱了我的情节功能.因此，我希望有人可以帮助我找出要使用的正确数据类型?或针对该问题的另一种解决方案.该代码可以在下面看到.

I am fairly new to VBA programming and how the different data types work, so I am running into a problem. I am adding two different numbers (rng and rng1) in an inputbox. If the user presses cancel the program closes down and the sheet locks. If I use e.g. an integer instead of a variant I can't run the close down code. However, when I try do rng + rng1 it does not add them, but instead glues them together, i.e. if grp = 2 and grp1 = 3 then grp + grp1 = 23. This messes with my plot function. So I am hoping that someone can help me figure out the correct data types to use? Or a different solution to the problem. The code can be seen below.

dim grp As variant
dim grp1 As variant

Application.DisplayAlerts = False
On Error Resume Next
grp = InputBox("Enter No in Group 1")
On Error GoTo 0
Application.DisplayAlerts = True

If grp = "" Then
    MsgBox ("User canceled!")
    ActiveSheet.Protect Password:="..."
    Exit Sub
End If

Application.DisplayAlerts = False
On Error Resume Next
grp1 = InputBox("Enter No in Group 2")
On Error GoTo 0
Application.DisplayAlerts = True

If grp1 = "" Then
    MsgBox ("User canceled!")
    ActiveSheet.Protect Password:="..."
    Exit Sub
End If

ActiveSheet.ChartObjects("Chart1").Activate

With ActiveChart
    I = 3
    Do Until I = grp + 3
        ActiveChart.FullSeriesCollection(I).Select
        ActiveChart.SeriesCollection(I).Select
        With Selection
            .Border.LineStyle = xlContinuous
            .Border.Color = RGB(0, 255, 0)
            .MarkerBackgroundColor = RGB(0, 255, 0)
            .MarkerForegroundColor = RGB(0, 255, 0)
        End With
        I = I + 1
    Loop

    j = grp + 3
    Do Until j = grp + grp1 + 3
        ActiveChart.SeriesCollection(j).Select
        With Selection
            .Border.LineStyle = xlContinuous
            .Border.Color = RGB(0, 0, 255)
            .MarkerBackgroundColor = RGB(0, 0, 255)
            .MarkerForegroundColor = RGB(0, 0, 255)
        End With
        j = j + 1
    Loop

推荐答案

它不会添加它们，而是将它们粘合在一起，即如果 grp = 2 和 grp1 = 3 ，则 grp + grp1 = 23

InputBox 返回 String 类型.很多人没有意识到的是，您可以使用 & 或 + 运算符来组合字符串，这就是您正在做:

InputBox returns a String type. What a lot of people don't realize is that you can use either the & or the + operator to combine strings, and that's what you're doing:

"2" + "3" = "23" '// equivalent to "2" & "3"

位置:

2 + 3 = 5

因此，由于您的参数的类型为 String ，因此 + 运算符假定您正在尝试将它们组合在一起，因此不会隐式地将类型转换为 Int 或 Long 或 Double ，因为该运算符对于组合字符串是完全有效的，这就是您给的:)

So because your parameters are of type String, the + operator assumes that you're trying to combine them, there's no implicit type conversion to Int or Long or Double, because the operator is perfectly valid for combining strings, which is what you gave it :)

注意: 通常建议仅 使用& 运算符，这样可以减少歧义您正在组合字符串，而不是添加长/整数值.

NOTE: It's usually recommended to only use the & operator, that way it's less ambiguous that you're combining strings, versus adding long/integer values.

要将结果输入处理为数字类型 (即执行加法或其他算术运算)，则需要使用数字数据( Integer/Long/Double 类型)而不是 String 类型.您可以进行明确的类型转换，例如:

To handle the resulting input as a numeric type (i.e., to perform addition or other arithmetic operation), then you need to be working with numeric data (Integer/Long/Double type) instead of String type. You could do an explicit typecast like:

Dim grp as Long
grp = CLng(InputBox("Enter No in Group 1"))

或更优选地，使用 InputBox 函数的 Type 参数:

Or, more preferably, use the Type argument of the InputBox function:

Dim grp as Long
grp = InputBox("Enter No in Group 1", Type:=1)

与 grp2 相同.

这篇关于将变体一起添加到VBA中的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！