Consulting

Results 1 to 6 of 6

Thread: Solved: Declaring Variables in Excel VBA

  1. #1
    VBAX Regular
    Joined
    Jul 2004
    Location
    Cambridge, England
    Posts
    23
    Location

    Solved: 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 :

    [vba]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[/vba]

    , 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
    Last edited by Richie(UK); 07-31-2004 at 04:08 AM. Reason: Added VBA tags

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    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.
    [vba]
    Sub Test()

    Dim x As String
    Dim n As Long

    x = Application.InputBox("Enter name.")
    n = WorksheetFunction.Find(" ", x) - 1


    End Sub
    [/vba]

    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.

  3. #3
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    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

  4. #4
    VBAX Regular
    Joined
    Jul 2004
    Location
    Cambridge, England
    Posts
    23
    Location
    (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

  5. #5
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    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.

  6. #6
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Also if you are not sure what data type you should use, just post the code here and we will help you out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •