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

Samsung Galaxy S5 – A Quick Glance

The Samsung Galaxy S5 was officially released in the market in February 2014. It is one of the most recognized Asian phones right now as it becomes comparable with the best mobile phones in the world in terms of usability and user-friendly interface. If you are familiar with the latest software on … [Read More...]

SCSI Internal Connections

The SCSI devices inside a computer are attached to an internal SCSI cable. The last device on the cable must be terminated. Most internal SCSI devices have a jumper or switch that controls termination. Refer to the device’s documentation on enabling or disabling termination. Find the … [Read More...]

8 Principles of ISO 9001 Requirements

We previously wrote an article on the importance of quality control in software development. There are a number of principles to consider. One of the best sets of guidelines is outlined under the ISO 9001 standards. What is ISO 9001? You can find the following description helpful. "The ISO … [Read More...]

Gaming Laptop Security Guide: Protecting Your High-End Hardware Investment in 2025

Since Jacob took over PC Tech Guide, we’ve looked at how tech intersects with personal well-being and digital safety. Gaming laptops are now … [Read More...]

20 Cool Creative Commons Photographs About the Future of AI

AI technology is starting to have a huge impact on our lives. The market value for AI is estimated to have been worth $279.22 billion in 2024 and it … [Read More...]

13 Impressive Stats on the Future of AI

AI technology is starting to become much more important in our everyday lives. Many businesses are using it as well. While he has created a lot of … [Read More...]

Graphic Designers on Reddit Share their Views of AI

There are clearly a lot of positive things about AI. However, it is not a good thing for everyone. One of the things that many people are worried … [Read More...]

Redditors Talk About the Impact of AI on Freelance Writers

AI technology has had a huge impact on our lives. A 2023 survey by Pew Research found that 56% of people use AI at least once a day or once a week. … [Read More...]

11 Most Popular Books on Perl Programming

Perl is not the most popular programming language. It has only one million users, compared to 12 million that use Python. However, it has a lot of … [Read More...]

Guides

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

Recent Posts

MyStart Incredibar Removal

MyStart Incedibar is a browser highjack.  It normally comes bundled with other free programs.  Once installed on a computer it highjacks your home … [Read More...]

Bios Configuration

Whichever method you've used to update your BIOS, it's important that the new BIOS Setup defaults are used when the system is next powered on. You … [Read More...]

Android NewTab Virus Removal Guide

Android Newtab is a browser hijacker that will redirect your browser homepage and default home page to start.androidnewtab.com. The homepage … [Read More...]

[footer_backtotop]

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