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
