How to Package an Excel Worksheet Calculations into A Function?

[semmanue]

Hi everyone,

I need some serious help! ... so any help would be awesome ...

I have an excell sheet that takes inputs (let's say A, B, C) for three cases (let's say 'baseline', 'low', 'high').

This sheet then does a bunch of calcs and produces ouputs (let's say X, Y, Z) for each of the cases (i.e. 'baseline', 'low', and 'high').

I would like to understand if it is possible to __PACKAGE__ the calcs in this sheet into some kind of "function" so that I can repeat it over and over again for different scenarios of the cases ('baseline', 'low', and 'hihg') ...

Your help is greatly appreciated.

Aside, my email is E-Mail Address Removed - Moderator if your posts can't go through.

Kind regards,
-s.

 

[milsky]

Thanks for responding to this, I am surprised that there are not more posts on the web on this topic as it would seem as if this is quite a logical function to want to have, nonetheless.

The code that I am using is exactly the same as from posts 5 and 6 above, to test the process I constructed a workbook with the two sheets in the one example and three sheets in the other, both of them fail at the "public sub loop" point with a compile error with variable not defined, again I have pretty decent excel experience but no VBA so perhaps I am doing something very basic wrong here.

Thanks

In responding this is what code I have pasted

Option Explicit

Public Sub LoopValues()

Dim iLast As Long
Dim iPtr As Long

With Sheets("Input")
iLast = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

For iPtr = 2 To iLast
Sheets("Engine").Range("D1") = Sheets("Input").Range("A" & CStr(iPtr)).Value
Do Until Application.CalculationState = xlDone: Loop
Sheets("Output").Range("A" & CStr(iPtr)) = Sheets("Main").Range("E1").Value
Next iPtr

MsgBox CStr(iLast - 1) & " sets of values calculated", vbOKOnly + vbInformation

End Sub

 

[Ruddles]

It's odd that Excel is complaining it doesn't understand xlDone as I thought this was part of the Excel 'object model'. To satisfy my curiosity, start Excel and press Alt-F11 to enter the VBA environment, then press Ctrl-G to open the Immediate Window. Type ?xlDone - that's a letter 'L' before the 'D' - and press Enter. It should return 0 (zero).

In any case, replace xlDone in your code with a zero and try running it again.

 

[milsky]

Ruddles

Thanks for following up on this, sorry I am only coming back to you on this now but I have been overseas for 2 weeks. I have tried as suggested but Alas realise that I am getting a different VBA environment from Excel for Mac... I logged into a Windows machine and have no issue entering this line of code (last post) and the response is 0... this comes up in the window titled "Immediate"

If I do the same on the Mac version I have no immediate window, but fire this up via "view" and enter ?xlDone and hit enter ... no response !!! Running the macro does not help... pasting this into the "code" environment neither (print then precedes this command)

Not sure where to from here, is Excell for mac so different ?

Thanks again

 

[milsky]

Sadly I think that I am coming accross as a complete newbie here but the reality is that i have pasted a variety of VBA code both into the immediate window as well as the code sheet and "nothing runs". As I have not run VBA code on MS excel (2011) for some time I must assume that there is some issue here. Is there anything you can think off that could give rise to this ?

 

[Ruddles]

I'm afraid my experiece doesn't extend to the Mac. Are there any Mac wizzes on here?

 

[milsky]

thanks for looking at this, so the VBA script runs so long as i delete the line:

Do Until Application.CalculationState = xlDone: Loop

however my concern is that it is likely that I use this code to run complex spreadsheets that may take quite some time to compute in which case the logic for this line will become clear... i think ? Is this the case or can this be avoided ? Are there any alternatives to xldone

I really appreciate the assistance here, this code is the difference between setting up a workbook with at least 60 different tabs for each of my product costings which will fall down should I need to change any of the variables. Being able to get this code running would mean that I only have to have a master-sheet with 5/6 variables per product type and using this code to populate "machine" tab which does all the detailed costings which are largely standard using these variables. The net outputs then feeding back into the same lines on the master-sheet. This then allows me to change either the master-sheet inputs or the "standard" mechanics of the "engine" without a very lengthy and detailed edit.

This code runs 100% in a Non-mac environment.... I have been all over the web trying to resolve this ... alas no luck other than a license of parallels/windows/office, which is crazy.

 

[Ruddles]

You've got it in a nutshell. That line merely waits for a Windows version of Excel to stop recalculating, so you just need to find a Mac equivalent: that would be my recommendation. In the Windows version, the constant xlDone has a value of zero, so you might try replacing xlDone with 0. At a a pinch you could try replacing the entire line with a command which will wait for a fixed length of time which you know is sufficient to allow recalculation to complete.

I don't know if anyone else can suggest anything?

 

[milsky]

Ruddles

thanks for this, interesting comments and I already tried these, I searched for alternatives to xlDone...nadda ! If you can think of any I would appreciate it but will also try you suggestion to substitute with a 0, I did try a few options on the delay/wait angle and nothing has worked as yet but I will have another look at this.

We are now working to see if we can nest some if statements prior to the cell input action, do you have any experience in this type of code..

Thanks again

Duncan