INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Cleaning up code, declaration and assignment of variables on same line...

Cleaning up code, declaration and assignment of variables on same line...

Cleaning up code, declaration and assignment of variables on same line...

(OP)


I read you can declare a variable and assign it on the same line, but on that forum post... there was confusion as to being able to do it with String data types. 1 guy said, yes...another said no.

I can't get it to work... but thought I'd ask if I just have my syntax wrong... any help would be appreciated.

CODE

Dim varSTAT As String: varSTAT = Sheets("Office View").Range("J" & (ActiveCell.Row)).Value 

RE: Cleaning up code, declaration and assignment of variables on same line...

Works for me.

Cheers
Paul Edstein
[MS MVP - Word]

RE: Cleaning up code, declaration and assignment of variables on same line...

VBA happily supports multiple statements on the same line, where ':' is used to separate the statements.

RE: Cleaning up code, declaration and assignment of variables on same line...

Just be careful to not overuse the multiple statements on a single line setup. You could end up making your code more confusing to read through when troubleshooting later.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

RE: Cleaning up code, declaration and assignment of variables on same line...

Although it is POSSIBLE to code multiple statements on one line I rarely do that. One of the places that I might is in the Select Case...End Select code structure like...

CODE

Select Case i
   Case 1: s = "Hello"
   Case 2: s = "Good Bye"
End Select 

Check out Tools > Options -- Editor TAB.
I have all the boxes checked, especially Require Variable Declaration. Also the Auto Indent. Indenting code is, IMNSHO, essential to generating readable code.

In general here's what I do to generate readable code:

1. Make all my declarations at the top of my Module/Procedure, inserting Comments.
2. Code all structures immediately and then go back and "fill in the blanks."
3. Indent all code/structures within a code structure.

So if I'm coding a loop...

CODE

For Each x in y

Next 
...and then go back and "fill in the blanks"...

CODE

For Each rQTY in [tRQ[QTY]]

Next 
...where I ought to have a declaration for rQTY as a Range object variable. The same goes for If...Then...Else...End If

CODE

If x Then

Else

End If 
...and then go back and "fill in the blanks"...

CODE

If rQTY.Value <= iLIM Then

Else

End If 

So my structured code would look like this...

CODE

Sub RQ_Proc
   Dim rQTY As Range     'Quantity in RQ table
   Dim iQLIM As Integer  'RQ Quantity Limit

   For Each rQTY in [tRQ[QTY]]
      If rQTY.Value <= iQLIM Then

      Else

      End If
   Next
End Sub 

So even without comments, you can clearly see two structures within the procedure structure. I guess you might say that I am somewhat dogmatic about this. Ha!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Cleaning up code, declaration and assignment of variables on same line...

(OP)


OK... so I've given up. Could someone please look at this and get this working again. First let me explain how to get to where you can see what I need you to see.

Login, use the passcode 1. And click View Transactions. Then click any of the recordsets and click the Validate Transaction button. A form will open and nothing will fill out in the form.

Ok, now to what I did. Everything did work... previously I had the code on the form itself and I had at the top of the procedure declared all the variables, followed by assigning all the variables. Well then I realized I wanted to be able to pass the MLS number from the recordset you clicked on which is the primary key to the data. I wanted to be able to pass the MLS number to the code so the code could be used from 2 different events (without duplicating the codework). So I put the code into the module and called it from the Validate Transactions button. Then from another place...that isn't important right now. Everything worked fine. So I was close to being done with the project and decided I wanted to clean up my code and comment it better... which got me thinking to condense the declaring and assigning into 1 line. So I tried it and it didn't work. I posted about it and still never got it to work. So then I put it back to the way it was....and it's no longer working. I really just want to be done with this project... I'm beginning to hate Excel. Could you please take a look and see what you can do. Thanks in advance,

RE: Cleaning up code, declaration and assignment of variables on same line...

"Validate Transaction button"

Where's THAT?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Cleaning up code, declaration and assignment of variables on same line...

(OP)
Scroll to the left... just before I saved it, I unhid all the columns and it didn't save the view showing column A.

RE: Cleaning up code, declaration and assignment of variables on same line...

Okay.

CODE

Dim AgentLicNum As Integer: AgentLicNum = Sheets("Office View").Range("C" & (ActiveCell.Row)).Value 

TILT!

Your TREC Licence Numbers (BTW, will you EVER do arithmetic on ANY of these "numbers") ALL exceed the limit of an Integer data type in Excel VBA.
FAQ68-6659: When is a NUMBER not a NUMBER?

You ought to either declare AgentLicNum as Long or as String, which is really what it ought to be.


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Cleaning up code, declaration and assignment of variables on same line...

(OP)
Ok.... I'll take that into consideration... but that's not where the program is bugging. Plus it did work before I combined the declaration and assignment. And yes, I do alot of math with these numbers, the code is all there below the declarations. On that one though, I don't believe I gave the declaration a data-type, I believe I left it as a variable data-type. I recently changed it when I combined the declarations and assignments.

RE: Cleaning up code, declaration and assignment of variables on same line...

You do math with AgentLicNum?

I searched your entire project and I can see no math used with that variable!!!

Quote:

On that one though, I don't believe I gave the declaration a data-type

WHAT???

Your declaration is right there!

CODE

Dim AgentLicNum As Integer: AgentLicNum = Sheets("Office View").Range("C" & (ActiveCell.Row)).Value 

Your code ERRORED on that assignment following the declaration. THEREFORE, nothing filled in on your Userform.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Cleaning up code, declaration and assignment of variables on same line...

(OP)
Ok sorry, I misunderstood you... I was thinking it was erroring out above that line. I see it semi-works now... I'll see if I can find the next problem. Thanks.

As for the declaration:

Quote:


Ok.... I'll take that into consideration... but that's not where the program is bugging. Plus it did work before I combined the declaration and assignment. And yes, I do alot of math with these numbers, the code is all there below the declarations. On that one though, I don't believe I gave the declaration a data-type, I believe I left it as a variable data-type. I recently changed it when I combined the declarations and assignments.

RE: Cleaning up code, declaration and assignment of variables on same line...

👍

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Cleaning up code, declaration and assignment of variables on same line...

Not sure why you would even want to do this. It is not saving anything and it does not make your code any easier to debug. You already proven it makes it harder to debug. Just, declare all your variables at the beginning. What you should be focusing on is using proper strict data types. Only use a variant when you have to, which is rare in my opinion.

CODE

Dim i, lastrow
    Dim afv, afagc, af1099
    afv = 0
    afagc = 0
    af1099 = 0
    lastrow = Sheets("Sales Datasheet").Range("A" & Rows.Count).End(xlUp).Row 
Those variables are all clearly long or integer variables but you declare them all as variants. There are lots of problems with that. A variant defaults to null, and a long and integer default to zero. If you would have declared them properly, there is no need for

CODE

afv = 0
    afagc = 0
    af1099 = 0 
Dim afy as integer
debug.print afy
You will see 0

Dim afy
debug.print afy
you will see nothing because it is a null


The other issue is for trouble shooting. If you strictly dimension your variables your code will error out when an unexpected datatype datatype is returned. So for example assume you made a typo and forgot to type .row
lastrow = Sheets("Sales Datasheet").Range("A" & Rows.Count).End(xlUp)
You would want to catch this problem at this time.
Since lastrow is a variant this will not error because it will return a range object and assign to the variant. If you declared correctly it would either not compile or error at the location and you would know where the error was immediately.
Instead the next time the code uses lastrow it will probably error, but it will not be very clear why.

Another issue in doing single line declarations is
Dim afv, afagc, af1099 as long
Only af1099 is a long the other are still variant.

See this
http://www.cpearson.com/excel/declaringvariables.a...

RE: Cleaning up code, declaration and assignment of variables on same line...

(OP)
Great explanation... thanks!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close