Variable Type For A Residential Address

I'm literally making my first steps into writing VBA code.

I am defining my variables for a bit of code; what would I use for a string that contains both numbers and text, for example 23 Yew Tree Road?

I understand that declaring it as a Variant will slow down my code; would I declare it as an Object?

Thanks in advance

No, an object is something like a workbook, or a worksheet, or button.

A mix of text and numbers would be a string.


Regards, TMS

Just a String.

Also, when I use the following:

Dim Address 2 As String

I get the following compile error: "Expected: end of statement"

?????

Thanks TMShucks and shg; I can see this is going to be a long, long road

Remove the space between Address and 2

Please Login or Register  to view this content.

Since you are just starting out, I would highly encourage the adoption of a variable naming convention you are comfortable with. There are a few to choose from, and you will see them in use throughout the forum should you continue to frequent.

Using your example, you would likely see the variable declared as strAddress2 (hungarian notation), or sAddress2 with the "s" denoting a string type.

C. Pearson's explanation on the topic is a must read for beginners, in my opinion.

Hope this helps

If the 2 is supposed to be part of the variable name, it isn't. Variable names must start with a letter and include nothing except letters, numbers, and underscores.

Cheers shg.

AlvaroSiza, I will take you up on your advice and read C Pearson's article.

I'm trying to write (well in truth, modify some existing) code that copies data entered into the row of one workbook and pastes it into the first empty row of another workbook. The existing code transposes the data from the source workbook's columns into the target workbooks rows so hopefully I will learn a little bit about VBA by the time I've got my head around it!

AlvaroSiza,

If I define the variable as strAddress2 as you suggest, would the column header have to be identical?

No. What you name a variable has no bearing on what the variable will actually represent. In practice it should be 'close', but following directly will likely confuse the code (in my opinion)

You assign the column header either directly in code

Please Login or Register  to view this content.

or via reference to a range (cell) containing the string you wish to assign

Please Login or Register  to view this content.

Declaring a variable specifically as a variant, or not defining a type, has penalties both in efficiency and performance. A variant will take up more space than a defined type and there will be conversions taking place.

If you don't have many variables and you don't have much data, you probably wouldn't notice or need to worry about it.

When you get into defining objects, like workbook and worksheets, if you define them properly, VBA will know what they are and will prompt you with appropriate methods and properties.

Thanks for the rep.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.