How to pass a value to an Excel 2010 workbook and return values

First Last

I have a parent Excel workbook that contains multiple columns on the sheet.

There is a 'Total Income' cell per column along with each column having 4 cells for Fed tax, State tax, Medicare, SS.

I have a child Excel workbook (1 sheet) where I have an income cell where if I enter an amount, 4 cells each will calculate the Fed tax, State tax, Medicare, SS amounts respectively.

When the parent is opened, I am looking for the parent to dynamically call the closed child for each of the 4 income cells (passing the parent’s Total Income cell’s amount) and have the child pass back the 4 calculated amounts respectively (Fed tax, Sate tax, Medicare, SS) to the parent and populate each column having 4 cells for the Fed tax, State tax, Medicare, SS.

I am not using VBA. Do I need to and learn it? If so, how do I access that functionality in Excel 2010?

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajkiran Swain3y ago

Accepted answer

To achieve this functionality, you would need to use VBA code in Excel 2010. Here are the high-level steps that you would need to follow:

Create a button in the parent Excel workbook that would trigger the VBA code.
Write VBA code to open the child Excel workbook, pass the Total Income cell's amount to the child, and get the 4 calculated amounts back from the child.
Use the VBA code to populate the 4 cells for Fed tax, State tax, Medicare, and SS in each column of the parent Excel workbook.

Here is some sample code that demonstrates how to open the child Excel workbook, pass values to it, and get values back:

Sub CallChildWorkbook()

    Dim childWorkbook As Workbook
    Dim incomeCell As Range
    Dim fedTaxCell As Range
    Dim stateTaxCell As Range
    Dim medicareCell As Range
    Dim ssCell As Range
    
    ' Open the child workbook
    Set childWorkbook = Workbooks.Open("C:\ChildWorkbook.xlsx")
    
    ' Get references to the cells in the child workbook
    Set incomeCell = childWorkbook.Worksheets("Sheet1").Range("A1")
    Set fedTaxCell = childWorkbook.Worksheets("Sheet1").Range("B1")
    Set stateTaxCell = childWorkbook.Worksheets("Sheet1").Range("C1")
    Set medicareCell = childWorkbook.Worksheets("Sheet1").Range("D1")
    Set ssCell = childWorkbook.Worksheets("Sheet1").Range("E1")
    
    ' Pass the Total Income cell's amount to the child workbook
    incomeCell.Value = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value
    
    ' Wait for the user to enter values in the child workbook
    MsgBox "Enter values in the child workbook and click OK"
    
    ' Get the calculated values from the child workbook
    ThisWorkbook.Worksheets("Sheet1").Range("B1").Value = fedTaxCell.Value
    ThisWorkbook.Worksheets("Sheet1").Range("C1").Value = stateTaxCell.Value
    ThisWorkbook.Worksheets("Sheet1").Range("D1").Value = medicareCell.Value
    ThisWorkbook.Worksheets("Sheet1").Range("E1").Value = ssCell.Value
    
    ' Close the child workbook
    childWorkbook.Close
    
End Sub

First Last3y ago
Ok...almost there.

I have multiple buttons each in its own cell on row 12.

I want to make this a dynamic build of the values in the Range function.

So based on one of the cells that a button is clicked (the button sits in cell C12). I get the 'cell letter'. In this case 'C' is inside the variable 'cellLetter'.

I want to dynamically build the Range with the Range function using that.

incomeCell.Value = ThisWorkbook.Worksheets("ForIrsIncomeAndExpenses").Range(""" & cellLetter & 12").Value

But I get an error. Application-define or object-defined error

Am I concatentating/building the statement in the 'Range' function correctly?

It should build:

incomeCell.Value = ThisWorkbook.Worksheets("ForIrsIncomeAndExpenses").Range("C12").Value