Wednesday, July 4, 2018

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

2 comments:

  1. 32 bit version, long data type is 4 bytes so the value is from -2 millions to +2 millions , but Rows of worksheet is just 10 lacs of rows. So I think there would be no issues with your code Long above.

    It I missed something, Please help me explain more detail.

    Thanks,
    N.Minh

    ReplyDelete
    Replies
    1. Dear N. Minh,

      Thank you so much for your comment first.

      My subject of this post is to make our workbook more distributive since few things are not same in 32bit Office and 64bit Office. And earlier, I came across to one article where author has cleared that, 64bit office have some flexible no. of rows and columns then 32bit. so i thought if we knows, our database would be no longer cross the 30000 mark then use of integer will be more good to find the last row. and yes it will be less expensive too for memory usage.

      Otherwise Use of Long is perfectly fine!

      Delete

IsValidPasswordString Function

'Following function will verify if the password string contains following characters or not? Rem : List of Characters Group - ASCII Rem ...