为什么我在这个VBA子程序中不断收到错误424?

huangapple go评论108阅读模式
英文:

Why Do I Keep Getting Error 424 for This VBA Sub?

问题

I am trying to create a Sub that takes 3 inputs. The first two inputs have no issues, but the third does not work. I get Error 424 every time I try to call the function, which states that an object is required and highlights the line shown below with asterisks.

Getting this function to work would save a significant amount of time and lines of code since the worksheet will contain a lot of checkboxes. The overall idea is that when the user checks the box and the first input cell number reads "FAIL", the second input cell will have its value changed to contain the username and the date at which it was checked. I am sure that this is a basic fix, but how can I go about fixing it?

Sub Approval(PF, Val, BoxNum)
    If Range(PF).Value = "FAIL" Then
      If BoxNum.Value = True Then
        User = Application.UserName
        Dim Today
        Today = Date
        Range(Val).Value = User & "   " & Today
      Else
        Range(Val).Value = ""
      End If
    Else
        BoxNum.Value = False      **********
        Range(Val).Value = ""
        Exit Sub
    End If
End Sub
Private Sub CheckBox1_Click()
    Call Approval("O28", "R28", "CheckBox1")
End Sub

I tried looking at the Microsoft VBA guide as well as other posts on SO for related content, but none of them seemed like they applied to this. It is also worth mentioning that the code did work before I attempted to turn it into a sub that is called.

英文:

I am trying to create a Sub that takes 3 inputs. The first two inputs have no issues, but the third does not work. I get Error 424 every time I try to call the function, which states that an object is required and highlights the line shown below with asterisks.

Getting this function to work would save a significant amount of time and lines of code since the worksheet will contain a lot of checkboxes. The overall idea is that when the user checks the box and the first input cell number reads "FAIL", the second input cell will have its value changed to contain the username and the date at which it was checked. I am sure that this is a basic fix, but how can I go about fixing it?

Sub Approval(PF, Val, BoxNum)
    If Range(PF).Value = "FAIL" Then
      If BoxNum.Value = True Then
        User = Application.UserName
        Dim Today
        Today = Date
        Range(Val).Value = User & "   " & Today
      Else
        Range(Val).Value = ""
      End If
    Else
        BoxNum.Value = False      **********
        Range(Val).Value = ""
        Exit Sub
    End If
End Sub
Private Sub CheckBox1_Click()
    Call Approval("O28", "R28", "CheckBox1")
End Sub

I tried looking at the Microsoft VBA guide as well as other posts on SO for related content, but none of them seemed like they applied to this. It is also worth mentioning that the code did work before I attempted to turn it into a sub that is called.

答案1

得分: 1

Call Approval("O28", "R28", "CheckBox1") 传递一个字符串 "CheckBox1" 给子程序。您可能想要传递复选框对象。

尝试 Call Approval("O28", "R28", CheckBox1) - 注意 CheckBox1 周围没有引号。不过,如果不看到您的其余代码,我无法确定这是否会起作用。

这也是为什么最佳做法是指定参数类型的良好示例,像这样:

Sub Approval(PF as Range, Val as String, BoxNum as Object)

这将有助于更早地发现问题,并更容易识别问题。

英文:

Call Approval("O28", "R28", "CheckBox1") passes a string "CheckBox1" to the sub. You probably want to pass the checkbox object.

Try Call Approval("O28", "R28", CheckBox1) - notice no quotes around CheckBox1. Although without seeing the rest of your code, I can't say if this will work.

This is also a good example why it is best practice to specify the type of your arguments like:

Sub Approval(PF as Range, Val as String, BoxNum as Object)

This would help catch the problem earlier and make it easier to identify the issue.

答案2

得分: 0

根据您的代码外观,我认为Kevin的答案是正确的。为了稍微扩展Kevin的回答,我强烈建议您在每个模块的顶部始终加入以下代码行:

Option Explicit

这将强制您声明每个变量的类型,正如Kevin所说,这是最佳实践。作为一个很大的附加好处,它还将检测到可能拼写错误的任何变量,这可能会在您找到拼写错误之前在您的代码中引起各种问题;-)

英文:

From the looks of your code I think Kevin's answer is the correct one. To expand slightly on Kevin's answer, I would highly recommend that you always have the following line of code at the top of every module:

Option Explicit

This will force you to declare every variable type and as Kevin said, it is best practice. As a big side benefit, it will also detect any variables that may have been spelled incorrectly which can cause no end of issues within your code until you find the spelling error 为什么我在这个VBA子程序中不断收到错误424?

huangapple
  • 本文由 发表于 2023年6月13日 08:32:29
  • 转载请务必保留本文链接:https://go.coder-hub.com/76461041.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定