Toggling Without an IF Statement

[OldRookie]

Is there a single VBA command that will test an expression/variable for a specific value (ie. 0) and when found, toggle an initialized variable between 0 and 1? For example,
X=0
Y=0
Loop:
If Y=0 Then X=1-X
Else
X=X
End If
Y= (a calculated expression)
Etc.
Etc.
GoTo Loop

I’m under the impression, any If statement I build into my code will slow execution down. For small applications this isn’t a problem. However with nested For loops and thousands of iterations, it can be.

 

[duggie33]

In your example, the "Else...X=X...End If" is not required. You could simply write "If Y=0 then X=1-X".

Hope that helps,

Doug

 

[OldRookie]

Doug,
That’s a good suggestion. One line command; certainly simple enough. I know this is getting into the minutiae, but do you think if a more elaborate, specialized command with several arguments actually existed, that it would be any faster? I don’t know how those types of amazing commands work at a machine language level. In my case, anyway I go, I see a decision being made and an assignment. Just a question of it’s existence and which would be faster.

 

[duggie33]

Generally speaking, the more complex the code (the more calculations it has to do), the longer it will take to process. Unless you have very large datasets and the processing time is critical, the time will not likely be an issue. I have some done data analysis and extraction of data that meets multiple criteria on somewhat large datasets (for Excel anyway), around 800,000 rows, and it is completed in about 15 seconds. In my mind, that is fast compared to the minutes it takes to process the same data using formulas on the worksheet.

I suggest you write the code, see how long it takes, and if it is not as fast as you need it to be, look for ways to speed it up. There are some brilliant people in the forum who are willing to help, but they will need more details.

Doug

 

[OldRookie]

Doug,
My coding skills are old and don’t make use of the streamlined, faster, commands available today. Just kicking around my flow chart, I’m envisioning a nested pair of For loops. The inner will have as many as 20 reps and then two IF statements. The outer For statement can then have as many as 2^20 (1,048,576) reps; thus my concern.

The 15 second run time on your data set is encouraging though. I’ll do what you suggest, write the code and see what happens.

Thank you very much!