pctechguide.com

  • Home
  • Guides
  • Tutorials
  • Articles
  • Reviews
  • Glossary
  • Contact

Dealing with Excel VBA Macros

In this tutorial we will provide an overview of the topic of VBA Functions and User-defined Functions (UDF). We will mention the practices in approaching macros without arguments, functions with one argument and functions with two arguments. We will see some examples of functions with each of these scenarios to help you understand how to work with them.

The example that contains two arguments, one of them will be optional. In case of optional arguments, I will show you how to evaluate whether the argument is entered or not, using the VBA.IsMissing function.

VBA Functions


Let’s review some topics on using functions in VBA for Excel. First, we need to provide a definition of a function.

A function is a procedure that will take arguments and return a value or an array of values.
Functions can be called from procedures or from cells.
There are functions without arguments such as TODAY or NOW.
Public functions are available to all procedures in the file and for use in cells.
Private functions are only available in procedures of the same module.
Custom functions UDF (User Defined Function)
A custom function can be public or private. If it is public we can invoke it from a cell in Excel, but if it is private, it can only be called from procedures.

These functions can be found in the Insert Function dialog box, in the User Defined category and we can have a graphical interface to insert the arguments.

When we enter the equals sign “=” in a cell, we will be able to visualize the UDF, as long as the file or add-in that contains them is open. Excel has more than 450 functions, plus the ones you develop.

Function without arguments
I share with you 3 examples of UDF functions where we do not require arguments to return a result. In the first one we return the name of the active sheet, in the next one the Excel version and in the last one we show the Excel user, which you can find in the Excel Options.

Option Explicit

Function SheetName()
Application.Volatile

SheetName = ActiveSheet.Name

End Function

Function Version()
Application.Volatile

Version = Application.Version

End Function

Function User()
Application.Volatile

User = Application.UserName

End Function
Function with one argument
In this function value we are going to have as argument the Sales value and depending on the quantity, we are going to return a discount. We will use the If-Then-Else statement to evaluate the quantities and MsgBox to show a well elaborated message.

Option Explicit

Function Discount(Sales)
Application.Volatile

If Sales < 10 Then
Discount = 0
ElseIf Sales < 20 Then
Discount = 0.1
Else
Discount = 0.2
End If

End Function

Sub CalculateDiscount()
Dim SalesValue As Integer
Dim Message As String

SalesValue = InputBox(“Enter sales”, “Sales”)

If SalesValue = 0 Then Exit Sub

Message = “Sales are: ” & vbTab & SalesValue
Message = Message & vbNewLine & “The discount is:” & vbTab & SalesValue
Message = Message & vbTab & VBA.Format(Discount(SalesValue), “0%”)

MsgBox Message, vbInformation, “EXCELeINFO”

End Sub
Function with two arguments. One optional
The following function will be valid only for use in an Excel cell. We will achieve that if the user enters the value 1, the entered text will be converted to UPPERCASE, if we enter 2, to lowercase, and if the Type parameter is not entered, the text will be returned as is. The Type parameter is optional, so we will evaluate with VBA.IsMissing if the parameter is entered or not.

Option Explicit

Function CText(Text As String, Optional Type As Variant)

If VBA.IsMissing(Type) Then
Type = 0
CText = Text
Else
Select Case Type
Case 1
CText = VBA.UCase(Text)
Case 2
CText = VBA.LCase(Text)
Case Else
CText = VBA.CVErr(xlErrValue)
End Select
End If

End Function

Filed Under: Articles

Latest Articles

How to Remove Win 7 Security 2012

Easiest Removal Option: Remote Repair: Paying an expert to remove this threat is always the easiest option. It's also the most expensive. PCTechGuide.com has reviewed several remote computer repair companies and we recommend http://www.pcninja.com This is a small computer repair company (Two … [Read More...]

Tips to Getting Engagement that Works the Instagram Algorithm in Your Favor

Building a formidable presence on Instagram these days requires much planning and patience. With over a billion active users, the site is rocketing in popularity for both personal and professional networking purposes. The frequent upgrades in recent times have made Instagram a very versatile space … [Read More...]

Core i5 3rd Gen

After much waiting and anticipation, Intel finally unveiled the Core i5 3rd Gen processors in April 2012. Based on the Ivy Bridge microarchitecture and the 22nm manufacturing process, these processors offer a huge bundle of exciting enhancements:  improved graphics, more power efficient, wireless … [Read More...]

Why Internet Bundles Are the Next Battleground for Gamers

A weak internet connection can turn seamless gaming sessions into frustrating experiences filled with lag, buffering or sudden disconnects. For … [Read More...]

Importance of Inbound Marketing in the Digital Age

A couple of months ago, Zacks reported that Hubspot was starting to make some major changes to its inbound marketing strategy. They talk a lot about … [Read More...]

Damage Control Strategies for Resolving Online PR Crises

Last July, Astrologer faced a major crisis after its CEO went viral at a ColdPlay concert when having an affair. This was just one of the many times a … [Read More...]

AI is Not Killing Computer Jobs Like Doomers Projected

There is no denying the reality that AI technology has played a massive role in disrupting our lives. A growing number of people claim that AI … [Read More...]

Everything You Need to Know About Sourcing Circuit Boards From U.S. Suppliers

In This Article This article includes: Why Source PCBs From the United States?How to Get a Quote From a U.S.-Based PCB ManufacturerThe Top U.S. … [Read More...]

Top Taplio Alternatives in 2025 : Why MagicPost Leads for LinkedIn Posting ?

LinkedIn has become a strong platform for professionals, creators, and businesses to establish authority, grow networks, and elicit engagement. Simple … [Read More...]

Guides

  • Computer Communications
  • Mobile Computing
  • PC Components
  • PC Data Storage
  • PC Input-Output
  • PC Multimedia
  • Processors (CPUs)

Recent Posts

Motivating Students to Learn Computer Science

Computer science has been an important part of the educational curriculum for decades. Professor Seymour Papert, a mathematician from South Africa … [Read More...]

Protect Your Business with Online Reputation Management

As a company grows the potential for negative comments online become more common. This type of activity needs to be monitored for potential issues.  … [Read More...]

Other Digital Video Codecs

There were a number of other popular codes, all supported by Microsoft's VfW, still in use by the late 1990s: Microsoft … [Read More...]

[footer_backtotop]

Copyright © 2026 About | Privacy | Contact Information | Wrtie For Us | Disclaimer | Copyright License | Authors