Which Variable to use?

[AndrewKent]

Hi there,

On my last few threads I have been asking questions about how to best declare variables. After reading through the excellent content on C Pearsons website I now have a better understanding of how to declare a variable.

What it doesn't give me however is a list of what variables to use in certain situations. For example...

Text would be As String
Number would be As Intiger
True would be As Boolean

...does anyone have a list of what variables to use and when?

Andy

 

[AndrewKent]

Cheers,

Set AddStatusNumber = Worksheets("Status History").Range("B4: B" & Range("B65536").End(xlUp).Offset(1, 0).Row & "").Find(What:=CheckStatusNumber)

Range?

PS - You have no idea how much this is helping!!!

 

[VoG]

Range is correct.

Its a long time since I did a multiple choice test

 

[RoryA]

Yes.
As an aside, I would suggest you adopt a naming convention so that it is always clear what type your variables are - for example:
rngAddStatusNumber (Range)
lngActiveRow (Long)
and so on.

 

[AndrewKent]

Rorya - Stunning piece of advice, I'll get on that...

Okay next...

StatusNumber = ActiveRow - 4

Both integer?

 

[VoG]

I would use Long - a row number could be greater than 32,767 resulting in Integer overflow.

 

[AndrewKent]

Here's a toughie...

    Dim Query As String
    
    Query = "SELECT * FROM tblTrackerFundData WHERE Record_ID = '" & Worksheets("Calculation Matrix").Range("CalculationMatrix_Search").Value & "'"

It declares a query in an Access database.........String?

 

[VoG]

It certainly looks like a String. However, I am an Access moron.

 

[RoryA]

Yes - it's just text.

 

[AndrewKent]

Gents,

You are the best. Thankfully that's it as I'm now finishing work for the weekend but I expect I'll continue this on Monday, as will some of my programmers!

Again, the help you are providing is top drawer so thank you again.

Andy

 

[AndrewKent]

Actually what I do need before Monday is a quick justification on why I need to decalre the varables in my program properly. This is so my bosses will actually give the thumbs up to the work.

Now, If I've not decalred the variables properly, (or put Option Explicit in....I know, naughty of me) then VBA defaults each variable not decalred to Variant.

To which my bosses will say "Yeah, but it still works?"

I want to be able to quantify that there will be a performance increase in our application. So, if I've got 2,000 variables that I haven't declared properly (therefore are Dim X As Variant) and they should have been Dim X As String, what sort of effect is me correcting them going to have?

To put you in the picture, I had declared everything as such...

 Dim Version, Type, Name As String

Where I should have coded as such...

 Dim Version As String, Type As String, Name As String

So you can see, most of my variables will be Variant by default.