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

Basic structure of a Pentium microprocessor

A Pentium processor's major functional components are: Core: The heart of a Pentium is the execution unit. The Pentium has two parallel integer pipelines enabling it to read, interpret, execute and despatch two instructions simultaneously. Branch Predictor: The branch prediction unit tries … [Read More...]

Hard Disk MicroDrives

GMR technology was a key feature of IBM's (the company's hard disk drive business was subsequently acquired by Hitachi Global Storage Technologies in 2002) revolutionary Microdrive device, launched in mid-1999. The world’s smallest, lightest one-inch hard drive was invented at … [Read More...]

ATX form factor

The Intel Advanced/ML motherboard, launched in 1996, was designed to solve issues of space and airflow that the Pentium II and AGP graphics cards had caused the preceding LPX form factor. As the first major innovation in form factors in years, it marked the beginning of a new era in … [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...]

10 Exceptional Books on ChatGPT that Will Blow Your Mind

ChatGPT is a powerful new AI tool that is taking the world by storm. You are going to find a lot of amazing books that will teach you how to make the … [Read More...]

Guides

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

Recent Posts

Hard Disk Maintenance – Error Checking using Windows XP ScanDisk tools

Chkdsk.exe is a utility which verifies the logical integrity of a file system. In the event of the utility encountering logical inconsistencies in … [Read More...]

XP Defender 2013

XP Defender 2013 Hello again! If you've found this page, you've probably been infected with XP Defender 2013 . Malware of this kind, while not … [Read More...]

Virtual Memory

Video Covering Virtual Memory - Part of our Fix Slow Computer tutorial found on  … [Read More...]

[footer_backtotop]

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