How to get Inputs from User in VBA Script ? & How to give Message to User?

In this blog we will learn InputBox and MsgBox in VBA. Input Box use to get user inputs in between a VBA code execution. However MsgBox use to give Message to user while user Running a program.

Followus

1. VBA InputBox

The InputBox function provides the functionality  of entering values by user. When user Run the VBA Program user got a input Box opened between  program. Now user require to enter input as asked by programmer and press Ok Button. Once user pass his input and press on ok button program start executing next coding steps. Refer Below image to under stand the process.

In above image we can see user input Box , where user require to enter input number and press on ok button. Now take a look on below image where user input is 1. Now user needs to press on ok button.

Followus

Click Bank Offers

Let see what happened when user entered 1 number and press on ok button. we got entered 1 number in Range("A1"). But Why ? Take a look on VBA Code which we wrote in Visual Basic Editor. Here in Code we mentioned IF condition code and we wrote (Range("A1").Value = i) on if condition is True. However on False condition we wrote (Range("A1").Value = "Exit from code").

You can Visit on  Our IF Conditions blog to learn.

Let see When user enter > 1 Number in Input Box. As per written code in Visual basic editor. False condition will work and it will entered (Exit from code) text in Range("A1").

1. VBA Message Box (Msg Box)

MsgBox is a dialog box which is use to show messages to our users as per our requirements. Here we are taking basic examples of MsgBox. we are using Input Box and Msgbox in below example. Take a look on below image.

When we will run above code . It will ask to enter input to user as we already explained in Input Box. As per user input we passed two messages for user. Let see below images to understand the execution process of VBA Code. 

Followus

Click Bank Offers

When user enter 1 number in input box If condition's logical test result will be True , So that Msg Box result will print (User wants to continue)as below image.

Let see what happened if user enter > 1 number (example user enter 5 number) in input Box.

As per written code in Visual basic editor. False condition will work and it will give (Exit from code) text in Msg Box as a program output.

Followus

Click Bank Offers

Thanks for visiting @ Bexpertadvexel.com Blogs

Click on the Link for Join us : Register_Course

We at BeXpert Advanced Excel offer state of the art Advanced Excel Coaching at Home. Even corporate professionals comes to us, for Advanced Excel Training in Gurgaon for the same state of the art database management education.

Other Services : We provide Automated Tools for reporting and analysis your business Data Automatically using some simple steps in User from and Command buttons.

You can place Your order on Fiverr bexpertadvexcel

I will prepare a data bank user form for you using macros

I will scrap Justdial data for you

Websete : learnvbabigners.blogspot.com For Any Inquiry call us : +91-8802329198

Variables (Data Types ) in VBA ? 

In this blog we will learn about Variables so called Data Types. Generally we know that every language have data types for develop codes. MS Excel have 3 Data input types (Number, Text, References) However VBA have 2 Data input types (Text and Numbers). Rather than a number everything is Text in VBA. 

Followus

1. Data Types

A. Integer

B. Single 

C. Long

D. LongLong

E. Object

F. String

G. Boolean

A. Integer

Integer is a Data Type which we assign to the integer variables. Its a Data type which can hold whole numbers without Decimals in between of Range (-32767 To 32767). Integer Data types hold 2 bytes of memory in hard disk which is half of the Long Data type. Default value of a integer variable is 0 look at below image.

Code :

Sub IntegerDataTypeDeclaration()

Dim i As Integer

End Sub

B. Single

Single is a Data Type which we assign to the decimal variables. Its a Data type which can hold Decimal numbers in between of Range -3.4028235E+38 To -1.401298E-45 for negative values and 1.401298E-45 To 3.4028235E+38 for positive values. Single Data types hold 4 bytes of memory in hard disk which is 2X of the integer Data type. Default value of a Single variable is 0 look at below image.

Code :

Sub SingleDataTypeDeclaration()

Dim i As Single

End Sub

C. Long

Long is a Data Type which is capable to store very long data values (-2,147,483,648 to 2,147,483,648). Long only can store whole numbers (without Decimal numbers). Long variable hold 4 bytes storage of hard disk which is 2X of the integer variable. Default value of a Long variable is 0 look at below image.

Code :

Sub LongDataTypeDeclaration()

Dim i As Long

End Sub

D. LongLong

LongLong is a Data Type which is capable to store very very long data values ( -9,223,372,036,854,775,808  To  9,223,372,036,854,775,807.). LongLong only can store whole numbers (without Decimal numbers) and its available only in 64 bit version of Microsoft Office. Long variable hold 8 bytes storage of hard disk which is 2X of the Long variable. Default value of a LongLong variable is 0^ look at below image.

Followus

Code :

Sub LongLongDataTypeDeclaration()

Dim i As LongLong

End Sub

E. Object

Object is a Data Type which is capable to store any Object or its methods. It hold address that refers to Object. We can assign any reference type (String, Array , Class or interface) to an Object variable . An Object variable also can hold data of any data type (Boolean, Integer, String,  Date or structure). The default value of an Object is nothing ( A Null Reference). An Object data type is a reference type however in VBA and an Object treat a value type when it refers to data of value type. Default value of a Object variable is Nothing look at below image.

Code :

Sub ObjectDataTypeDeclaration()

Dim obj As Object

End Sub

F. String

There are two types of string variables in VBA, Fixed Length and Variable length. The Fixed length variables can store string of 1 To 65400 length. The Maximum length is 65535. However Variable length string data type can store string of 0 To 2 billions length. 

Fixed Length String

In the fixed length string variable declaration we can fix length of string. refer below example code to understand the same. we are taking 30 length of string on declaration time. So This string variable will be able to hold on 30 length string. Default value of a Fixed Length String variable is "0000..." look at below image.

Code :

Sub FixedLengthStringDataTypeDeclaration()

Dim FixedLength As String * 30

End Sub

Variable Length String

Variable length string can hold 0 To 2 Billions characters string. Default value of variable string is empty (""). So String variable do automatically increase or decrease variable size as per inputs. A variable length string can hold string of any length. Code for declare string variable is as mentioned below. Default value of a Variable Length String variable is "" look at below image.

Code :

Sub VariableLengthStringDataTypeDeclaration()

Dim varstr As String

End Sub

G. Boolean

Boolean is variable in VBA which can only hold Boolean values (TRUE or FALSE). Its also a inbuild data type in VBA. This use for hold only logical references or logical variables which is use for logical comparison. Default value of  a Boolean variable is FALSE, The declaration of this data type is as mentioned below. 

Followus

Code :

Sub BooleanDataTypeDeclaration()

Dim bln As Boolean

End Sub

Thanks for visiting @ Bexpertadvexel.com Blogs

Click on the Link for Join us : Register_Course

We at BeXpert Advanced Excel offer state of the art Advanced Excel Coaching at Home. Even corporate professionals comes to us, for Advanced Excel Training in Gurgaon for the same state of the art database management education.

Other Services : We provide Automated Tools for reporting and analysis your business Data Automatically using some simple steps in User from and Command buttons.

You can place Your order on Fiverr bexpertadvexcel

I will prepare a data bank user form for you using macros

I will scrap Justdial data for you

Website : www.bexpertadvexcel.com For Any Inquiry call us : +91-8802329198

What is hierarchy of Objects in Excel VBA  and Shape Object?

In this blog we will learn hierarchy of  VBA Objects and will discuss about Shape Object. Let's see in below image example of Shape Object. Shape Object use to create shapes in Excel as per our requirements. Here we are taking example of a rectangle shape.  

Followus

Code

Sub ShapeObjectinVBA()

With Worksheets(1).Shapes.AddShape(msoShapeRoundedRectangle, _

150, 70, 70, 80)

.Name = " ShapeObjectinVBA"

End With

End Sub

Sub SelectShapesinActiveExcelSheet()

ActiveSheet.Shapes.SelectAll

End Sub

Output : Refer first image to see created rectangle its created after Run the first code. Also take look on created rectangle in first image for second code out put when we run the selector code it select the created image automatically.

 

Excel VBA Hierarchy model (Nested use of Objects)

Here we are taking a example to understand the hierarchical use of VBA Objects . Let's assume we need to select a Range("D4") in a Sheet1 of  Workbook (Book1) .

Code

Sub HierachicalUseofVBAObjects()

Workbooks("Book1").Worksheets("Sheet1").Range("D4").Select

End Sub

Output

After Run Above code we got selected D4 Range in Sheet1 of  Book1 Workbook. Let's fill the Range D4 with 100 Number using a VBA code. Take a look on below image.

Followus 

Thanks for visiting @ Bexpertadvexel.com Blogs

Click on the Link for Join us : Register_Course

We at BeXpert Advanced Excel offer state of the art Advanced Excel Coaching at Home. Even corporate professionals comes to us, for Advanced Excel Training in Gurgaon for the same state of the art database management education.

Other Services : We provide Automated Tools for reporting and analysis your business Data Automatically using some simple steps in User from and Command buttons.

You can place Your order on Fiverr bexpertadvexcel

Click Bank Offers

I will prepare a data bank user form for you using macros

I will scrap justdial data for you

Website : www.bexpertadvexcel.com For Any Inquiry call us : +91-8802329198

What is Objects in Excel VBA ?

The Excel Application itself is a Object. As we know every thing is Object which is actionable. So MS Excel also have some Objects like workbook, worksheets, Range etc. We are listing some types of Objects below in MS Excel.

Followus

1. Application Object

In below image you can see when we write (Application. ) keyword in visual basic editor we got entire excel objects . We can use these all excel objects with Application. Object accordingly we are taking example of  Application.ActiveSheet.Range Object. 

A. Code

Sub Application_ActiveSheet()

Application.ActiveSheet.Range("A1").Value = 100

End Sub

B. Output

We click Run button for Run the written code. Refer Cell A1 we got result in value 100 in A1 Range.

2. Workbooks Object

In below image you can see when we write (Workbooks. ) keyword in visual basic editor we got entire excel objects . We can use these all excel objects with Workbooks.  Object accordingly we are taking example of  Workbooks.Add  Object. We can add new workbook using Workbooks.Add code.

Followus

A. Code

Sub workbook_object()

Workbooks.Add

End Sub

B. Output

We click Run button for Run the written code. We got a new Workbook created Name Book2.

3. Workbook Object

The workbook Object use to manage worksheets collections. For Active workbook we use ActiveWorkbook. Object use to work on Active workbooks. Here we taking example of  ActiveWorkbook.Save. Below code will save Active workbook.

A. Code

Sub workbook_object()

ActiveWorkbook.Save

End Sub

B. Output

We click Run button for Run the written code. Active workbook will be saved on default location.

4. Sheets Object

In below image you can see when we write (Activeworkbook.Sheets. ) keyword in visual basic editor we got entire excel objects related to Sheets Object. We can use these all excel objects with ActiveWorkbook.Sheets. Object accordingly. 

We are taking example of  ActiveWorkbook.Sheets.Add . We can Add new worksheet in active workbook using this Object.

A. Code

Sub Sheets_Object()

ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Worksheets(Worksheets.Count), Count:=1, _

Type:=xlWorksheet).Name = "Newsheet"

End Sub

B. Output

We click Run button for Run the written code. New sheet added in Active workbook having Name (Newsheet)

Followus

5. Worksheet Object

Worksheet Object work with Worksheets of entire excel workbooks. We use Object Activeworkbook. for work with Active workbooks. Let;s see below example for Copy paste Data of Range("A1:B3") at Range("D4") .

A. Code

Sub Activeworkbook()

ActiveSheet.Range("A1:B3").Copy

ActiveSheet.Range("D4").Select

ActiveSheet.Paste

End Sub

B. Output

We click Run button for Run the written code. Range ("A1:B3") Data copy and pasted in Range("D4:E6").

6. Worksheets Object

In below image you can see when we write (Worksheets. ) keyword in visual basic editor we got entire excel objects related to Worksheets. Object. We can use these all excel objects with Worksheets. Object accordingly.

We are taking example of  Worksheets.Add . We can Add new worksheet in active workbook using this Object.

A. Code

Sub Activeworkbook()

Worksheets.Add

End Sub

B. Output

We click Run button for Run the written code. New sheet added in Active workbook having Name (Sheet3)

Followus

Thanks for visiting @ Bexpertadvexel.com Blogs

Click to Donwood Macro file Here

Click on the Link for Join us : Register_Course

We at BeXpert Advanced Excel offer state of the art Advanced Excel Coaching at Home. Even corporate professionals comes to us, for Advanced Excel Training in Gurgaon for the same state of the art database management education.

Other Services : We provide Automated Tools for reporting and analysis your business Data Automatically using some simple steps in User from and Command buttons.

You can place Your order on Fiverr bexpertadvexcel

I will prepare a data bank user form for you using macros

I will scrap justdial data for you

Website : www.bexpertadvexcel.com For Any Inquiry call us : +91-8802329198

Visual basic Editor

How to use Visual basic Editor for Create Macros (codes) in MS Excel VBA

1. How to Open Visual Basic Editor

In this blog we will learn about visual basic editor, So first we click on developer tab in MS Excel Ribbon. Here we find a sub tab ( Visual Basic). Now we click on Visual Basic tab for access visual basic editor.

Followus

2. Insert Module:

Now we find a Insert tab in Visual Basic editor. Here we click on Insert tab and got a list. After that we click on module tab. Refer below 2nd Image we got inserted module for write VBA code.

3. Can we write code in This Workbook and Sheet1 Objects

Yes we can write VBA code in This workbook and sheet 1 and sheet 2 etc also. We do write codes in Sheets when we want get execute our code on some events like (with Open file, close file, etc). Normally we write codes in modules.

4. Properties Window

Using properties window we can change Name of inserted module . We changed name (computation) of inserted module. See in project explorer New assigned Name has been changed.

5. Run , Break and Reset Button in Visual basic Editor 

We use Run button for execute the VBA Macro (code) for perform task and see results. Break button (Ctrl + Break) we use to break VBA Code in between while it is running. Reset Button we use to get exit from running mode if code gave error and stopped in between or we got entered wrongly in interpretation mode.

Followus

See below image when code is in Interpretation mode.

Thanks for visiting @ Bexpertadvexel.com Blogs

Click to Donwood Macro file Here

Followus

Click on the Link for Join us : Register_Course

We at BeXpert Advanced Excel offer state of the art Advanced Excel Coaching at Home. Even corporate professionals comes to us, for Advanced Excel Training in Gurgaon for the same state of the art database management education.

Other Services : We provide Automated Tools for reporting and analysis your business Data Automatically using some simple steps in User from and Command buttons.

You can place Your order on Fiverr bexpertadvexcel

I will prepare a data bank user form for you using macros

I will scrap justdial data for you

Websete : www.bexpertadvexcel.com For Any Inquiry call us : +91-8802329198

Excel VBA - Record a Macro

How to Record First Macro in Excel VBA

1. Start Recording of Macro :

In this blog we will learn how to record Macro in MS Excel VBA. First we click on Developer Tab in Excel Ribbon. After That we click on Record Macro tab.

Followus

2. Assign a Macro Name and Short Key:

Now we do write a relevant Name for our Macro and assigned a short key ( Note: Short key would be new one else it will be replaced and it will impact excel basic short key's uses). Take a look on below image how we have added Name and short key. After fill Required details we clicked on Ok button for start recording.

3. Start Recording:

Now VBA Macro recording is in progress and its recording steps whatever we are doing in Excel workbook manually. We have written Hello World in Range("A1") at Sheet 1. Now this process is recorded in VBA lets See Visual Basic Code in Visual Basic Editor.

4. Drag a Command Button for Execute the Recorded Code:

We clicked on Developer Tab and dragged a command button from insert Tab. Also did some Color and formatting for make this attractive. 

Followus

5. Rename Command Button:

We Renamed the dragged button (Run Macro).

6. Assign Macro to Command Button :

we assigned recorded macro to Run macro (Command Button).

7. Run The Recorded Macro:

We Run recorded macro by clicking on Run Macro Command Button.

8. Out put after Run Macro:

After Run recorded we got printed Hello World in Range("A1") automatically.

Followus

Thanks for visiting @ Bexpertadvexel.com Blogs

Click to Donwood Macro file Here

Click on the Link for Join us : Register_Course

We at BeXpert Advanced Excel offer state of the art Advanced Excel Coaching at Home. Even corporate professionals comes to us, for Advanced Excel Training in Gurgaon for the same state of the art database management education.

Other Services : We provide Automated Tools for reporting and analysis your business Data Automatically using some simple steps in User from and Command buttons.

You can place Your order on Fiverr bexpertadvexcel

I will prepare a data bank user form for you using macros

I will scrap justdial data for you

Website : www.bexpertadvexcel.com For Any Inquiry call us : +91-8802329198