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.

 

[Ruddles]

You can convert all your calculations into Visual Basic - actually Visual Basic for Applications (VBA) - and call the VBA function repeatedly as you describe. The programming effort to achieve this might be non-trivial and the fact that you're having to ask the question indicates that you're likely to be on a steep learning curve if you intend to carry it out yourself.

The other possible approach would be to have a series of inputs in a separate worksheet, inject these inputs into your existing worksheet in turn so that it can produce the outputs, then copy the outputs back to the separate worksheet.

Does your existing worksheet have a clearly-defined set of cells where the inputs are entered and a clearly-defined set of cells where the outputs are displayed?

 

[semmanue]

Ruddles -- Thanks for your help on this front -- much appreciated!


A few points:

1. Ideally, I would like the "ENGINE" worksheet to be encapsulated by a single VBA function ... however, as you mentioned, this may be non-trival

2. Hence, my thoughts were as you suggested in the sense that I would have a "wrapper" function that would:
a. take the "range" inputs from the "input" sheet
b. send the inputs to the "ENGINE" sheet
c. take the outputs from the "ENGINE" sheet and put them into an "OUTPUT" sheet

3. To your point, my worksheet does have clearly deifnined set of cells where the inputs are entered and where the outputs should go


Question:
Could I send you a "sanitized" version of what I am doing to give you flavour of what I am doing?

Aside:
I am currently reading on how to do point-(3).

Kind regards,
-s.

 

[semmanue]

I am having the most difficult time simply copying from one spreadsheet to another!!!! god!


I just want a function that:
1. takes in a RANGE from one sheet (the "INPUT" sheet)
2. copies it to to specific cells in another sheet (the "ENGINE" sheet)
3. copies the output to another sheet (the "OUPUT" sheet)


Ideas?
-s.

 

[Ruddles]

I'm not happy working off-forum in that way: it places you in the hands of my availability and I think it's a bad idea to exclude others from contributing potentially better solutions or enhancements. But I'm happy to give you an idea on how you could proceed.

For example, if you have a sheet containing your calculations called Main which expects input in D1 and places its output in E1, create a sheet called Values with a list of inputs in column A and run the following code:-

Option Explicit
 
Public Sub LoopValues()
 
  Dim iLast As Long
  Dim iPtr As Long
 
  With Sheets("Values")
    iLast = .Cells(.Rows.Count, 1).End(xlUp).Row
  End With
 
  For iPtr = 2 To iLast [COLOR=green]' start from row 2 to allow for column headings[/COLOR]
[COLOR=blue][B]    Sheets("Main").Range("D1") = Sheets("Values").Range("A" & CStr(iPtr)).Value[/B][/COLOR]
    Do Until Application.CalculationState = xlDone: Loop
[COLOR=red][B]    Sheets("Values").Range("B" & CStr(iPtr)) = Sheets("Main").Range("E1").Value[/B][/COLOR]
  Next iPtr
 
  MsgBox CStr(iLast-1) & " sets of values calculated", vbOKOnly + vbInformation
 
End Sub

The blue line of code takes an input from Values and places it in the appropriate cell in Main, the following line waits for all the Excel calculations to complete, and the red line of code places the result back in Values.

You would modify the blue and red bits to suit. For example, if Main needed its inputs to be in A13, J44 and Z16, and it placed its outputs in E13 and T20, your Values sheet would contain inputs in columns A, B and C and the outputs would appear in columns D and E. In that case the VBA code would be:-

  For iPtr = 2 To iLast
    Sheets("[COLOR=red][B]Main[/B][/COLOR]").Range("[B][COLOR=red]A13[/COLOR][/B]") = Sheets("[B][COLOR=#0000ff]Values[/COLOR][/B]").Range("[B][COLOR=blue]A[/COLOR][/B]" & CStr(iPtr)).Value
    Sheets("[COLOR=red][B]Main[/B][/COLOR]").Range("[B][COLOR=red]J44[/COLOR][/B]") = Sheets("[B][COLOR=#0000ff]Values[/COLOR][/B]").Range("[B][COLOR=blue]B[/COLOR][/B]" & CStr(iPtr)).Value
    Sheets("[COLOR=red][B]Main[/B][/COLOR]").Range("[B][COLOR=red]Z16[/COLOR][/B]") = Sheets("[B][COLOR=#0000ff]Values[/COLOR][/B]").Range("[B][COLOR=blue]C[/COLOR][/B]" & CStr(iPtr)).Value
    Do Until Application.CalculationState = xlDone: Loop
    Sheets("[COLOR=blue][B]Values[/B][/COLOR]").Range("[B][COLOR=blue]D[/COLOR][/B]" & CStr(iPtr)) = Sheets("[COLOR=red][B]Main[/B][/COLOR]").Range("[B][COLOR=red]E13[/COLOR][/B]").Value
    Sheets("[B][COLOR=#0000ff]Values[/COLOR][/B]").Range("[B][COLOR=blue]E[/COLOR][/B]" & CStr(iPtr)) = Sheets("[COLOR=red][B]Main[/B][/COLOR]").Range("[B][COLOR=red]T20[/COLOR][/B]").Value
  Next iPtr

Can I suggest you set up a new workbook with two sheets in it, Main and Values, set up some simple calculations in Main, create some sample inputs in Values, and give the code a try. Satisfy yourself that you understand what's going on and then see if it's sensible and feasible to apply this sort of solution to your existing workbook.

Get back to me if anything's not clear or you need any additional guidance.

 

[Ruddles]

Sorry, I started writing my post timed 13:09 before you posted yours at 13:04.

It would look something like this:-

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
[COLOR=black]   Sheets("[B]Engine[/B]").Range("[B]D1[/B]") = Sheets("[B]Input[/B]").Range("[B]A[/B]" & CStr(iPtr)).Value[/COLOR]
[COLOR=black]   Do Until Application.CalculationState = xlDone: Loop[/COLOR]
[COLOR=black]   Sheets("[B]Output[/B]").Range("[B]A[/B]" & CStr(iPtr)) = Sheets("[B]Main[/B]").Range("[B]E1[/B]").Value[/COLOR]
  Next iPtr
 
  MsgBox CStr(iLast-1) & " sets of values calculated", vbOKOnly + vbInformation
 
End Sub

That would take values from column A of Input, put them in the 'input' cell of Engine, wait for the 'output' cell to be calculated, then take that cell and place it in column A of the Output sheet.

 

[semmanue]

R,

Thanks for all your efforts on this -- I am indebted.

I will examine your code now ... In the meantime, I tried to hack a solution that is just giving me problems.

As I want to learn, could you point out the errors in the following ... Simple copying data from one range to another range in the same sheet ....


On the input sheet ...
=================
baseline 1 2 3
Low 4 5 6
High 7 8 9



Public Function result (source As Range) As Range
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("INPUT & OUTPUT")
Set result = ws.Range("F12")

source.Copy result
End Function

 

[Ruddles]

What are result and source declared as? You only need to Set the value of objects, not simpe variables. I would recommend using the Option Explicit directive at the top of all modules to force you to DIM all your variables.

I tend not to use copy/paste to copy single cells - I would just assign the value of the source cell to the target cell. Check the way my code copies the values from one sheet to another.

 

[milsky]

I am not sure if anyone is still available to answer this thread but this is a function that I have been trying to figure out for a long time as i run large spreadsheets with lots of computational invormation in them and this is EXACTLY what I am looking for.

My problem is that I am not a VB user but have tried this, have setup a sample workbook with the above and pasted this code into the vb editor (sorry I am a real newbie here) and run the code and get the following error..

Compile error, and the following two lines of code hilighted

Public Sub LoopValues()

and
xlDone

I think I am failing at a basic level

I would appreciate a little assistance here

thanks a million

 

[Ruddles]

Can you post your entire code here? That way we can run it and see if we get the same results.

Also let us know what your worksheets are called.