Declaring Variables in Excel VBA

Hi all, dunno the correct terminology (it's not in Lesson 1, Annie!), so apologies if the title's misleading. I've kludged for years, but I am TRYING to learn properly now. Basically I have this :

Code:

---

Sub Enter_Name()
x = Application.InputBox("Enter name.")
n = WorksheetFunction.Find(" ", x) - 1
Range("H1") = Left(x, n)
Range("H2") = WorksheetFunction.Substitute(x, Range("H1") & " ", "")
End Sub

---

, which works fine.

(It's best if I ask one thing at a time, so I'll start with) should I/do I really need to prefix with "Dim x As blah, n As ???", or whatever the heck it's called?

TIA,
Andy

Lesson 2 covers this.

You should always decare variables. The code will run faster (not that it will be noticable) and it will ensure that only the correct type of data is assigned to the variable. Also for objects you will get access to VBA Intellisense for that variable.

Code:

---

Sub Test()
Dim x As String
Dim n As Long
x = Application.InputBox("Enter name.")
n = WorksheetFunction.Find(" ", x) - 1
End Sub

---

Btw even though Long is for much larger numbers that Integer, Long variables calculate faster, so there is never really a reason to Dim As Integer.

Hi Andy,

Welcome to the board.

You don't need to declare your variables, and some people never do. However, as you are relatively new to VBA coding I would recommend that you get into the habit of declaring your variables (you can always choose to abandon the practice again at a later date ;) ).

It is more efficient to declare your variables with the appropriate data type rather than allow Excel to use the default Variant data type. However, this is not, to my mind, the main benefit. I see the main benefit as being the fact that it helps to detect errors in your coding. You can enforce variable declaration by going to Tools (in the VBA) and selecting Options and then tick the box for 'Require variable declaration' - this will add Option Explicit to the start of your code modules and make sure that you declare your variables. It helps to pick-up simple typos like this:[vba]Sub Test()
strtest1 = "Hello world"
MsgBox sttest1
End Sub[/vba]
You may also wish to consider adopting some sort of naming convention for your variables - this makes them much more meaningful. For example, rather than using x as a Long variable for a row you would use lLastRow - the l indicates that the variable is of the Long data type and the LastRow is a little more helpful when reading through the code than a simple x.

You should also look in the VBE Help files under "Understanding Scope and Visibility" - this is an important concept to grasp.

HTH

(Thanks for the edit Richie ; the actual questions are all I can get my head round at the moment)

Thanks DRJ. I know I should, I just don't understand them. x As String because it's text? n As Long (vice Integer) because Find returns a number?? Can I change my middle name to Dim???

I'll try Lesson 2 but it's usually where I get lost. BTW, I heard you were pretty good.

Rgds,
Andy

You can do a search in VBA for data types to see what they are.

String is for Text
Integer or Long for whole numbers
Single or Double for fractional numbers
Date for dates

etc.

Also if you are not sure what data type you should use, just post the code here and we will help you out.