We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

How to execute simultaneous lines of code in VBA?

rgrguric
rgrguric asked
on
Medium Priority
931 Views
Last Modified: 2010-04-07
I have 3 equations I would like to execute simultaneously.  Please show me how to execute simultaneous equations in VBA.

NP1 = (NPmax1 * Rnd() + NPmin1)
NP2 = (NPmax2 * Rnd() + NPmin2)
NP3 = (NPmax3 * Rnd() + NPmin3)

This is an urgent one...

Thanks,
Comment
Watch Question

Harisha M GEngineer
CERTIFIED EXPERT

Commented:
Hi, if your goal is to use the same random value for all the three, then you need to store that in some temporary variable

TemporaryVar = Rnd()
NP1 = (NPmax1 * TemporaryVar + NPmin1)
NP2 = (NPmax2 * TemporaryVar + NPmin2)
NP3 = (NPmax3 * TemporaryVar + NPmin3)


---
Harish
CERTIFIED EXPERT
Top Expert 2010

Commented:
Hi rgrguric,

Why is it important that these be done at the same time?

Also, if the intent is to make NP1 be a randomly selected value between NPmin1 and NPmax1, your code does not
necessarily achieve it.  This will:

NP1 = NPmin1 + (NPmax1 - NPmin1) * Rnd()

Regards,

Patrick

Author

Commented:
I need it to calculate a different random value for each equation.
Muhammad KhanManager, IT
CERTIFIED EXPERT

Commented:
but why simultaneous?

Author

Commented:
I have many equations similar to these that would save me a bunch of time if I could run them simultaneously... I'm using this for data analysis.  I have 78 variables involved in a set of 83 equations.  I need to run that set of 83 equations 500,000 times... this is very time consuming running each line one by one.
Harisha M GEngineer
CERTIFIED EXPERT

Commented:
rgrguric, can you tell us, what exactly is your need ?

What do you mean by executing simultaneously ?

1) Do you mean they are simultaneous equations ?
-- If so, store the coeffecients, and then use one of the methods, (matrix method, using determinant would be the easiest) to solve them

2) Do you mean they should be executed at the same time ?
-- I am not aware of that in VB without a new thread

Author

Commented:
Thank you for the correct random function Patrick.
Muhammad KhanManager, IT
CERTIFIED EXPERT

Commented:
VB doesnt support multithreading intrinsincly...

but you can still create ActiveX Exes and have them run simultaneaously from an application. but that would be a poor design...

Author

Commented:
I enter 13 nominal values for 6 variables.  I then calculate the min and max for each nominal value. Next, I generate a random number between the min and max.  I then have 78 "random" numbers.  I then use these "random" numbers in various equations.  Finally, I store the results.  I recalculate the "random" numbers and rerun the various equations each iteration.

The problem I run into is when i run 1,000 iterations, time ran < 1min
When I run 10,000 iterations, time ran < 2min
When I run 100,000 iterations, time ran > 30 min... I stopped at 30 min, there were only 23k out of 100k records stored in the table
Muhammad KhanManager, IT
CERTIFIED EXPERT

Commented:
Then i can suggest that you design an ActiveX Exe project.. which does all the calculation and then insert the data into database.. then you create 6 objects and have those running... Threading model must be per object... so that every object runs in a separate thread...

theoritically that should reduce the time of execution to 1/6th of present value.
Engineer
CERTIFIED EXPERT
Commented:
Instead of storing/displaying the results in each iteration, store them in some temporary variable, and store to the table/display it after some, say 100, iterations. This will speed up the process...

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Muhammad KhanManager, IT
CERTIFIED EXPERT

Commented:
Oh my god... i h ave just noticed that you want to do that in VBA.... so sorry... i overlooked at that...
I must be more careful while reading the Questions.. :(
what you are describing is generally called "PARALLEL PROCESSING" - and ANY version of VB (VB6 or VB.NET) is a very poor choice for attempting to do 'parallel processing'

Parrallel processing generally involves using Multiple preocessor 'threads' to carry out the 'simultaneous' calculations, and as said, earlier, VB is not well suited for doing multi-threaded applications.

AW
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.