Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Find function

Think you have a spread sheet similar to this. It contains value "100" in several cells.

So today I will teach you how to get the address of those cells from VBA code

First let's define the value we need to search.

Dim s1 As String

Then assign the value we search to that variable.

s1 = "100"

And it is a good practice to use objects where you can. It prevent occurrence of errors. Think you have worksheet call "Report". If you use conventional way you need to refer that sheet by worksheets("Report") at every where you needed to refer it. But if you use worksheet object you can refer it from very short name. So this will give neat look to your coding as well. So I prefer using objects. So use worksheet objects as follows.

Dim WS As Worksheet
Set WS = Worksheets("sheet1")

Then Set the range we need to search.

With WS.Range("A1:T23")

Following line of code will give you the address of each cell having 100 in it.

MsgBox c.Address

Loop will continue until it find a value for c except first value.

Loop While Not c Is Nothing And c.Address <> firstAddress

So here is the full code. You can use this wherever you need by changing the sheet names, range and search values accordingly. Also you can pass the search value as a variable.

Dim s1 As String
s1 = "100"
Dim WS As Worksheet
Set WS = Worksheets("sheet1")
With WS.Range("A1:T23")
    Set c = .Find(s1)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            MsgBox c.Address
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

With the above code you will get the cell address of cells having "100" to msgboxes.

Populate combo box with sheet name of opened work book

Here cboFromSheeet is the name of the combo box.

Dim wbFrom As Workbook
Set wbFrom = Application.Workbooks.Open(lblFromFilePath.Caption)
Dim ws As Worksheet

'Clear the combo box
cboFromSheeet.Clear

With cboFromSheeet
    For Each ws In Worksheets
            .AddItem ws.Name
    Next ws
End With

cboFromSheeet.Text = "Select a Sheet"

Add work sheet with a name

Think you want to add a worksheet with a name "Report"

This code will do it.

Sheets.Add.Name = "Report"

However you can do this even with objects

See following code

Dim WS as Worksheet
Set WS = Sheets.Add

You can refer this new sheet by WS wherever at the code. You don't need to know anything about like it's name, location etc. Even you can give it a name later.

For an example think you want to name it as "Calculation". So below code will do it.

WS.Name = "Calculation"

Find last row having data in a certain column

    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

reference workbooks and sheets Explicitly

This example shows how to reference workbooks and sheets explicitly.

Dim wbFrom As Workbook
Dim wbTo As Workbook
Dim wsFrom As Worksheet
Dim wsTo As Worksheet
Dim Str1, Str2, s1, s2, s3, s4 As String
'paths of 2 workbooks
Str1 = lblFromFilePath.Caption
Str2 = lblToFilePath.Caption
Dim WrdArray() As String
WrdArray() = Split(Str1, "\")
's1 and s2 are Workbook names
s1 = WrdArray(UBound(WrdArray()))
WrdArray() = Split(Str2, "\")
s2 = WrdArray(UBound(WrdArray()))
'Worksheet names
s3 = cboFromSheeet.Value
s4 = cboToSheet.Value

Set wbFrom = Workbooks(s1)
Set wbTo = Workbooks(s2)
Set wsFrom = wbFrom.Worksheets(s3)
Set wsTo = wbTo.Worksheets(s4)

If cboFromSheeet.Text <> "Select a Sheet" And cboToSheet.Text <> "Select a Sheet" Then
    MsgBox wsFrom.Name, , "from"
    MsgBox wsTo.Name, , "To"
Else
    MsgBox "Please select relevant sheets from the drop down menu.", vbOKOnly, "WARNING!"
End If

Browse file and put path+name and name in two labels

Dim strFile As String
strFile = Application.GetOpenFilename("Excel workbooks,*.xls*")
If strFile = "False" Then
    ' if the user clicked Cancel
    'Here nothing will happen
Else
    'If the user selected a file; both its path and name is in strFile
    'So we store it in lable caption. you can set visibility of lable to false
    lblFromFilePath.Caption = strFile
    Dim WrdArray() As String
    WrdArray() = Split(strFile, "\")
    'here it will show the file name
    lblFromFile.Caption = WrdArray(UBound(WrdArray()))
End If

You can use below line to browse any type of file.

strFile = Application.GetOpenFilename()

Call each and every sheet of a workbook

Dim ws As Worksheet
For Each ws In Worksheets
    ws.Protect Password:="data123"
Next ws

Open a closed work book

Application.Workbooks.Open ("E:\Blogger Folder\My Files\Folder1\Calculations.xls")

Save excel file as CSV file

ActiveWorkbook.SaveAs Filename:= _
"c:\MyFile.csv", FileFormat:=xlCSV _
, CreateBackup:=False

Replace string inside a string by another string

Dim s1,s2,s3 As String
s1 = "abcabcabc"
s2 = Replace(s1, "a", "b")
s3 = Replace(s1, "bc", "a")

You will get below answers

s2= bbcbbcbbc
s3= aaaaaa

Dealing with word documents

Open excel work book.

press Alt+F11

tools > References >

put tick on "Microsoft word 15.0 object library"

Write below code

    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Set wApp = CreateObject("Word.Application")
    wApp.Visible = True

'Here only the word application will be opened.

if you put following line next a new word document will be created.

Set wDoc = wApp.Documents.Add

If you want to open a document in your hard drive ( (Location -D:\VBA LAB\Result.docx) ) you need to put following code

  Set wDoc = wApp.Documents.Open("D:\VBA LAB\Result.docx")

Get path of a workbook

Following code will show the path of the active work book in a message box

MsgBox ActiveWorkbook.Path

Clear the value of a text box when click on it

Some times we need to clear the text box when click on top of it.

Ex - Enter date to a text box which displays "Date" on it.

So you have to put the code to MouseUp event of the text box. Below is the code for a date text box

Private Sub txtBDate_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

If txtBDate.Text = "Date" Then
    txtBDate.Text = ""
End If

End Sub

Add data set to next empty row of a excel database

Private Sub cmdEnter_Click()

Dim WS As Worksheet
Set WS = Worksheets("Data")

'find the end of column A
Dim x As Integer
x = 1
Do Until WS.Range("A" & x).Value = ""
     x = x + 1
Loop
'last row having data = x-1

'Add data to database
        WS.Range("A" & x).Value = txtJN.Value
        WS.Range("B" & x).Value = txtCus.Value
        WS.Range("C" & x).Value = txtSup.Value
        WS.Range("D" & x).Value = txtPO.Value
        WS.Range("E" & x).Value = txtDD.Value

        ActiveWorkbook.Save

        MsgBox "Data Saved Successfully"


'Clear text and combo boxes

txtJN.Value = ""
txtCus.Value = ""
txtSup.Value = ""
txtPO.Value = ""
txtDD.Value = ""

End Sub

Add names of all open workbooks to a combo box list

Here cboCsvFileName is the name of the combo box

Dim wb As Workbook

For Each wb In Application.Workbooks
    With cboCsvFileName
        .AddItem wb.Name
    End With
Next wb

Function to returns column name from col number

This function will return column name if you input column number.

for example if you want to get column name of 100 th column

you can use ReturnColumnName(100)

Answer will be CV

Function ReturnColumnName(ByVal number As Integer) As String
    ReturnColumnName = Split(Cells(, number).Address, "$")(1)
End Function

How to get a discription of an error

On Error Resume Next
-------------------------------
Your code goes here.( ex: Mail.Send)
-------------------------------
If Err.Number <> 0 Then
    MsgBox Err.Description, vbCritical, "There was an error"
    Exit Sub
End If

Delete a row, rows, column or columns

Below code will delete the 6 th row from the activesheet

Rows(6).Delete

This will delete the 3 rd column (Column "C")

Columns(3).Delete

Cell address of a cell having a specific data

MsgBox Cells.Find(What:="Deposit", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Address

Open save as dialog box with a file name

Some times we need to show save as dialog box for the user. Some times we need to show it with a  file name. Below is a code for that.

Dim IntialName As String
Dim fileSaveName As Variant
InitialName = "Result"
fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _
     fileFilter:="CSV (Comma delimited) (*.csv), *.csv")

Populating 3 combo boxes

Populating 3 combo boxes  cboYBrand, cboYName and cboColor from values from rows 2 to 10 of columns A,B and C

'Populating cboYBrand, cboYName and cboColor
If Worksheets("Database").Range("A2").Value <> "" Then 'if there is atleast one data row
        For i = 2 To 10
            With cboYBrand
                .AddItem Worksheets("Database").Range("A" & i).Value
            End With
            With cboYName
                .AddItem Worksheets("Database").Range("B" & i).Value
            End With
            With cboColor
                .AddItem Worksheets("Database").Range("C" & i).Value
            End With
        Next i
End If

Contact Form

Name

Email *

Message *

Popular Posts