VBA question

paulo6891

Hi,

Sorry for troubling you with a very easy question, but I'm looking for some help with a simple bit of VBA.

In my excel sheet, I have 2 variables in Cells A1 and B1. I have a formulae in A2 and A3 and it ends up in giving me values in cells A3 and B3.

Then I want the code to update the Cells A1 and B1 with the values in A3 and B3. And so on. And I want to do this process 500 times.

It's really simple but I'm suffering from brain freeze!

It's essentially a solver but I just want to have my own code instead of using the solver.

Thanks in advance







Would something like this work or am I missing something?


Sub Solver()

Dim x As Integer

for x = 0 to 500 step 1
if x = 0 then
cells(1,1).value = cells(3,1).value
cells(1,2).value = cells(3,2).value
next x

End Sub

Mort5000

How about doing all the processing inside the function, with local variables, and then passing the result back out the cells when you have it?

paulo6891

would that make it run a lot quicker?

It's a pretty big set of equations that I have going on and I just wanted to simplify the example by using cells A1:B3

The Corinthian

Using a For/Next loop would do it. However, looking briefly at the sub you've written and your requrements a few issues jump out at me.

• Your 'For 0 to 500' is actually a loop of 501, not 500. If you want this done only 500 times then I'd change the '0' to '1'.
• You've an IF conditional that appears to serve no real purpose and isn't closed off.
• Your logic seems to be a bit confused. For example, you have a formulas in A2 and A3, that you say give you values A3 and B3. Is this what you mean, as it's a bit confusing to say the least? Otherwise you need to rewrite your requirements.

paulo6891

good point on the 1 to 500, was a bit late when i wrote that. It's just some artificial intelligence anyway so there's no real difference between 500 and 501 epochs.

I get an output in cells A2 and B2 using cells A1 and B1. Then the back propogation is calculated in cells A3 and B3.

It was meant to read: 'I have a formulae in A2 and B2 and it ends up in giving me values in cells A3 and B3.' There are formulae in all cells although that wasn't clear, especially with the typo.

I don't really know why I put in the 'if x = 0 then', I think I must have been too busy half looking at Children in Need! Although at the same time, I don't know too much about VBA. Did some C/C++ in the past, but that is a long time ago now! It's working now though anyway so cheers.

The Corinthian

paulo6891 wrote: »

I get an output in cells A2 and B2 using cells A1 and B1. Then the back propogation is calculated in cells A3 and B3.

It was meant to read: 'I have a formulae in A2 and B2 and it ends up in giving me values in cells A3 and B3.' There are formulae in all cells although that wasn't clear, especially with the typo.

Still not entirely clear. Are you saying (using simple pseudo-formulas) something like:

A1: "1"                         B1: "2"
A2: "=A1+2"                     B2: "=B1+1"
A2: "=A2"                       B2: "=B2"

Other than that, your approach, using a FOR/NEXT loop, appears correct, it's just the embedded calculation that needs to be pinned down.

paulo6891

Hi, thanks for the contribution. I probably made the initial requirement description a little unclear, there was probably no need for cells A3 and B3. Let me start again...

Basically, it is a back propogation system, i.e. it is essentially the same as the excel solver, but i wanted to build one myself.

In this simplified example, there are 2 inputs which give an output. There is also an ideal output.

Cells A1 and B1 contain the weights of the 2 inputs.

Cells A2 and B2 work out the output of the function given the 2 weights above and the 2 known inputs.

Cells A3 and B3 work out what the weights in the next iteration will be, it adjusts them basically by examining the difference between the output and the ideal output.

It does this loop 500 times, and with every iteration, the output gets closer and closer to the ideal output.


That's basically what the program does. I tried simplifying it a bit (but failed), there are around 10,000 cells with all these formulae in them. Despite doing some C/C++ in college, I'm very rusty with the VBA as I had to give it up when I moved to mac (and excel for mac didnt have macros!).

I pasted some of the code above from elsewhere (hence a few mistakes), but I ended up using:

Sub Solver()

Dim x As Integer

for x = 1 to 500 step 1

cells(1,1).value = cells(3,1).value
cells(1,2).value = cells(3,2).value
next x

End Sub

This has been doing exactly what I need it to do. The only problem is that, given the size of the samples, it takes on average 90 seconds to run the 500 loops. And I need to do this around 100 times, so it takes a bit of time. It's not the end of the world though.

Do you think that putting some of this code into the macro would speed things up a bit instead of having all of the formulae in excel and changing some inputs there?

I have an idea about how I could go about that, but I'll mess around with it before wasting people's time.

Thanks

The Corinthian

paulo6891 wrote: »

It does this loop 500 times, and with every iteration, the output gets closer and closer to the ideal output.

By the sound of things, a potentially better approach for you would actually just involve a different mathematical formula; specifically the use of calculus, as what you appear to be looking for is a derivative.

The only problem is that, given the size of the samples, it takes on average 90 seconds to run the 500 loops. And I need to do this around 100 times, so it takes a bit of time. It's not the end of the world though.

This is because Excel is not the most efficient calculator memory-wise and macro code is not compiled, meaning that it requires interpretation at runtime, which is resource heavy and thus will cause execution to be slow.

Given you already know C/C++, had you considered writing it up in one or the other of these? If written as a compiled console executable, I'd imagine it would be significantly faster than as an Excel macro.

Code Blocks is a good, and free, IDE that you could use to this end.

paulo6891

Thanks for the link, that will definitely be handy!

I'k working on v1 of my code, I think that I will stick with VBA. Mainly because, I want to have some working knowledge of it for the cv, gets mentioned in a lot of job requirements! But yeah, C/C++ is definitely the way forward for me.

Do you reckon it would be worthwhile learning C#? I would have to brush up on my c/c++ so i wonder if I would be better off putting the time into #, which would be more useful?

Thanks for your help

The Corinthian

paulo6891 wrote: »

Do you reckon it would be worthwhile learning C#? I would have to brush up on my c/c++ so i wonder if I would be better off putting the time into #, which would be more useful?

Despite it's name, C# is probably more similar to Java than C or C++. I'd certainly still learn C, as it's not too difficult to learn and many languages derive their syntax from it. Mastering C++ is a much larger investment, so I can't say if it would be worth your while going down that rabbit hole.

Nonetheless, C# is in pretty high demand and rates/salaries for it are typically buoyant. Same can be said for Java. Depends mainly on what you want to do in the longer term.