VBA function not being executed

Question

I'm trying to create a function that looks for a value in a given range of cells, but it is not being executed. It should return the cell where the value is found.

Function searchInRange(where As Range, what As String) As Range
For Each c In where.Cells
 Debug.Print (c.Value)
    If c.Value = what Then
        searchInRange = c
    End If
Next c

End Function

I'm calling it from a submodule, but it is not executed. If I go to debug the debugger just jumps to the next statement without entering in the function. I don't understand why

EDIT

As many people suggested, I'm posting the module that calls the functions. It is a button click function. The file and sheet are correctly selected. I know this because the last sentence (PE_Sheet.activate) works correctly. Regards.

Private Sub CommandButton1_Click()
Dim PE_File As Workbook
Dim PE_Sheet As Worksheet
Dim cell As Range


Set PE_File = Workbooks(getSelectedWorkbook())
Set PE_Sheet = PE_File.Worksheets("Monitored")
Unload UserForm1

searchAlarmFilter PE_Sheet.Range("A:A"), "5184"

PE_Sheet.Activate
End Sub

If the function is skipped on the parent sub/function, you should be looking at the parent sub/function instead of the above. As @TheEngineer suggested, post the section that calls the above function — Alex
– Alex, Commented Dec 17, 2014 at 17:07
not that this is the issue, but you might want to exit for as you risk overwriting earlier results — NickSlash
– NickSlash, Commented Dec 17, 2014 at 17:16

NickSlash · Accepted Answer · 2014-12-17 17:20:41Z

3

You also need to set ranges

Public Function sir(where As Range, what As String) As Range
Dim res As Range: Set res = Nothing
For Each c In where.Cells
    Debug.Print c.Value
    If c.Value = what Then
        Set res = c
        Exit For
    End If
Next
Set sir = res
End Function

answered Dec 17, 2014 at 17:20

NickSlash

5,1954 gold badges25 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Danielo515 · Accepted Answer · 2014-12-19 08:49:33Z

1

Since NickSlash was right and the function should look like he pointed, this is just part of the problem. I can't understand jet why, but unloading the form before calling the function makes the interpreter not executing the function. Seems that all the variables that depends in any way of the form are unloaded with the form. This does not makes much sense to me, but it works this way.

So the proper way of calling the function would be:

Private Sub CommandButton1_Click()
Dim PE_File As Workbook
Dim PE_Sheet As Worksheet
Dim cell As Range


Set PE_File = Workbooks(getSelectedWorkbook())
Set PE_Sheet = PE_File.Worksheets("Monitored")
searchAlarmFilter PE_Sheet.Range("A1:A500"), "5184"
Unload UserForm1



PE_Sheet.Activate
End Sub

For a correct version of the function see NickSlash's answer.

answered Dec 19, 2014 at 8:49

Danielo515

7,3016 gold badges46 silver badges84 bronze badges

4 Comments

NickSlash Over a year ago

If your code is in the userform module then calling unload might effectively be exit subing. Try hiding the form, activating your sheet then unloading the form

NickSlash Over a year ago

Had a quick test and unloading the form in the middle of the code block didn't stop execution of the statements after it. Still, probably best not to unload the form before you're done.

Danielo515 Over a year ago

But the function that I'm calling is also inside the form "block of code", so maybe that is the problem. I don't know if I move the function to the main module it could work.

NickSlash Over a year ago

If your function is not explicitly tied to the user form having it in a module is usually a good plan. Does your form get used frequently? Might try just hiding it instead of unloading it.

Collectives™ on Stack Overflow

VBA function not being executed

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related