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

Text Masking in Excel VBA

Hello Friends,

Two days ago, I have posted one query here on Password Masking of Userform Textbox. David Miller tried to help me out but it's not happened actually.

Problem:

.PasswordChar property has been used to mask text to look like Password Characters just like "*********"

So, it's working fine in Windows Excel but not in MAC Excel.

Solution:

So, I developed a code which works with Textbox1_Change events. There is some limitation too with my solution. In general, you can edit the typed password during writing but in my case user won't be able to do so. if User wants to edit the password, they have to rewrite the whole code again.

My code will use the Cell A1 to store the actual password and will use it for credential matching.

here is the solution:

'/+++++++++++++++++++++++++++

Private Sub TextBox1_Change()

    Dim mystring As Variant
    Dim textlen As Integer
    Dim counter As Integer
    Dim passlen As Integer

    mystring = UserForm1.TextBox1.Value
    textlen = VBA.Len(mystring)
    passlen = VBA.Len(Sheets("Sheet1").Range("A1").Value)

    If VBA.Right(mystring, 1) = "*" Then
        If passlen <> textlen Then
            MsgBox "You're not allowed to do so"
            UserForm1.TextBox1.Value = ""
            Sheets("Sheet1").Range("A1").Value = ""
        Exit Sub
        End If
    Exit Sub
    End If

    For counter = 1 To textlen
        If textlen > 0 Then
            If VBA.Mid(mystring, counter, 1) = "*" Then
            Else
            Sheets("Sheet1").Range("A1").Value = Sheets("Sheet1").Range("A1").Value & VBA.Mid(mystring, counter, 1)
            End If
         End If
    Next

    If textlen > 0 Then
         UserForm1.TextBox1.Value = VBA.Replace(mystring, VBA.Mid(mystring, textlen, 1), "*")
    End If

End Sub

'/++++++++++++++++++++++++++++

this way text masking will be more universal and portable.

Thank you.

The scope of the Variables in VBA - Part 1

The scope of the variables means when we declare variable to hold some information in it, we need to provide those variables with some datatypes. DataTypes could be anything. Integer, long, string, variant etc...

Sometimes we required a single variable to use it in multiple procedures and even throughout the whole project of the workbook sometimes.


To understand the scope of the variables, we need to understand the basic structure of the VBA. Whatever the procedure you create it depends on your declaration, how you want to use those subprocedures and variables.

There are three layers of the project.

1. Procedure
2. Module
3. Project

the bottom layer is Procedure

Like whatever you will define will stay up to that procedure only.

E.g.

insert the module and type the following sub-procedure.

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

Private Sub Testing1()
'This is the Procedure.
       Dim A as long
       A = 1
       msgbox A
End Sub
'/------------------------------------------

Private Sub Testing2()
       msgbox A
End Sub

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

The output of Testing1 will be "1"
and The output of Testing2 will be "0" or blank

Because we have declared variable A in sub procedure. and that variable A has the scope of up to that Testing1 Procedure only.

So it's seems a private variable declaration.

you can notice... procedural declaration has effect until its own procedure only. that's the concept. we can do more and more.

But Scope is not only limited up to variables. It also applies to the declaration of procedure too. but we will look forward to it in next part.

thank you.

- Kamal Bharakhda

Concept of Connecting MySQL data into the EXCEL using VBA

There're numerous modules available on the internet about Importing MySQL Data into Excel using VBA. But What they don't explain is about the concept of the Connection string. you need to understand few important attributes of connecting MySQL server with ODBC Drivers.

We need the following few things to attempt this procedure.

1. Go to the Tools > References > and choose Microsoft ActiveX Data Objects x.x Library (6.1 in my case) 
2. Then we need to create two objects with ADODB (ActiveX Data Objects), one for connection and one for data storage (which brings data with it).

We refer,
Connection as ADODB.Connection, and 
Recordset as ADODB.Recordset

The first part is over. Now, comes the toughest part of the program where we need to create the connection between our excel workbook and the MySQL server where your data is kept under tables.

Now, to connect both excel and server, we need to address the things very properly. and it should be. because without providing the proper addressing, we will surely get the frustrating errors. anyway, let's get back to the point. so, addressing the server correctly, we need to provide the various pieces of information to the objects. and those pieces of information are attributes which we will use to get the perfect data as queried.

Here are those important attributes / element. 
- Driver / Provider
- Server Name / Host
- DataSource / Database 
- Initial Catalog / Table Name 
- Username 
- User Password
- Port No.

You need someone who supports you while reaching towards your destination. that we called a DRIVER. the first element. there are many attributes we required to mention but the DRIVER is the most sensitive part of the program. Because it requires the damn clear conception of ODBC Driver. (There are many kinds of drivers, but mostly ODBC driver has been used to fetch the MySQL Server Data)

ODBC is the clear and simple driver/software, which will use your attributes/elements and will reach the perfect destination using your SQL Queries. Let's looks at the definition of ODBC by Microsoft. "Open Database Connectivity (ODBC) interface by Microsoft that allows applications to access data in database management systems (DBMS) using SQL as a standard for accessing the data."

Now, ODBC Drive Which I have used is. "MySQL ODBC 5.3 ANSI Driver". There are many version available of the same software but I prefer to use this because it's the most successful one till.

You can find the ODBC driver in the System Drive C: > Windows Folder.

now the most important thing to remember is that, where this file is actually placed?

if your Windows operating is 32bit and office application also 32bit then VBA will look the ODBC driver in the system32 folder under windows. there's no issue at all.

but what if you have 64bit windows and running the 32bit office? you will face Driver Lost errors. It's just because, mostly all active drivers in the 64bit windows are kept in SYSWOW64 named folder under the same windows folder. So you need to copy that driver from system32 to the syswow64 folder.

but first, find the driver which named as odbcad32.exe (you can even find that drive in Administrative Tools)

Double click and find the DRIVER tab. you will get the above-mentioned ODBC driver name there. what if you don't have that driver specified there? just don't worry. Google the "MySQL ODBC 5.3 ANSI Driver" to download that driver's MSI form. and installed it. it has nearly an 8 MB of size. and I recommend you to choose 32bit if your office is 32bit. vice versa.

after installing, check the driver tab again, you will get that name there.... and now try your code again. you will get the result. and that's how I get.

it's the only thing which hasn't been discussed anywhere. so I thought I should share with you the proper concept.

thank you.



- Kamal C 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.