VBA - Call function inside a sub (module)

Question

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?

andrux · Accepted Answer · 2012-10-18 21:33:58Z

3

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.

edited Oct 18, 2012 at 21:33

answered Oct 18, 2012 at 20:36

andrux

2,9323 gold badges26 silver badges31 bronze badges

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

2 Comments

Welisson Goldmann Over a year ago

okay you're right, however, and what I want to do something like the "AutoSum" that when you click the button it performs the function sum

andrux Over a year ago

If you want a button or macro to work like the autosum feature, you would need to reference the active cell and then act upon it on your function, which should be a sub instead... Take a look at my edited answer above.

Barranka · Accepted Answer · 2012-10-18 20:53:14Z

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

Collectives™ on Stack Overflow

VBA - Call function inside a sub (module)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related