Scribd
Upload
57 views9 pages

Built-In Functions: What You Learn

This document provides an overview of different topics covered in VBA including: - Comparing VBA and Excel worksheet functions with examples of the Date and Max functions. - Working with common VBA functions such as Month, MonthName, and ISEMPTY. - Using the MsgBox function to display simple message boxes and get user feedback. - Creating input boxes using the InputBox function and method to add data and allow user selection. - An activity that uses an input box for the user to select a range of numbers and a message box to display the top 3 values.

Uploaded by

Burhan Asri
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
57 views9 pages

Built-In Functions: What You Learn

This document provides an overview of different topics covered in VBA including: - Comparing VBA and Excel worksheet functions with examples of the Date and Max functions. - Working with common VBA functions such as Month, MonthName, and ISEMPTY. - Using the MsgBox function to display simple message boxes and get user feedback. - Creating input boxes using the InputBox function and method to add data and allow user selection. - An activity that uses an input box for the user to select a range of numbers and a message box to display the top 3 values.

Uploaded by

Burhan Asri
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd

What you learn Built-in Functions

Link Topic Covered What you Learn

VBAExcel VBA versus Excel Worksheet ▪ Using VBA Date Function


Functions - some examples ▪ Lower Case, Upper Case and Proper Case
▪ Difference between VBA & Excel functions
▪ Using Max worksheet function

VBAFunctions Working with VBA Functions


▪ VBA Month & MonthName functions
▪ VBA ISEMPTY function (similar to ISBLANK in Excel)
▪ VBA TYPENAME function
MsgBox Display Message Box with MsgBox
▪ Quick & simple message box with MsgBox function
Function ▪ MsgBox that asks for user feedback
InputBox_VBA Create an Input Box: InputBox VBA
Function
▪ Quick & simple input box with InputBox function
▪ Use Input box to add data to the last row of the database
InputBox_Excel Create an Input Box: Excel InputBox
Method ▪ Excel Input Box method for more flexibility (e.g. data
validation)
▪ Use Input box to allow user to select ranges
Activity Activity: Create an Input Box and a With an input box, the user will select a range of numbers and
Message Box with message box they find out which are the top 3 values in
the range selected.
VBA versus Excel Worksheet Functions - some examples

Get Date here: 5/1/2020

Change the below to their specified case:


uPPer caSe UPPER CASE
loweR casE lower case
proPer caSE Proper Case
proper case Proper Case

Get Max of below data set : 740 96

52 20 70 257 495 627 120


33 40 10 625 562 246 265
65 59 65 713 303 410 478
15 62 12 141 172 275 706
24 41 72 657 492 420 89
35 96 39 396 195 365 403
75 46 92 435 35 473 693
59 38 16 310 372 572 71
Working with VBA Functions

Current month: 5
Current month name: May
Month abbrev. of this number: 3 May

Are these cells empty?


0
Empty? 0
1
Display Message Box with MsgBox Function
1. Create a welcome message for the user.
2. Clear values in A7:B9 - but before clearing double-check with the user.

Welcome! Delete Range


Create an Input Box: InputBox VBA Function
Create an InputBox to add a
Selamat Datang Subtitle

Customer Name
Tanox
Wencal
Create an InputBox to add
Abdul Aziz the customer name to the
Uwa Haji Umuh next empty cell.
Akang Zlatan
Create an Input Box: Excel InputBox Method

Amount
Customer Name Invoiced Add the customer name and
Tanox 200 amount to the next available
Wencal 300 row (use InputBox Method
for the amount)
Burhanudin 20000

Let the user highlight a range. Once


they press ok, let them know how
many blank cells and non-blank
cells are in the selected range.
mer name and
next available
Box Method
t)

hlight a range. Once


et them know how
s and non-blank
selected range.
Activity: Create an Input Box and a Message Box
The aim of the exercise is as follows: Example of input box
1. Create an input box which allows the user to highlight a range of numbers.
2. After the user clicks ok, they will get a message box.
3. The message box shows them the top 3 numbers, in the format shown.
4. Assign the macro to the button.

Bid Amount
6335
6173
7203
7070
8046
7218 The answer is in the completed workbook in module "ProjectActivity"
7741
6042
6381
8489
7820
6951
7702
8916
7615
7793
8477
7899
6503
7254
8651
7018
7186
7006
7183
8076
of input box Example of message box

You might also like