The benefit of Object-Oriented Programming (Class Modules) in VBA.

Many experienced professional VBA developers in the fraternity keep discussing how is OOP going to add value in writing programming? And the supporters of OOP, like me, always give them ideas on how OOP will create reusability of your code, how it will protect (encapsulate) your code from the outer world, etc., etc.

But today I want to push a very clear notion in support of object-oriented programming in VBA.

OOP will let your IMPERATIVE Programming into DECLARATIVE

IMPERATIVE means: To tell computer What to do? and How to do it?

DECLARATIVE means: to tell computer only What To Do!

Once we have designed a class for general methods and functions, our coding in General Module will be DECLARATIVE.

DECLARATIVE programming is fun, readable and neat & clean.

Hope this will help VBA Developers to get their self, practice more seriously in the OOP domain.

#kamalam

Import XML Data in Excel Using VBA

Here is the complete VBA code to import XML Data into the Excel Workbook in a completely dynamic approach. Look at the following video.

Following is the VBA Code

Sub Impor_XML_Data()
    
    'Code By Kamal Bharkahda
    Application.ScreenUpdating = False
    Dim TargetSheet As Worksheet
    Dim ChooseFIle As Variant
    Dim TargetSheetName As String
    Dim TargetCellAddress As String
    
    TargetSheetName = Application.InputBox("Write a Sheet Name where you want to place the XML Data ***Case Sensitive***", "Target Sheet Name")

    TargetCellAddress = Application.InputBox("Write a Cell Address from where XML Data Start Placing", "Target Cell Address")
    
    
    Set TargetSheet = ThisWorkbook.Sheets(TargetSheetName)
    TargetSheet.UsedRange.Clear
    
    ChooseFIle = Application.GetOpenFilename("XML File (*.xml), *.xml", , "Kamal Bharakhda", , False)
    
    If ChooseFIle = vbNullString Then Exit Sub
    
    ThisWorkbook.XmlImport Url:=ChooseFIle, ImportMap:=Nothing, Overwrite:=True, Destination:=TargetSheet.Range(TargetCellAddress)
    
    MsgBox "Import Done"
    
    Set TargetSheet = Nothing
    Application.ScreenUpdating = True
    
End Sub

Using Spin Button, Hide & Show the column one by one in Excel VBA

Show and Hide Columns one by one by Spin button up to mentioned columns limits.

this is going to be the best small tool for excel users.

here is the video. and here is the code of Spin Button.

Private Const DefaltWidth As Integer = 9
Dim TargetSheetName As String
Dim TargetSheet As Worksheet
Dim FirstColNo As Integer
Dim LastColNo As Integer
Dim I As Integer

Private Sub SpinButton1_SpinDown()
    Application.ScreenUpdating = False
    'Assigning Values to the Variables
    TargetSheetName = "Sheet1"
    Set TargetSheet = Application.ThisWorkbook.Sheets(TargetSheetName)
    FirstColNo = TargetSheet.Range("A7").Value
    LastColNo = TargetSheet.Range("A9").Value
    If TargetSheet.Range("A7").Value = Empty Or TargetSheet.Range("A9").Value = Empty Then
        MsgBox "Please Set The  Column Range Limits Before You Proceed", vbCritical, "Kamal Bharakhda"
        Exit Sub
    End If
    'Code For Unhiding Columns One By One from Limit One to Last Column
    For I = LastColNo To FirstColNo Step -1
        If TargetSheet.Columns(I).ColumnWidth = 0 Then
            TargetSheet.Columns(I).ColumnWidth = DefaltWidth
            Exit For 'This is most important point if you want to show columns one by one.
        End If
    Next I
    Set TargetSheet = Nothing
    Application.ScreenUpdating = True
End Sub

Private Sub SpinButton1_SpinUp()
    Application.ScreenUpdating = False
    'Assigning Values to the Variables
    TargetSheetName = "Sheet1"
    Set TargetSheet = Application.ThisWorkbook.Sheets(TargetSheetName)
    FirstColNo = TargetSheet.Range("A7").Value
    LastColNo = TargetSheet.Range("A9").Value
    If TargetSheet.Range("A7").Value = Empty Or TargetSheet.Range("A9").Value = Empty Then
        MsgBox "Please Set The  Column Range Limits Before You Proceed", vbCritical, "Kamal Bharakhda"
        Exit Sub
    End If
    'Code For Hiding Columns One By One from Limit One to Last Column
    For I = FirstColNo To LastColNo Step 1
        If TargetSheet.Columns(I).ColumnWidth > 0 Then
            TargetSheet.Columns(I).ColumnWidth = 0
            Exit For 'This is most important point if you want to hide columns one by one.
        End If
    Next I
    Set TargetSheet = Nothing
    Application.ScreenUpdating = True
End Sub

Working With NAMED Ranges in VBA

1. The code to change the value of each cell of the name range at the same time

Sub Change_the_Value_of_Each_cell_in_Named_Range()
    Dim NameRange As Range
    Set NameRange = Range("SALES")
    Dim Eachcell As Range
    For Each Eachcell In NameRange
        Eachcell.Value = "Sales" & "2017"
    Next Eachcell
    MsgBox "Done"
End Sub

2. Let's Say You have a list of Named Range in Sheet3 where you want to change the value of each Named Range with the Same Format

Sub Change_The_Value_of_Each_Named_Range()
    Dim I As Long
    I = 2
    Dim SheetName As String
    SheetName = "Sheet3"
    Dim TargetRange As Range
    Do Until Sheets(SheetName).Cells(I, 1).Value = vbNullString
        Set TargetRange = Range(Sheets(SheetName).Cells(I, 1).Value)
        TargetRange.Value = Sheets(SheetName).Cells(I, 1).Value & VBA.Format(Sheets(SheetName).Range("B6").Value, "YYYY")
    I = I + 1
    Loop
End Sub

3. TO RENAME THE WHOLE NAMED RANGES with EXACT FORMAT

Sub Rename_The_Name_Range()

    Dim SheetName As String
    SheetName = "Sheet3"
    Dim TargetSheet As Worksheet
    Set TargetSheet = Sheets(SheetName)
    Dim TargetNamedRange As Name
    Dim CurrentName As String
    Dim Iname As String
    Dim OriginalNamePlace As Integer
    Dim HelperString As String
    For Each TargetNamedRange In ThisWorkbook.Names
        Iname = TargetNamedRange.Name
        If VBA.InStr(1, Iname, "_") = 0 Then
            CurrentName = Iname & VBA.Format(VBA.Date, "_yyyy")
            ThisWorkbook.Names(Iname).Name = CurrentName
        Else
            OriginalNamePlace = VBA.InStr(1, Iname, "_")
            HelperString = VBA.Left(Iname, OriginalNamePlace - 1)
            CurrentName = HelperString & VBA.Format(VBA.Date, "_yyyy")
            ThisWorkbook.Names(Iname).Name = CurrentName
        End If
    Next TargetNamedRange
    MsgBox "Done"
End Sub

Timer in Excel Using VBA

Gift Your self a Nice Timer in Excel Using VBA

Just Give It A Try!

Step 1: Insert an Userform

Step 2: Locate the SHOWMODAL Property of The Userform and make it FALSE

Step 3: Design the userform you as you are seeing in video or you can design your own. it's up to you.

Step 4: Inert a New Module and paste the following code into it.

Sub ()
UserForm2.Show vbModeless
End Sub

Step 5: Double Click on a button on the userform which will start the clock and paste the following code in it.

Private Sub CommandButton1_Click()
Do
DoEvents
UserForm2.Label1.Caption = VBA.Time
Loop
End Sub

Step 6: Paste the following code to close the Timer
Private Sub CommandButton2_Click()
Unload UserForm2
End Sub

Step 7: Put a Button on the Sheet and assign macro named as "Open_Timer"
and it's done! enjoy friend.

By Kamal Bharakhda

Sheets Navigator Tool in Excel VBA

You all have that one workbook or spreadsheet, which have definitely more than 50 sheets. Which is very boring to scroll sometimes when you want to work with any random sheets more often.

There are many ways we can perform navigation through sheets. We can create the shapes to navigate there but hardly, we can only do that for a few sheets. Not for every sheet. So, here I'm coming with a permanent and handsome solution.

My Sheet Navigator

How it looks?

How it Works?

it's just initiated with pressing "Ctrl+k"

(I have selected "k" as a shortcut key.)

Select the Sheet you want to navigate to, and double-click it. Navigator takes you to that sheet and userform will be closed automatically.

Isn't easy?

Let's develop this Awesome Tool. 

Step 1: Goto the Developer Tab and click on the Record Macro. You will be asked to name the new macro which you gonna record. Now, don't require to name this macro but if you want then do it. Now, most important is that to assign a macro with the Shortcut key. As I have said, in my case I have chosen the letter "k" and you choose what you want. but remember, if you choose the active shortcut key, after this process, that earlier action will be overlapped by the current macro activities

So save the macro and goto the Developer Tab again and Stop the Recording. Now in VB Editor, you will see this module. 

Step 2: Design the Userform. 

You just need one list box! Insert the Userform and pick the list box from the toolbox and set it just like what you can see in the image. 

Step 3: Coding! 

This tool is running totally on two Events. 

1. UserForm_Initialize event 

2. Listbox ListBox_DblClick Event

Here is the Code for UserForm_Initialize event 

Private Sub UserForm_Initialize()

Application.ScreenUpdating = False

    

    Dim idp As Variant

    idp = ThisWorkBook.Name

    

    Dim Machine As Workbook

    Set Machine = Excel.Workbooks(idp)

    

    Dim sh As Worksheet

    

    For Each AllSH In Machine.Sheets

        Userfrom1.ListBox1.AddItem (sh.Name)

    Next

    

Application.ScreenUpdating = True

End Sub

Here is the Code for Listbox ListBox_DblClick Event 

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Application.ScreenUpdating = False

    

    Dim idp As Variant

    idp = THEIDP.Name

    

    Dim Machine As Workbook

    Set Machine = Excel.Workbooks(idp)

    

    With UserForm1.ListBox1

        Machine.Worksheets(.List(.ListIndex)).Activate

    End With

    

    Unload UserForm1

Application.ScreenUpdating = True

End Sub

There you go...save it and run it! EasyPeasy!!!!

Thank for trying this. 

Regards,

Kamal Bharakhda 

Userforms, TexBoxes & Condition Matching in Excel VBA

Suppose,

TextBox1.Value = 10
and
TextBox2.Value = 10

also, if you pass this following coding it gives the wrong result.

If TextBox1.value = TextBox2.value then 
    MsgBox "Yes They are Equal"
Else
    MsgBox "No, They are not Equal"
End if

You will defiantly get the second message and that is, "No, They are not Equal!"

So why does it happens? In Excel VBA, when you are dealing with Userforms, sometimes work demands an use of conditional matching criteria. Just like, IF the value of textbox1 is equal to textbox2 then do something. just like that.

So, Textboxes have always stored values entered in it as String Format. yes even if it's number or not. So, when do we matching the two textboxes based on numbers then we will surely not get the correct result.

So there are two ways to get rid of this.

1. Use Val function
2. Store the Value of TextBoxes into the Worksheet Range.

1. Use of Val Function

If Val(TextBox1.value) = Val(TextBox2.Value) then 
    MsgBox "Yes They are Equal"
Else
    MsgBox "No, They are not Equal"
End if

This time you will receive the First Message.

2. Let's look at this.

'Store the Value of Textboxes into the cell. 
Sheets("Sheet1").Range("A1").Value = TextBox1.value
Sheets("Sheet1").Range("A2").Value = TextBox2.value

If Sheets("Sheet1").Range("A1").Value = Sheets("Sheet1").Range("A2").Value then 
    MsgBox "Yes They are Equal"
Else
    MsgBox "No, They are not Equal"
End if

The result will be the same, the first one. It's because of Excel automatically recognize the DataType of the Data entered in the cell. So It will become more accurate and also solves your purpose.

These are the small things. many are knowing and many don't. Who doesn't it's for them. 

Thank you!

Kamal Bharakhda

Making VBA Development Version Independent Part 1

Making VBA Development Version Independent, will require focussed research and I think it's still "long" to go... ( Here "long" is not data type :P ) 

I tried my best to hell throughout VBA versioning issues but sometimes VBA itself has limitations which keep us behind the facility of distributing the spreadsheets. 

We need to solve every issue on a particular basis. 

So Let's try that first particular. :D

>> Finding the Last Empty Row of Worksheet Data <<

This is most common task amongst every VBA Tasks. There are multiple ways to get the last empty row but the easiest way is following mentioned. (I use it every time!)

'/-------------------

'Finding Last Empty Row of Sheet1

Sub Test()

    Dim LROW as Long 

    With Thisworkbook.Sheets("Sheet1")

    LROW = .cells(.Rows.count, 1).End(XlUp).Row + 1

    End With 

    Msgbox LROW

End Sub

'/-------------------

Above subprocedure will throw Last Empty Row Number through Message Box. 

Have you noticed, I have used a variable to store the procedure reflection. And that where the whole procedure goes wrong to become a version independent. 

Let me explain, 

I have assigned that LROW variable with datatype LONG. Which is itself has different values when you have MS OFFICE of 32bit or 64bit. Ans technically in both systems has a different method to assign long datatype. How? (Research It!)

So, to solve at least this issue I want to change the datatype of a variable from LONG to Integer! Yes, Integer datatype has same values across every version and its exact value is between -32,768 to 32,767 and we know, every worksheet after MS OFFICE 2007 will have more than 10 lacs of rows. which will not hold the row number data and will throw an error at the same time on execution like Type Mismatch Error no. 13

So, if you guys are sure about that, your database will not exceed the 32000 lines then I have perfect version independent code for you. :) apply it. 

'/-------------------

'Finding Last Empty Row of Sheet1

Sub TestVersionIndependent1()

Dim LROW as Integer

    With Thisworkbook.Sheets("Sheet1")

        LROW = .cells(32000, 1).End(XlUp).Row + 1

    End With 

Msgbox LROW

End Sub

'/-------------------

Above code will work for sure. Enjoy...!!

I will keep post more n more on this subject. 

- Kamal Bharakhda

The Wonderful Login System for the Excel VBA

This is Kamal Bharakhda & I'm here presenting my tools for Excel VBA Workbooks for its protection. Yes, I have named this Application as "The Wonderful Login System" What it actually does, it will help you to make secure your Excel Workbooks at high-level. Yes, this application will use the online resources to compare the login credentials. Main Operation & Features of using my Login System. 1. Your application will not be accessed off-line. Yes, if your system has active internet, then only you can access the login system in the first place. 2. After checking the activeness with the Internet, you will see the Login form right at the moment. Now, Enter your credentials. 3. After entering the user details, you need to hit the "LOGIN" button once, and it will compare your provided pieces of information with an online database, and if it goes wrong then it gives the wrong messaged popups. 4. Let's say if you want to restrict one of the users the workbook, then you just need to change the password from the database. after that, that user will not be able to access the document furthermore. 5. Wonderful Login System will also check the System Compatibility. Suppose, one of the users has copied your workbook for the data... then the user will not be able to access that workbook from the different system, even if the user is providing the user credentials correct or not! So, in short, your workbook will be copy protected. Isn't it awesome tools for the industry where the data security is highly demanding things? Well, I'm also thinking of to adding more features, if you advise me to do so. But I'm so much excited to distribute this awesome tool. I hope it will help you in your work. Email me for any queries. I'm here providing the link of the video representation of my Wonderful Login System. Please have look to it. https://www.youtube.com/watch?v=VYuAP... -- Thank You, Regards Kamal C. Bharakhda | VBA (Excel) Developer Ahmedabad +91 - 9328093207 kamal.9328093207@gmail.com Skype : bharakhdakamal

Default Text for Textbox of Userform

Userform utility of VBA has always fascinated me to experiments beyond the limits. Well, I haven't touched the max yet. but for me, it's always necessary to dig new possibilities from available resources.

Today I have tried very common experiments with "Textbox Tool" in Userform.

I'm working on some serious Login system for the VBA Applications which will authenticate the user's credentials by matching it from the external database(Google Sheet). If it matched with the source strings, the program will allow the user to access the remaining features of the application. This Login system will be required the active internet connection.

But, that is not the subject of the discussion here.

We have already seen on many websites and on applications, the default text imprinted on the textboxes. Let's say, you are on the login page of any website. In the Username Box, you could see the default text like "USERNAME" or "TYPE YOUR USER ID HERE" etc....

And When you click the textbox, the default text will be disappeared and textbox gets blank and it will allow the user to write their user id. And if you left the textbox empty, it will again show the Default text on the box.

So How we can code the same in Excel VBA Textboxes?

I have used two events of Textbox

1. Enter Event - The Enter event occurs before a control actually receives the focus from a control on the same form or report.
2. AfterUpdate Event - The AfterUpdate event occurs after changed data in a control or record is updated.

--------- 

Private Sub UserForm_Initialize()

     'Assigning the DEFAULT values to the Textboxes
     UserForm1.TextBox1.Value = "USERNAME"

     'the Forecolor of the Textbox when userform initializes
     UserForm1.TextBox1.ForeColor = &HC0C0C0 'light gray

End Sub

---------

Private Sub TextBox1_Enter()

     UserForm1.TextBox1.Value = ""
     UserForm1.TextBox1.ForeColor = &H0& 'black

End Sub

---------

Private Sub TextBox1_AfterUpdate()

     'AFTER UPDATING THE TEXTBOX IF THERE'S NO STRING
     If UserForm1.TextBox1.Value = "" Then
          UserForm1.TextBox1.Value = "USERNAME"
          UserForm1.TextBox1.ForeColor = &HC0C0C0
     End If

End Sub

----------

It's work like Pro! Lol...

Try urself with this code

Thank you

- Kamal Bharakhda

How to start Excel VBA Programming ?

Question:

Hi All! I considered myself an advanced user until I learned there was an entire world of VBA out there. So now I’m trying to teach myself. This group is an amazing resource. Thanks to all that contribute!

Question: Does anyone have a specific method or process in planning an Excel project?

I’m finding there’s so much you can do and I end up getting lost down rabbit holes. I decided to start with designing the visual aspect of UserForms first then figuring out how to make them work later. I’m not sure that is the best way.

Hopefully, my question makes sense. Thanks for any and all feedback!

===========

Answer:

Let's begin with the home.. :)
Before a Six month ago, I was in your place. But, I have decided to learn VBA anyhow. I'm an Engineer BUT, I was too much far from the world of programming.
I started learning VBA by Recording A Macro. Yes,
there are two features available in Microsoft Excel to do an automation task.
I used to record macros and see how it coded. I took little help from my cousin who knows VBA programming. He challenged me with the little tasks.
task Like,
Copy every time the cell value of A1 of sheet1 to Cell A1 of sheet2
So, that's how it begins for me.
We are all for you as my Cousin brother was for me!
And one more important point to remember.
whenever you feel just little in the amount that, you can write little codes on your own. Then go for the bigger project to develop. and challenge your self to develop each and every idea of yours regarding your dreaming application.

1. First, design your idea.
2. Write down the functionality of your dream application.
3. Make the design on paper that, how it will look
4. Make the design on paper that, how it will work.
5 Finally, think about how I will code this damn!

And we are always here for all of our group members.
thank you and all the best.

INDIAN CURRENCY FORMAT FOR VBA EXCEL

Hello Mates, 

When you work with Userform in Excel VBA, usually sometimes you need to retrieve or reflect currency figure in the Userform (Like in Textbox, in Label or in Listbox) from the range(s) of the workbook. 

When you directly assign values to the objects, Userform generally takes it as a number and never reflect the proper format that you required in as Indian or else country's currency's format. 

So, I have struggled a lot to find the solution indeed. After a constantly looking for the solution of this, I came across to the following worksheet function. 

It's ".Text" formula

Yes, here's the Syntax:

userform1.textbox1.value = Application.WorksheetFunction.Text(VariableName.value, "[>=10000000]##\,##\,##\,##0.0#;[>=100000] ##\,##\,##0.0#;##,##0.0#")

Thank you. 

- Kamal Bharakhda