Access Excel remove text in brackets

This Access Excel VBA tutorial explains how to remove text in brackets using custom Function.

You may also want to read:

Change text color in brackets

Extract text in brackets

Access Excel remove text in brackets

I was inspired to create this post because the HR system I am using (Workday) is very annoying, some data fields are actually a combination of two different fields, the additional data field is displayed in brackets in the prefix. For example,  an employee has a English name followed by local name inside brackets in the suffix, but I only want the English name. Therefore I create a custom Function so that I can use it in Microsoft Access and Microsoft Excel.

VBA Code – remove text in brackets

In this custom Function, you can specify the exact type of open bracket and end bracket you want to apply. It searches for multiple sets of brackets, not just one set.

Public Function wRemoveBrackets(myString, stStr, edStr)
    tempString = myString
    For i = 1 To Len(myString)
        If InStr(tempString, stStr) > 0 And InStr(myString, edStr) > 0 And InStr(tempString, edStr) > InStr(tempString, stStr) Then
            tempString = Trim(Left(tempString, InStr(tempString, stStr) - 1) & Right(tempString, Len(tempString) - InStr(tempString, edStr)))
        End If
    Next i
    wRemoveBrackets = tempString
End Function

Function Syntax

wRemoveBrackets(myString, stStr, edStr)
ArgumentExplanation
myStringThe text or Range that contains the brackets contents to remove
stStrDefine the open bracket
edStrDefine the close bracket

Example

The results are self-explanatory. This custom Function removes all characters inside specific open / close brackets, brackets inclusive.

ValueFormulaResultExplanation
123 (456) 789 (012) 345=removeBrackets(A2,”(“,”)”)123  789  345Remove characters inside two sets of brackets
123()(456)789=removeBrackets(A3,”(“,”)”)123789Remove brackets even if there is no contents inside
123[456]789(012)=removeBrackets(A4,”[“,”]”)123789(012)Remove characters inside square brackets as specified in the argument
(45678(9012))=removeBrackets(A5,”(“,”)”))Remove characters inside the complete pair of (   )

Note that you can apply the function more than once in order to remove more than 1 set of brackets. For example, to remove square brackets and round brackets in text in A1

=removeBrackets(removeBrackets(A1,"(",")"),"[","]")

 

Access Excel INT Function to get the integer of number

This Access / Excel tutorial explains how to use INT Function to get the integer part of a number.

Access Excel INT Function to get the integer of number

When you have a number with decimal places, you can separate the number into integer part using INT Function, and then work around to get the decimal part. INT Function can be used in Access and Excel, and also VBA.

Syntax of INT Function

INT(number)

INT Function only contains one argument, which is the number from which you want to get the integer part.

The syntax for Access, Excel and VBA are same.

Example of INT Function

Example for positive numbers

FormulaResultExplanation
INT(11.4)11Integer part of 11.4 is 11
INT(11.6)11Integer part of 11.6 is 11 also ,regardless whether the decimal part is over 0.5
11.6-INT(11.6)0.6Return the decimal part of 11.6

 

Unlike positive number, handling of negative number is a little bit confusing, it returns the first whole number less than your input number. For example, -5.1 would become -6. In the below examples, I will demonstrate how to return 5 or -5 instead of -6.

FormulaResultExplanation
INT(-5.1)-6The first whole number less than -5.1 is -6
INT(abs(-5.1))5To return the true integer part using absolute function
IF(A1<0,-INT(ABS(A1)),INT(ABS(A1)))-5Suppose A1 is -5.1, to return the true integer part and then add the sign back

Example of VBA INT Function

INT Function is particularly useful in custom rounding, below is a custom MRound function that I created in my previous post, click here to read more.

Function wMRound(pValue As Double, multiple) As Double
    Dim negNumber As Boolean
    If pValue < 0 Then
        pValue = 0 - pValue
        negNumber = True
    End If
    If multiple < 0 Then
        multiple = 0 - multiple
    End If
    
    Select Case pValue / multiple - Int(pValue / multiple)
        Case Is < 0.5
            result = Int(pValue / multiple) * multiple
        Case Is >= 0.5
            result = Int(pValue / multiple) * (multiple) + multiple
    End Select
        
    If negNumber = True Then
        wMRound = 0 - result
    Else
        wMRound = result
    End If
End Function

Outbound References

https://support.office.com/en-us/article/INT-function-A6C4AF9E-356D-4369-AB6A-CB1FD9D343EF

Excel VBA NPV Function to calculate Net Present Value

This Excel VBA tutorial explains how to calculate net present value using Excel VBA NPV Function.

You may also want to read:

Excel worksheet NPV Function

Net Present Value

Net Present Value is to calculate the present value of future cash flow. For example, your client will give you $10000 when the project completes after 1 year, assume there will be inflation, the future $10000 will be less than the present $10000. Assume the inflation is 4%, the present value of $10000 is 10000/(1+4%) = 9615.384615    This calculation is just a simple maths, you don’t even need to memorize any formula.

If your company needs to invest $9900 today in order to gain $10000 after 1 year, then you should not take this project because $9615 you will gain is less than $9900 you invest today.

If you receive $10000 after 1 year, and $5000 in the second year, then

Net present value = $10000/(1+4%) + $5000/(1+4%)2

= 9615.38 + 4622.78

= 14238.17

Excel VBA NPV Function

Instead of calculating the Net Present Value manually, Excel NVP Function provides a quick way to calculate the Net Present Value.

Excel worksheet NPV Function is similar to Excel VBA NPV Function.

Syntax of VBA NPV Function

NPV(rate,valueArray())
RateRequired. The rate of discount over the length of one period.
valueArray()Required. An array to store the cash flow in double format.

It is important to note that array has to start from item 0. If you start from item 1, the first cash flow item is assumed to be $0.

Example 1

Using the above example, if the discount rate is 4%, while the cash flow return is $10000 after the first year, $5000 after the second year

Public Sub testNPV()
    Dim npvArray(1) As Double
    npvArray(0) = 10000
    npvArray(1) = 5000
    MsgBox (NPV(0.04, npvArray()))
End Sub

Result

Excel VBA NPV Function

Example 2

If you have a lot of cash flow, you can type the values in worksheet first and then convert them into array in VBA.

Excel VBA NPV Function 02

Public Sub testNPV()
    Dim npvArray() As Double   
     For r = 2 To Range("A" & Rows.Count).End(xlUp).Row
        ReDim Preserve npvArray(r - 2)
        npvArray(r - 2) = Range("B" & r).Value
    Next r    
    MsgBox (NPV(0.04, npvArray()))
End Sub

Result

Excel VBA NPV Function 03

 

 

Excel VBA Hyperlinks Function

This Excel VBA tutorial explains how to use Hyperlinks Function to send email and add hyperlink to Cell.

Excel VBA Hyperlinks Function

The purpose of the Hyperlinks Function is to remove or add hyperlink to a Cell, the hyperlink can be an email or a website.

There is another Worksheet Function Hyperlink (without s).

Hyperlinks.Add Method

Syntax

expression.Add(Anchor, Address, SubAddress, ScreenTip, TextToDisplay)
NameRequired/OptionalData TypeDescription
AnchorRequiredObjectThe anchor for the hyperlink. Can be either a Range or Shape object.
AddressRequiredStringThe address of the hyperlink.
SubAddressOptionalVariantThe subaddress of the hyperlink.
ScreenTipOptionalVariantThe screen tip to be displayed when the mouse pointer is paused over the hyperlink.
TextToDisplayOptionalVariantThe text to be displayed for the hyperlink.

Example 1 – Add website hyperlink

The below example adds a text “Access-Excel.tips” in Cell A1, and add a hpyerlink to http://access-excel.tips. ScreenTip is the yellow pop up box when you hover the move over the hyperlink.

Public Sub add_hyperlink()
    ActiveSheet.Hyperlinks.Add anchor:=Range("A1"), Address:="http://access-excel.tips", ScreenTip:="This is my website", TextToDisplay:="Access-Excel.tips"
End Sub

Excel VBA Hyperlinks Function 01

Example 2 – Add file hyperlink

Simply replace the website with the file path to add file link.

Public Sub add_hyperlink()
    ActiveSheet.Hyperlinks.Add anchor:=Range("A1"), Address:="C:\example.docx", ScreenTip:="Open file", TextToDisplay:="example.docx"
End Sub

Excel VBA Hyperlinks Function 001

Alternatively, you can insert an object as below, click here for details.

Excel insert file 07

 

Example 3 – Add Email hyperlink

The below example adds a text “Email me” in Cell A1, and add an email hpyerlink to my email address.

Public Sub add_hyperlink()
    ActiveSheet.Hyperlinks.Add anchor:=Range("A1"), Address:="mailto:scammera1@yahoo.com.hk", ScreenTip:="This is my email", TextToDisplay:="Email me"
End Sub

Excel VBA Hyperlinks Function 02

 

Click on the link to open the default email application.

Excel VBA Hyperlinks Function 03

There are other methods to send email via Excel VBA where you can do more customization, click on the below post.

Send Email in Excel VBA using CDO and OLMailItem

 

 

Remove Hyperlink using Hyperlinks.Delete Method

The below example removes all hyperlinks in active worksheet.

Public Sub del_hyperlink()
    ActiveSheet.Hyperlinks.Delete
End Sub

 

The below example removes hyperlink in Cell A1. You can customize the If Condition to suit your needs such as removing hyperlink of specific range value.

Public Sub del_hyperlink()
    For Each hyper In ActiveSheet.Hyperlinks
        If hyper.Range.Address = "$A$1" Then
            hyper.Delete
        End If
    Next
End Sub

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff822490.aspx

 

Excel VBA DIR Function

This Excel tutorial explains how to use Excel VBA DIR Function and loop through workbooks in a folder.

You may also want to read:

Excel loop workbooks in folders and subfolders with FileSystemObject

Excel VBA DIR Function

DIR Function returns the first file name that matches the criteria in Function argument (pathname and attributes).

DIR Function is usually used with loop in order to manipulate multiple files of specific name.

Syntax of DIR Function

Dir(pathname, [attributes])
pathnameOptional. String expression that specifies a folder path+file name or folder path . A zero-length string (“”) is returned if pathname is not found. Support wildcard characters.

WildcardExplanation
*Allows you to match any string of any length (including zero length)
?Allows you to match on a single character

 

attributesOptional. Constant or numeric expression, whose sum specifies file attributes. If omitted, returns files that match pathname but have no attributes.

ConstantValueDescription
vbNormal0(Default) Specifies files with no attributes.
vbReadOnly1Specifies read-only files in addition to files with no attributes.
vbHidden2Specifies hidden files in addition to files with no attributes.
VbSystem4Specifies system files in addition to files with no attributes. Not available on the Macintosh.
vbVolume8Specifies volume label; if any other attributed is specified, vbVolume is ignored. Not available on the Macintosh.
vbDirectory16Specifies directories or folders in addition to files with no attributes.
vbAlias64Specified file name is an alias. Available only on the Macintosh.

 

Normally DIR Function only retrieves the first matched file name, but you can use Dir with no argument to retrieve additional file name (refer to the example 2).

Example of DIR Function

In this example, I create a folder called “testing” with some workbooks inside and place it in Desktop.

excel_dir_function

Example 1: Test Folder Path

Create a Sub Procedure as below

Public Sub firstFileName()
    Filename = Dir("C:\Users\WYMAN\Desktop\testing\")
    MsgBox (Filename)
End Sub

The result is 11.xlsx, which is the first file in the testing folder. The first file means the first file in ascending file name.

excel_dir_function_02

Example 2: Loop through workbook of specific file name

The below code finds all files with xlsx extension and file name starting with 2.

The purpose of code Filename = Dir() in the end is to find additional file name.

Public Sub LoopFileName()
    Filename = Dir("C:\Users\WYMAN\Desktop\testing\2*.xlsx")
    Do While Filename <> ""
        MsgBox (Filename)
        Filename = Dir()
    Loop
End Sub

excel_dir_function_03

Example 3: Test if a file exists

Public Sub LoopFileName()
    Filename = Dir("C:\Users\WYMAN\Desktop\testing\2*.xlsx")
    If Filename = "" Then
        MsgBox "file not found"
    Else
        Do While Filename <> ""
            MsgBox (Filename)
            Filename = Dir()
        Loop
    End If
End Sub

Alternate solution to loop through workbooks in a folder

You can also loop through workbooks using File System Object (FSO), which I think is a better solution than DIR Function because it supports additional Methods such as deleting file objects.

Outbound References

https://msdn.microsoft.com/en-us/library/office/gg278779.aspx

 

Excel VBA Inputbox and Application.Inputbox

This Excel tutorial explains difference between VBA Inputbox and  Application.Inputbox and how to use them.

You may also want to read:

Excel VBA Msgbox and handling of return value

Excel VBA use Inputbox to select options

Excel VBA Inputbox and Application.Inputbox

Inputbox is a pop up dialog box that displays a message and provides a text box for users to input free text. It is very similar to Msgbox except that Msgbox does not have a text box for user input.

There are two kinds of Inputbox: Inputbox and Application.Inputbox. Application.InputBox allows selective validation of the user’s input, and it can be used with Microsoft Excel objects, error values, and formulas. Note that Application.InputBox calls the InputBox method; InputBox with no object qualifier calls the InputBox function.

Syntax of Inputbox (Function)

InputBox(Prompt, [Title], [Default], [Left], [Top], [HelpFile], [HelpContextID])
PromptRequiredThe message to be displayed in the dialog box. If prompt consists of more than one line, you can separate the lines using a carriage return character (Chr(13)), a linefeed character (Chr(10)), or carriage return–linefeed character combination (Chr(13) & Chr(10)) between each line.
TitleOptionalThe title for the input box. If this argument is omitted, the application name is placed in the title bar.
DefaultOptionalString expression displayed in the text box as the default response if no other input is provided. If you omit default, the text box is displayed empty.
LeftOptionalSpecifies an x position for the dialog box in relation to the upper-left corner of the screen, in points.If omitted, the dialog box is horizontally centered.
TopOptionalSpecifies a y position for the dialog box in relation to the upper-left corner of the screen, in points. If omitted, the dialog box is vertically positioned approximately one-third of the way down the screen.
HelpFileOptionalThe name of the Help file for this input box. If the HelpFile and HelpContextID arguments are present, a Help button will appear in the dialog box.
HelpContextIDOptionalThe context ID number of the Help topic in HelpFile.

Example of Inputbox (Function)

The below example demonstrates how to create an input box for users to input their names, and then use the name in MsgBox.

Note that if user press Cancel button, use sinput = “” to capture.

 Public Sub inputFunction()
    sinput = InputBox("Please input your name", "User Input", "default value")
    If sinput = "" Then
        Exit Sub
    Else
        MsgBox ("Your name is " & sinput)
    End If
End Sub

 excel_inputbox

When OK button is pressed

excel_inputbox_02

Syntax of Application.Inputbox (Method)

Application.InputBox(Prompt, [Title], [Default], [Left], [Top], [HelpFile], [HelpContextID], [Type])

PromptRequiredThe message to be displayed in the dialog box. If prompt consists of more than one line, you can separate the lines using a carriage return character (Chr(13)), a linefeed character (Chr(10)), or carriage return–linefeed character combination (Chr(13) & Chr(10)) between each line.
TitleOptionalThe title for the input box. If this argument is omitted, the default title is “Input.”
DefaultOptionalSpecifies a value that will appear in the text box when the dialog box is initially displayed. If this argument is omitted, the text box is left empty. This value can be a Range object.
LeftOptionalSpecifies an x position for the dialog box in relation to the upper-left corner of the screen, in points.If omitted, the dialog box is horizontally centered.
TopOptionalSpecifies a y position for the dialog box in relation to the upper-left corner of the screen, in points. If omitted, the dialog box is vertically positioned approximately one-third of the way down the screen.
HelpFileOptionalThe name of the Help file for this input box. If the HelpFile and HelpContextID arguments are present, a Help button will appear in the dialog box.
HelpContextIDOptionalThe context ID number of the Help topic in HelpFile.
TypeOptionalSpecifies the return data type. If this argument is omitted, the dialog box returns text.

ValueDescription
0A formula
1A number
2Text (a string)
4A logical value (True or False)
8A cell reference, as a Range object
16An error value, such as #N/A
64An array of values

As you can see, Application.Inputbox has one more argument “Type” which could perform validation.

Example of Application.Inputbox (Method)

The below example demonstrates how to create an input box for users to input their names, and then use the name in MsgBox.

You should use sinput = False to prevent error when users click on Cancel Button.

 Public Sub inputMethod()
    sinput = Application.InputBox("Please input your name", "User Input", "default value")
    If sinput = False Then
        Exit Sub
    Else
        MsgBox ("Your name is " & sinput)
    End If
End Sub

You may set the Range using Type:=8 in the last argument. In this case, you should use On Error to prevent error from pressing Cancel Button as well as setting value as Range.

Public Sub inputRanged()
On Error GoTo errHandler:
    Set rng = Application.InputBox("Please input data Range", "User Input", Type:=8)
    rng.Interior.ColorIndex = 19
errHandler: Exit Sub
End Sub

If you enter a value that is not correct (not a Range), an error message will pop up and then return you to the Inputbox. This is how the validation of data type is done with Application.Inputbox, such checking applies to other formats as well.

excel_inputbox_03

If you do not like the message from validation to pop up, turn it off with

Application.DisplayAlerts = False

Carriage Return

You can enter the text in two rows instead of one row by using Carriage Return Code vbCrLf

Example:

Application.InputBox("This is row 1" & vbCrLf & "This is row 2")

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff839468.aspx

https://msdn.microsoft.com/en-us/library/office/gg251811.aspx

Excel VBA Rnd Function to generate random number

This Excel tutorial explains how to use Excel VBA Rnd Function to generate random number and create custom Function for random decimal number.

You may also want to read:

Excel RAND RANDBETWEEN Function to generate random number

Access Excel Generate random HKID Hong Kong ID card number

Assign unique random number using Excel

Excel VBA Rnd Function to generate random number

Rnd Function is for use in Access, Access VBA, Excel VBA, it is used to generate random number larger than 0 and smaller than 1 (1 and 0 exclusive).

Excel worksheet use RAND Function and RANDBETWEEN Functions, which have quite different behavior.

Syntax of Excel VBA Rnd Function

Rnd[(number)]

NumberRnd generates
Not supplied or any positive numberGenerate a new random number
0Return the last generated number
Any negative numberGenerate a new random number and use it every time

Example of Excel VBA Rnd Function – generate random number

It is not useful to use Excel VBA Rnd Function alone, because we ultimately want to generate a random number between two specific numbers, but not a random number between 0 and 1.

Use the below formula to generate a number bounded by lowerbound and upperbound (both inclusive).

Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)
FormulaExplanation
Int((10-1+1)*Rnd()+1)Generate random number between 1 and 10
Int((200-100+1)*Rnd()+100)Generate random number between 100 and 200

VBA custom Function – generate random decimal number

If you think the formula is too complicated, you can use the below custom Function. The purpose of this custom Function is to simulate Excel worksheet Function RandBetween, which allows you to simply use upperbound and lowerbound as Function arguments.

This custom Function offers an optional parameter that allows you to generate random integer or decimal number.

VBA code of custom Function – generate random decimal number

Public Function wRandomNumber(lowerbound, upperbound, Optional rndType = 1) As Double
    Randomize
    rndVariable = Rnd
    If rndType = 1 Then
        wRandomNumber = Int((upperbound - lowerbound + 1) * rndVariable + lowerbound)
    ElseIf rndType = 2 Then
        If (upperbound - lowerbound + 1) * rndVariable + lowerbound <= upperbound Then
            wRandomNumber = (upperbound - lowerbound + 1) * rndVariable + lowerbound
        Else
            Do While (upperbound - lowerbound + 1) * rndVariable + lowerbound > upperbound
                rndVariable = Rnd
                If (upperbound - lowerbound + 1) * rndVariable + lowerbound <= upperbound Then
                    wRandomNumber = (upperbound - lowerbound + 1) * rndVariable + lowerbound
                End If
            Loop
        End If
    End If
End Function

Syntax of custom Function – generate random decimal number

wRandomNumber(lowerbound, upperbound, [rndType])
lowerboundThe minimum random number
upperboundThe maximum random number
rndTypeOptional, indicate whether the random number is decimal or integer

valuedescription
1 (default)Return a random integer
2Return a random decimal number

Example of custom Function – generate random decimal number

FormulaExplanation
=wRandomNumber(1,100)Return a random integer between 1 and 100 (both inclusive)
=wRandomNumber(100,200,2)Return a random decimal number between 100 and 200 (both inclusive)

Outbound References

https://support.office.com/en-us/article/Rnd-Function-503CD2E4-3949-413F-980A-ED8FB35C1D80

VBA Excel Split Function to delimit text

 What does VBA Excel Split Function do?

VBA Excel Split Function separates a text by delimiter into an array, for example, you can split a text a;b;c;d into array {a,b,c,d}, afterwards you can get the desired array item (starting from 0). Split Function is similar to the action of “Text to Columns”.

Syntax of VBA Excel Split Function

Split(text,[delimiter],[limit],[compare])
texttext to be delimited
delimiterOptional. String character used to identify substring limits. If omitted, the space character (” “) is assumed to be the delimiter. If delimiter is a zero-length string, a single-element array containing the entire expression string is returned.
limitOptional. Number of substrings to be returned; –1 indicates that all substrings are returned.
compareOptional. Type of comparison to perform. It can be one of the following values:

VBA ConstantValueExplanation
vbUseCompareOption-1Default value, uses setting of Option Compare statement defined at Module level.(Two options: Option Compare Text / Option Compare Binary)If you don’t write Option Compare statement, Option Compare Binary is default
vbBinaryCompare0Binary comparison, turn text into a sequence to compare. Since the sequence is case sensitive, you can compare text using this option if you want the comparison to be case sensitive, which means you cannot search “aaa” within “AAA”
vbTextCompare1Compare text, case insensitive, which means you can search “aAa” within “AAA”

Example of Excel VBA Split Function

VBA CodeResultExplanation
Split(“Mary Ann”, ” “)(0)MaryReturn the first array item of split (array item starts from 0)
Split(“Mary Ann”, ” “)(1)AnnReturn the second array item of split
Split($A$1,”$”)(1)AGet the column name of Cell address
Split(Cells(1, column_num).Address, “$”)(1)Convert numeric column number (1,2,3) to column name (A,B,C)

The below code assign an array name arr() to the split array, and then loop through all array items to count number of “Mary” (case insensitive)

Public Sub convtArray1()
    Dim arr() As String
    arr() = Split("Mary Ann MARY", " ")
    For i = 0 To UBound(arr())
        If UCase(arr(i)) = "MARY" Then
            Count = Count + 1
        End If
    Next i
    MsgBox (Count)
End Subb

Outbound References

https://msdn.microsoft.com/en-us/library/6x627e5f%28v=vs.90%29.aspx

Access Excel VBA Chr Function to convert ASCII value to character

This Access Excel VBA tutorial explains how to use Chr Function to convert ASCII value to character, and convert from character to ASCII value with ASC Function

What is Access Excel VBA Chr Function?

Access Excel VBA Chr Function converts ASCII value to a character. ASCII (American Standard Code for Information Interchange) uses 8-bit code units, an old encoding system which stores mainly numbers, lowercase letters a to z, uppercase letters A to Z, basic punctuation symbols, control codes. Many old systems still use this encoding system. 8 bit means the computer memory uses “8” digits with 1 and 0 combination (binary) to represent a character, 8 bits memory is equal to 1 byte.

Chr Function is applicable to: Excel VBA, Access, Access VBA

Refer to the below table, Excel Char Function will convert the “Dec” value to “Char” value.

DecHexOctCharDescription
00000null
11001start of heading
22002start of text
33003end of text
44004end of transmission
55005enquiry
66006acknowledge
77007bell
88010backspace
99011horizontal tab
10A012new line
11B013vertical tab
12C014new page
13D015carriage return
14E016shift out
15F017shift in
1610020data link escape
1711021device control 1
1812022device control 2
1913023device control 3
2014024device control 4
2115025negative acknowledge
2216026synchronous idle
2317027end of trans. block
2418030cancel
2519031end of medium
261A032substitute
271B033escape
281C034file separator
291D035group separator
301E036record separator
311F037unit separator
3220040space
3321041!
3422042
3523043#
3624044$
3725045%
3826046&
3927047
4028050(
4129051)
422A052*
432B053+
442C054,
452D055
462E056.
472F057/
48300600
49310611
50320622
51330633
52340644
53350655
54360666
55370677
56380708
57390719
583A072:
593B073;
603C074<
613D075=
623E076>
633F077?
6440100@
6541101A
6642102B
6743103C
6844104D
6945105E
7046106F
7147107G
7248110H
7349111I
744A112J
754B113K
764C114L
774D115M
784E116N
794F117O
8050120P
8151121Q
8252122R
8353123S
8454124T
8555125U
8656126V
8757127W
8858130X
8959131Y
905A132Z
915B133[
925C134\
935D135]
945E136^
955F137_
9660140`
9761141a
9862142b
9963143c
10064144d
10165145e
10266146f
10367147g
10468150h
10569151i
1066A152j
1076B153k
1086C154l
1096D155m
1106E156n
1116F157o
11270160p
11371161q
11472162r
11573163s
11674164t
11775165u
11876166v
11977167w
12078170x
12179171y
1227A172z
1237B173{
1247C174|
1257D175}
1267E176~
1277F177DEL

Extended Character Set

DecHexUnicode Open in a new window...Char Zoom...Name
12880U+20ACEuro Sign
12981Undefined
13082U+201ASingle Low-9 Quotation Mark
13183U+0192ƒLatin Small Letter F With Hook
13284U+201EDouble Low-9 Quotation Mark
13385U+2026Horizontal Ellipsis
13486U+2020Dagger
13587U+2021Double Dagger
13688U+02C6ˆModifier Letter Circumflex Accent
13789U+2030Per Mille Sign
1388AU+0160ŠLatin Capital Letter S With Caron
1398BU+2039Single Left-pointing Angle Quotation Mark
1408CU+0152ŒLatin Capital Ligature Oe
1418DUndefined
1428EU+017DŽLatin Capital Letter Z With Caron
1438FUndefined
14490Undefined
14591U+2018Left Single Quotation Mark
14692U+2019Right Single Quotation Mark
14793U+201CLeft Double Quotation Mark
14894U+201DRight Double Quotation Mark
14995U+2022Bullet
15096U+2013En Dash
15197U+2014Em Dash
15298U+02DC˜Small Tilde
15399U+2122Trade Mark Sign
1549AU+0161šLatin Small Letter S With Caron
1559BU+203ASingle Right-pointing Angle Quotation Mark
1569CU+0153œLatin Small Ligature Oe
1579DUndefined
1589EU+017EžLatin Small Letter Z With Caron
1599FU+0178ŸLatin Capital Letter Y With Diaeresis
160A0U+00A0 No-break Space
161A1U+00A1¡Inverted Exclamation Mark
162A2U+00A2¢Cent Sign
163A3U+00A3£Pound Sign
164A4U+00A4¤Currency Sign
165A5U+00A5¥Yen Sign
166A6U+00A6¦Broken Bar
167A7U+00A7§Section Sign
168A8U+00A8¨Diaeresis
169A9U+00A9©Copyright Sign
170AAU+00AAªFeminine Ordinal Indicator
171ABU+00AB«Left-pointing Double Angle Quotation Mark
172ACU+00AC¬Not Sign
173ADU+00AD­Soft Hyphen
174AEU+00AE®Registered Sign
175AFU+00AF¯Macron
176B0U+00B0°Degree Sign
177B1U+00B1±Plus-minus Sign
178B2U+00B2²Superscript Two
179B3U+00B3³Superscript Three
180B4U+00B4´Acute Accent
181B5U+00B5µMicro Sign
182B6U+00B6Pilcrow Sign
183B7U+00B7·Middle Dot
184B8U+00B8¸Cedilla
185B9U+00B9¹Superscript One
186BAU+00BAºMasculine Ordinal Indicator
187BBU+00BB»Right-pointing Double Angle Quotation Mark
188BCU+00BC¼Vulgar Fraction One Quarter
189BDU+00BD½Vulgar Fraction One Half
190BEU+00BE¾Vulgar Fraction Three Quarters
191BFU+00BF¿Inverted Question Mark
192C0U+00C0ÀLatin Capital Letter A With Grave
193C1U+00C1ÁLatin Capital Letter A With Acute
194C2U+00C2ÂLatin Capital Letter A With Circumflex
195C3U+00C3ÃLatin Capital Letter A With Tilde
196C4U+00C4ÄLatin Capital Letter A With Diaeresis
197C5U+00C5ÅLatin Capital Letter A With Ring Above
198C6U+00C6ÆLatin Capital Ligature Ae
199C7U+00C7ÇLatin Capital Letter C With Cedilla
200C8U+00C8ÈLatin Capital Letter E With Grave
201C9U+00C9ÉLatin Capital Letter E With Acute
202CAU+00CAÊLatin Capital Letter E With Circumflex
203CBU+00CBËLatin Capital Letter E With Diaeresis
204CCU+00CCÌLatin Capital Letter I With Grave
205CDU+00CDÍLatin Capital Letter I With Acute
206CEU+00CEÎLatin Capital Letter I With Circumflex
207CFU+00CFÏLatin Capital Letter I With Diaeresis
208D0U+00D0ÐLatin Capital Letter Eth
209D1U+00D1ÑLatin Capital Letter N With Tilde
210D2U+00D2ÒLatin Capital Letter O With Grave
211D3U+00D3ÓLatin Capital Letter O With Acute
212D4U+00D4ÔLatin Capital Letter O With Circumflex
213D5U+00D5ÕLatin Capital Letter O With Tilde
214D6U+00D6ÖLatin Capital Letter O With Diaeresis
215D7U+00D7×Multiplication Sign
216D8U+00D8ØLatin Capital Letter O With Stroke
217D9U+00D9ÙLatin Capital Letter U With Grave
218DAU+00DAÚLatin Capital Letter U With Acute
219DBU+00DBÛLatin Capital Letter U With Circumflex
220DCU+00DCÜLatin Capital Letter U With Diaeresis
221DDU+00DDÝLatin Capital Letter Y With Acute
222DEU+00DEÞLatin Capital Letter Thorn
223DFU+00DFßLatin Small Letter Sharp S
224E0U+00E0àLatin Small Letter A With Grave
225E1U+00E1áLatin Small Letter A With Acute
226E2U+00E2âLatin Small Letter A With Circumflex
227E3U+00E3ãLatin Small Letter A With Tilde
228E4U+00E4äLatin Small Letter A With Diaeresis
229E5U+00E5åLatin Small Letter A With Ring Above
230E6U+00E6æLatin Small Ligature Ae
231E7U+00E7çLatin Small Letter C With Cedilla
232E8U+00E8èLatin Small Letter E With Grave
233E9U+00E9éLatin Small Letter E With Acute
234EAU+00EAêLatin Small Letter E With Circumflex
235EBU+00EBëLatin Small Letter E With Diaeresis
236ECU+00ECìLatin Small Letter I With Grave
237EDU+00EDíLatin Small Letter I With Acute
238EEU+00EEîLatin Small Letter I With Circumflex
239EFU+00EFïLatin Small Letter I With Diaeresis
240F0U+00F0ðLatin Small Letter Eth
241F1U+00F1ñLatin Small Letter N With Tilde
242F2U+00F2òLatin Small Letter O With Grave
243F3U+00F3óLatin Small Letter O With Acute
244F4U+00F4ôLatin Small Letter O With Circumflex
245F5U+00F5õLatin Small Letter O With Tilde
246F6U+00F6öLatin Small Letter O With Diaeresis
247F7U+00F7÷Division Sign
248F8U+00F8øLatin Small Letter O With Stroke
249F9U+00F9ùLatin Small Letter U With Grave
250FAU+00FAúLatin Small Letter U With Acute
251FBU+00FBûLatin Small Letter U With Circumflex
252FCU+00FCüLatin Small Letter U With Diaeresis
253FDU+00FDýLatin Small Letter Y With Acute
254FEU+00FEþLatin Small Letter Thorn
255FFU+00FFÿLatin Small Letter Y With Diaeresis

Why do you need Access Excel  VBAChr Function?

1. To import special characters. However, Windows may not be able to show those characters.

2. Start a new line in a Cell with Char(10) in worksheet (must need to set Wrap Text first) and MsgBox message

Syntax of Access Excel VBA Chr Function

CHR( ascii_value )

ascii_value is the ASCII value used to retrieve the character.

Note that Chr Function also exists for worksheet but the name is Char. You can convert the character back into ASCII value using Code Function in worksheet / ASC Function in VBA

Example of Access Excel VBA Chr Function

FormulaResultExplanation
Chr(65)A
Chr(90)Z
Range(“A1”).Value = “line one” & Chr(10) & “line two”line one
line two
Start a new line with Char(10)
 Range(“A1”).Value = Replace(“line one,line two”, “,”, Chr(10))line one
line two
 Replace comma with new line

To start a new line in VBA MsgBox

msgbox_03

Public Sub test1()
    sInput = MsgBox("Are you sure to delete the data?" & Chr(10) & "Data will be deleted permanently")
End Sub

Outbound References

http://www.techonthenet.com/excel/formulas/char.php

Excel VBA Msgbox and handling of return value

This Excel tutorial explains how to use Excel VBA Msgbox with carriage return such as vbCr, vbNewLine, and handling of return value such as vbOK, vbCancel, vbRetry

You may also want to read:

Excel VBA Inputbox and Application.Inputbox

What is Excel VBA MsgBox?

Excel VBA MsgBox is a prompt box that is used to prompt user for important information, usually provide user with OK and Cancel buttons. Note that there is another prompt box called InputBox which receives text input from user but it is not in the scope of this article.

Why do you need Excel VBA MsgBox?

There are several reasons that you may need Excel VBA Msgbox:

1) When you run a Sub or a Function, you will see the final result only and the result may not be what you expect while there is no error message. In that case, you can insert a Msgbox code within a Procedure to check the value of a variable at specific line of code.

2) To warn user or confirm an action, such as asking users if they confirm to delete data

Syntax of Excel VBA MsgBox

MsgBox(prompt[, buttons] [, title] [, helpfile, context])
PromptRequired. String expression displayed as the message in the dialog box. The maximum length of Prompt is approximately 1024 characters, depending on the width of the characters used. If Prompt consists of more than one line, you can separate the lines using

1) carriage return – Chr(13) or vbCr OR

2) line feed – Chr(10) or vbLf OR

3) carriage return + line feed – Chr(13)& Chr(10) or vbCrLf  OR

4) vbNewLine, suitable for different platforms

ButtonsOptional. Numeric expression that is the sum of values specifying the number and type of buttons to display, the icon style to use, the identity of the default button, and the modality of the message box. You can add more than one Buttons using + sign, such as vbOKCancel + vbExclamationmsgbox_02

If you omit Buttons, the default value is zero (The user must respond to the message box before continuing work in the current application.)If you choose a Button, you need to define a variable to return a value, otherwise you will receive the following error message.msgbox_07

The return values are as follows

Return ValueValue
vbOK1
vbCancel2
vbAbort3
vbRetry4
vbIgnore5
vbYes6
vbNo7

 

TitleOptional. String expression displayed in the title bar of the dialog box.msgbox_01

If you omit Title, the application name (Microsoft Excel) is placed in the title bar.

 helpfile Optional. String expression that identifies the Help file to use to provide context-sensitive Help for the dialog box. If helpfile is provided, context must also be provided.
 context Optional. Numeric expression that is the Help context number assigned to the appropriate Help topic by the Help author. If context is provided, helpfile must also be provided.

 

Buttons Argument (blue is about Modality)
MemberValueDescription
vbAbortRetryIgnore2Display Abort, Retry, and Ignore buttons.
vbApplicationModal0The user must respond to the message box before continuing work in the current application.
vbCritical16Display Critical Message icon.
vbDefaultButton10First button is default.
vbDefaultButton2256Second button is default.
vbDefaultButton3512Third button is default.
vbDefaultButton4768Fourth button is default.
vbExclamation48Display Warning Message icon.
vbInformation64Display Information Message icon.
vbMsgBoxHelpButton16384Adds a Help button to the message box
vbMsgBoxRight524288Text is right aligned
vbMsgBoxRtlReading1048576Specifies text should appear as right-to-left reading on Hebrew and Arabic systems
vbMsgBoxSetForeground65536Specifies the message box window as the foreground window
vbOKCancel1Display OK and Cancel buttons.
vbOKOnly0Display OK button only (this is the default).
vbQuestion32Display Warning Query icon.
vbRetryCancel5Display Retry and Cancel buttons.
vbSystemModal4096System modal; all applications are suspended until the user responds to the message box
vbYesNo4Display Yes and No buttons.
vbYesNoCancel3Display Yes, No, and Cancel buttons.

Example of Excel VBA MsgBox

Step 1 – MsgBox with two lines of message

Public Sub test1()
    sInput = MsgBox("Are you sure to delete the data?" & vbNewLine & "Data will be deleted permanently")
End Sub

msgbox_03

Step 2 – Add OK / Cancel Button

Public Sub test2()
    sInput = MsgBox("Are you sure to delete the data?" & vbNewLine & "Data will be deleted permanently", vbOKCancel)
End Sub

msgbox_04

Step 3 – Add exclamation logo

 Public Sub test3()
    sInput = MsgBox("Are you sure to delete the data?" & vbNewLine & "Data will be deleted permanently", vbOKCancel + vbExclamation)
End Sub

msgbox_05

Step 4 – Add message box title

Public Sub test4()
    sInput = MsgBox("Are you sure to delete the data?" & vbNewLine & "Data will be deleted permanently", vbOKCancel + vbExclamation, "Warning")
End Sub

msgbox_06

Step 5 – Condition on return value

Public Sub test5()
sinput = MsgBox(“Are you sure to delete the data?” & vbNewLine & “Data will be deleted permanently”, vbOKCancel + vbExclamation, “Warning”)
    If sinput = vbOK Then
        ‘delete something
    ElseIf sinput = vbCancel Then
        ‘no action
    End If
End Sub

In the above example, we have OK and Cancel Buttons in MsgBox. If you click on the red cross on the top right, the return value is same as clicking on Cancel Button.

If your MsgBox contains only 1 button, clicking on the red cross is same as clicking on the only button, therefore red cross doesn’t necessarily mean Cancel.

 Outbound References

https://msdn.microsoft.com/en-us/library/office/gg251821%28v=office.15%29.aspx