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

[SnayJ]

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.

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

 

[macropod]

Works for me.

Cheers
Paul Edstein
[MS MVP - Word]

 

[strongm]

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

 

[kjv1611]

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

 

[SkipVought]

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...

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:
[tt]
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.
[/tt]
So if I'm coding a loop...

For Each x in y

Next

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

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

If x Then

Else

End If

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

If rQTY.Value <= iLIM Then

Else

End If

So my structured code would look like this...

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SnayJ]

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,

 

[SkipVought]

Validate Transaction button"

Where's THAT?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SnayJ]

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

 

[SkipVought]

Okay.

    Dim AgentLicNum As [b][highlight #FCE94F]Integer[/highlight][/b]: 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

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


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SnayJ]

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.

 

[SkipVought]

You do math with AgentLicNum?

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

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

WHAT???

Your declaration is right [highlight #FCE94F]there[/highlight]!

[highlight #FCE94F]Dim AgentLicNum As [b][u]Integer[/u][/b][/highlight]: 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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SnayJ]

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:

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. [highlight #FCE94F]On that one though, I don't believe I gave the declaration a data-type[/highlight], I believe I left it as a variable data-type. [highlight #FCE94F]I recently changed it when I combined the declarations and assignments[/highlight].

 

[SkipVought]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MajP]

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.

    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

    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.aspx

 

[SnayJ]

Great explanation... thanks!