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