The Excel Blog

Tuesday, December 20, 2016

What is VBA ?

VBA stands for Visual Basic for Applications. It is a programming language provided by Microsoft, to create applications or develop macros. VBA is a free tool, which comes along with the Microsoft office package, which we can use to develop programs in Excel, Word or PowerPoint platform.

Here VBA is widely used with Excel to create macros to automate the repetitive tasks. Tasks which consumes lot of manual efforts, which can be automated using the VBA macro. We can write a set of instructions in VBA code to execute the repetitive tasks, fast and more accurately without any manual intervention. Few people also call these macros as Robots. which do their tasks without resting and they can rest for the time the robot is doing their job :-).

Please note that VBA and VB are different from each other. By using VB (Visual Basic), you can create applications which are standalone and executable. While VBA needs support of Microsoft office applications to develop and run macros.

Excel from Microsoft office is widely used across globe, from day to day tasks to complex data processing. To execute these tasks using the macro (vba code), we can execute many manual tasks such as:

Formatting the data in specific formats for all sheets in workbook
-formatting your monthly reports for specific format and arrangement of the data for presentation purpose. Creating charts and formatting colors as per the report requirements. This can be done by writing set of instructions and executing them with click of a button.
Automating the repetitive task
-If you need to collate data from 20 different excel files on daily basis and put it in a template file. Then format this data as per required output. This task can be automated by the macro. For the first time you can record the macro while collating the data, The next time you would just need to execute the macro with one click and all the manual work will be completed by the automated instructions.
Inserting frequently used text
-Say while preparing reports, you need to insert few texts in a standard format for all reports you create, Here we can write macro and assign a shortcut button for it, by pressing which the macro will insert the required text for you. e.g. The product description headers. This will reduce your typing or copy paste time and eliminate the error of manual mistake.
Creating complete vba application
-To create a complete vba application would definitely take some time to understand the coding and input requirements. e.g. you can develop a complete conference room manager application using Excel VBA code, which can be used by the users to book an conference room.
Creating custom functions in VBA
- Apart from the standard functions (sum, count and more) available in Excel, we can create custom functions as per our requirements, very easily using the VBA code. e.g. say we are creating a home loan emi calculator, we can create a functions which will give us the output, based on few inputs for calculations.

There is a large scope of developing macros, automating the complex processes, generating reports and much more. It all depends on how creative you think to solve the problems. That's all for now, about VBA.

Thank you for reading.

You can also, check out my below posts:

Monday, October 10, 2016

How to view excel data table on VBA Form ?

There may be a requirement to view data on excel VBA form, in case you are building an Excel VBA application.

This data might be used to view a list of points to cross audit or adding a check if your data is updated correctly and many more audit options.

In order to create a sample form, we need prepare a data input. You can copy paste the below table to your excel sheet and follow the steps, below.

1. Open excel workbook and copy the below table to sheet1

User Name	Communication	Leave Management	Training Score	Productivity	Quality
ABC	10	3	94	56	98
DEF	10	4	42	49	91
GHI	8	4	100	60	91
JKL	3	9	40	60	90
MNO	1	3	52	62	95
PQR	8	5	74	45	85
STU	10	6	74	65	96
VWX	10	3	51	94	95
YZA	9	8	72	61	93

2. As per above table our data is in Sheet1, within range from A1 to F10.

3. Press Alt + F11 and open the VBA editor window

4. Right click on Microsoft Excel Objects and Click on Insert --> Module. For more details check out my post on How to use Visual Basic Editor in Excel VBA ?

5. Right click on Microsoft Excel Objects and Click on Insert --> UserForm

6. A new UserForm will be inserted, adjust the length of the UserForm as required to view the 6 columns.

7. Now add a list box from the VBA Editor Tool Box window. (If you are not able to find the tool box on screen, then Go to View menu on the top-->Click on Toolbox). Adjust the size of the list box to fit the 6 columns of the data table.

8. Add a Command Button on the form we have created. The code to view the data in list box, will go into this command button.

9. Double click on CommandButton1 and this will take us to the vba editor screen of the form. We will be able to see the below code:

Private Sub CommandButton1_Click()

End Sub

10. Copy paste the below code in the CommandButton1 click event.

--------------------------------------------------------------------------------------------------------------------------

Private Sub CommandButton1_Click()

With ListBox1
.ColumnCount = 6
.ColumnWidths = "50;70;90;70;70;50"
.RowSource = Sheet1.Range("A1:F20").Address
End With

End Sub

--------------------------------------------------------------------------------------------------------------------------

11. How this works ?

--------------------------------------------------------------------------------------------------------------------------
Private Sub CommandButton1_Click()

' below code will work on the Listbox1, which we have added on the form
With ListBox1

' We can change the number of the columns we need to view in the list box. In this case we need to view 6 columns.
.ColumnCount = 6

'Below code defines what will be the column width. We can adjust this column width based on the data requirement.
.ColumnWidths = "50;70;90;70;70;50"

'In below line we are providing our table data range, which needs to be populated in the Listbox1
.RowSource = Sheet1.Range("A1:F20").Address

End With

End Sub

--------------------------------------------------------------------------------------------------------------------------

12. Once the code is added, Click on Run button on top menu or press F5 on keyboard.

13. We will be able to see the below form. Here click on command button.

14. Once we click on command button, we will be able to see the below result.

15. The list box here, is used to populate the table data in tabular format. We provided the # of columns, column width and the data range source to the list box. The list box provide an horizontal and vertical scrollbars, based on the data range.

Please feel free to customize this code as per the requirements.

Also check out my posts on :

Saturday, October 8, 2016

How to protect vba module in excel ?

If you are building a macro and don't want any one to modify it. You have an option in Excel VBA editor, using which you can protect your VBA module code.

Open a new excel file

Go to Visual Basic Editor Window.

Insert new module and add a sample code to it.

Sub SampleCode()

Msgbox "This is a sample code we need to hide !", vbInformation

End Sub

Follow the below steps to protect the VBA module:

Click on Tools

Click on VBA Project Properties

The VBA object properties window will pop up. On this window, select the Protection Tab.

Click on Lock Project for viewing check box

Enter the password of your choice in two fields, Password and Confirm Password.

Click OK and we have just protected the VBA module. Save and close the workbook. Open it again to check the protection.

Go to VBA module, all modules will be hidden and if you double click on it will ask you the password to unprotect.

Also check out my posts on :

How to super hide excel sheets using VBA code ?

If you want to super hide the worksheets in excel, excel has an very hidden property. When the sheets are very hidden. When you right click and try to unhide such sheets, the unhide view gets disabled. As there will be no worksheets to view.

Use the below steps to test the very hidden property of the excel sheets.

Open new worksheet

Press Alt + F11 to go to VBA editor window

Insert new module from the project explorer

Copy and paste the below code to try out:

Sub SuperHide()

'This property will super hide the sheets
Sheet1.Visible = xlVeryHidden

End Sub

We will not be able to unhide this sheet1 from excel window. For that we would need to write a vba code for unhide.

Sub UnHide()

'This property will unhide the sheets. True over here reprents the xlSheetVisible property
Sheet1.Visible = True

End Sub

This will unhide the sheet and you will be able to see the same in excel window.

You can add multiple sheets in a similar way and use both the hide and unhide codes for different uses. If you don't want to super hide the sheet, you can also use the below code to just hide the sheet.

Sub Hide()

'This property will simply hide the sheets. False over here represents the xlSheetHidden property
Sheet1.Visible = False

End Sub

This will hide the sheets and in this case, you will be able to unhide the sheet from the Excel Window.

You can also use the below keyboard shortcuts to hide or unhide sheets for faster use.
Hide - Alt, O, H, H

Unhide - Alt, O, H, U

Just in case if you don't want any one to go in VBA code and unhide the super hidden sheets. You can protect the VBA module with password of your choice.

Check the post How to protect vba module in excel ? for the same.

Also check out my posts on :

Wednesday, October 5, 2016

How to split or delimit data using Excel VBA code ?

Using the below macro, you can split the text in one cell. This work as a delimiter, without using the Text to column feature available in excel.

You can use the below code, anywhere in your VBA coding project, where there is a requirement to split the text with specific symbol reference.

Below is the sample code with sample data as example.

Open new excel worksheet.

Copy below data in Cell A2 or Sheet2.

#>ABC>DEF>GHIJ

#>GHI>JKL>MNO

#>PQR>STUV>WXY

#>ZABC>DEFG>HIJK

#>LMN>OPQ>RSTU

#>W>XY>Z>TT

here in the above data we will use ">", symbol to split the data.

open the excel window VBA module. (check out my post on

How to use Visual Basic Editor in Excel VBA ?)

Insert new module and copy and paste the below VBA code.

Sub split_text()

'--------------------Define Variables which we are using in the macro-----------------------------
Dim indata As String
Dim i As Integer, j As Integer
Dim out As Variant
Dim endrow As Integer
Dim splitSymbol As String

'----------------------Set values to the variables we need to user---------------------------------------

'Set the split symbol, using which we need to split the data
splitSymbol = ">"

'Find the last row of the data in Sheet 2. Using which we will run a loop starting from row number 2 to end row. In older versions of excel update 65536 instead of 1048576
endrow = Sheet2.Range("A1048576").End(xlUp).Row

' There are 2 loops in below code. First is to loop through the rows of the data and second within the cell to split the data

For j = 2 To endrow

'assign the data to indata variable, which we need to split. here A acts as column and j as a row number
indata = Sheet2.Range("A" & j).Value

'you can directly put symbol in code or assign the value as listed below out = split(indata, ">")
out = Split(indata, splitSymbol)

'The split value is assigned to out. While the UBound function below will detect the maximum number from the out string, which we need to split. i.e. this number will define the number of columns in which data will be populated and the below for loop will run for the same number.

For i = 0 To UBound(out)

'Below code will post the output in the row assigned to j variable and i will act as column
Cells(j, i + 1) = out(i)
Next i

Next j

'This will pop up the message box, once the data split is processed. It will say # of rows data processed sucessfuilly
MsgBox j & " rows of data split process completed sucessfully !", vbInformation

End Sub

Please copy the above code to your vba module and test the same. Do let me know if you want any more information on the same.

Tuesday, October 4, 2016

How to delete the table in MS ACCESS database using Excel VBA code ?

Please add the reference to Microsoft ActiveX Data Objects 2.x Library in the Excel VBA Editor.

see my blog on How to use VBA editor and add references to Excel VBA ?

--------------------------------------------------------------------------------------------------------------------------

Sub Delete_Test_Table ()

'Define the variables to be used in the VBA code

Dim connectdb As String, pathdb As String, connObj As ADODB.Connection

'Assigning the database name and the connection path to the pathdb variable

pathdb = Sheet1.Range("A2").Value

'In the above case the reference is taken from Range A2 of sheet 1, while we can directly add the path in VBA code e.g.

'pathdb = "C:\Users\Default\Desktop\Test.accdb"

'Connection reference and path

connectdb = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & pathdb & ";"

'Connecting to the database and creating new table

Set connObj = New ADODB.Connection

With connObj

.Open connectdb

.Execute "drop table TEST_TABLE"

End With

' Table with name "TEST_TABLE" will be deleted from the database

End Sub

Please note that this function will permanently delete the table and its content, which will be nor more recoverable.

Please use this function with caution.

If you check out my other post regarding, How to create table in MS Access using Excel VBA, you will find that only the .Execute line is changed. Anything you enter withing the double quoted after .Execute "" will be executed on the database by the VBA code.

This concludes that, you can use the above connection method and procedure to execute different queries like UPDATE DATA, DELETE DATA, EXPORT DATA and more.

Create a function to delete only the visible rows after data filter ?

You can copy paste the below function in VBA editor and test it yourself with different columns and criteria.

Function delvisiblerows(col, criteria)

ActiveSheet.Range("$A:$AI").AutoFilter field:=col, Criteria1:= criteria

Set Rng = Application.Intersect(ActiveSheet.UsedRange, ActiveSheet.Range(Cells(2, 1), Cells(1048576, 100)))

If ActiveSheet.AutoFilter.Range.Columns(1).specialcells(xlCellTypeVisible).Cells.Count - 1 > 0 Then
Rng.EntireRow.Delete
End If

ActiveSheet.AutoFilterMode = False

End Function

How this works ?

ActiveSheet.Range("$A:$AI").AutoFilter field:=col, Criteria1:="<>" & criteria

here we pass the column number and the criteria values from the macro routine.

This is then used around the If statement above. If any row is filtered as per the criteria match, then only the visible rows will be deleted.

The important function to remember here is .specialcells(xlCellTypeVisible).Cells property available in the excel vba.

e.g.
Say you have different user names in column A and you want to delete the data for user "Suresh", you can pass the values in macro as below.

Sub DeleteVisibleRows()

Call delvisiblerows(1, "Suresh")

MsgBox "Data for Suresh Deleted Sucessfully!", vbInformation

End Sub

_________________________________________________________________________________

Happy learning !

Also check out my post on how to find first and last date of any month.