Hello. It is necessary to write a macro on VBA for checking values, but, alas, I don’t understand the essence of this language at all. I write a lot on Python, Javascript and some Java and C ++. I have seen a lot of situations, but the errors of the BWA simply put into a stupor ... :) Actually the code

Private Sub Worksheet_Change(ByVal Target As Range) BasicRequirements (ActiveCell) End Sub Sub BasicRequirements(currentCell) If Cells(currentCell.row, currentCell.Column - 1).Value = "Александр" Then Debug.Print "Yes" Else Debug.Print "No" End If End Sub

When performing, I get an error with the requirement of some object ... What? Where should he be?

    2 answers 2

    Most likely the problem with calling Cells(currentCell.row, currentCell.Column - 1) when currentCell.Column is 0 .

    Try adding a check for boundary values.

    Upd.

    In fact, the main problem with the wrong call. It should be like this:

     Private Sub Worksheet_Change(ByVal Target As Range) BasicRequirements ActiveCell End Sub Sub BasicRequirements(currentCell) If Cells(currentCell.Row, currentCell.Column - 1).Value = "Александр" Then Debug.Print "Yes" Else Debug.Print "No" End If End Sub

    Check that currentCell.Column greater than zero is also needed.

    • Alas, even if you leave only Debug.Print currentCell.row in the function body, the result is the same. - Skotinin
    • one
      @Skotinin, I found the problem and updated the answer. Parentheses are not needed when calling BasicRequirements . I love VBA. - Uladzimir Palekh
    • I also came to this a couple of seconds ago. :) Now google when they are needed and when not. - Skotinin
    • one
      @Skotinin if not yet google. Parentheses are needed whenever a variable is assigned a function value, i.e. s = BasicRequirements(ActiveCell) , or if the return value is not used, but Call is used to call the function, i.e. Call BasicRequirements(ActiveCell) - Edward Izmalkov

    In your default variant ActiveCell = ActiveCell.Value . You pass cell value to procedure

    Change:

     Private Sub Worksheet_Change(ByVal Target As Range) BasicRequirements (ActiveCell.Offset(, -1).Value) End Sub Sub BasicRequirements(s As String) If s = "Саня" Then Debug.Print "Yes" Else Debug.Print "No" End If End Sub

    If you pass the cell as an object:

     Private Sub Worksheet_Change(ByVal Target As Range) BasicRequirements ActiveCell End Sub Sub BasicRequirements(r As Range) If r.Column = 1 Then Exit Sub If r.Offset(, -1).Value = "Саня" Then '.....' End If End Sub

    Feel free to put at the beginning of the Option Explicit module and declare variables. Helps to avoid many mistakes.

    • I used the option with Call , but the further solution of the problem showed that "The deeper into the forest, the thicker the guerrillas" and decided to delete all the macros and do it on formulas, lists and conditional formatting, otherwise I will not have time in time. And then all this business will have to be supported :) - Skotinin