CODEWITHSUNDEEP
excelvba
Welisson Goldmann
Welisson Goldmann

Reputation: 107

VBA - Call function inside a sub (module)

I have the following function in VBA:

Public Function lorh(custo As Integer)
If custo > 10.99 And custo <> 0 Then
    lorh = "1.4"
Else
    If custo < 11 And custo <> 0 Then
        lorh = "1.35"
    Else
        If custo <= 0 Or custo < 0 Then
            lorh = "Valor Inválido"
        End If
    End If
End If
End Function

Now what I need is to call this function starting from a sub or better from a macro, so I can associate it to a custom button on the excel toolbar . Can anyone guide me?

Upvotes: 1

Views: 69732

Answers (2)

andrux
andrux

Reputation: 2922

Actually, if you have that function inside of a module, you can directly reference it inside a worksheet cell, pretty much as you do with Excel's formulas.

=lorh(A1)

In order for your code to run from a macro button, it needs to be a Sub instead of a Function

I think the code below would work the way you want it, I removed redundant pieces as well as Barranka did.

Public Sub lorh()
    Dim lorh As String
    custo = ActiveCell.Value

    If custo > 10.99 Then
        lorh = "1.4"
    Else
        If custo > 0 Then
            lorh = "1.35"
        Else
            lorh = "Valor Inválido"
        End If
    End If

    ActiveCell.Value = lorh
End Sub

This macro would use the active cell value the same way you were using the custo parameter in your function.

Upvotes: 3

Barranka
Barranka

Reputation: 21057

If you need to use your function in your excel sheet, you only need to write it at any cell, just as andrux said.

If you need to call it from a sub, again, you only need to write it:

public sub aSubprocedure()
    ' Any variables and other instructions go here
    var = lorh(input)
    ' Your code goes on
end sub

Then you can assign your subprocedure to your button.

A few suggestions for your code:

I suggest the following 'clean up' for your function:

Public Function lorh(custo As Integer)
    If custo > 10.99 And custo <> 0 Then
        lorh = "1.4"
    Else If custo < 11 And custo <> 0 Then
        lorh = "1.35"
    Else If custo <= 0 Then
        lorh = "Valor Inválido"
    End If
End Function

Notice that if custo <= 0 or custo < 0 is redundant... you only need custo<=0.

Hope this helps you

Upvotes: 1

Related Questions