CODEWITHSUNDEEP
excelvba
Ham Sandwich
Ham Sandwich

Reputation: 103

VBA program not "showing up" on View Macros

I am trying to learn the basics of VBA coding. I copied this code word for word from an Excel course, other than changing the tax rate:

Option Explicit

Public Function NettoGross(Net As Double)
Dim TaxRate As Double
    TaxRate = 0.01
    NettoGross = Net * (1 + TaxRate)
End Function

Yet, when I go to Excel and view macros, it's nowhere to be found. Please help. Thanks.

Upvotes: 0

Views: 1199

Answers (2)

TechnoDabbler
TechnoDabbler

Reputation: 1265

A function won't appear in the View Macros ... only subroutines. As a function is meant to return a value ... so it is used in cells and other places where a standard Excel function would be used.

For example ... go to cell A1 and enter =NettoGross(1).

Upvotes: 2

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

Public keyword defines the scope of Function and Sub routine. Macros dialog shows only Sub routines which are either having Public or no scope definition prefix before them. Applying Private makes it invisible in the macros dialog.

Notably, if there are arguments being passed to a Public Sub then that also makes it invisible. e.g. Public Sub CalculateGross(Net as Double) will not be visible through macros dialog.

If you intend to use a Public Function (which is also called User Defined Function or UDF) then it can be called like sheet function if it is inserted in a module and not in any Worksheet or ThisWorkbook module. You can write it like a formula e.g.

=NettoGross(A1)

where cell A1 holds net value to be passed.

Upvotes: 1

Related Questions