表工作簿和函数工作簿在excel中进行通信的代码-后果 [英] code for communicating between a table workbook and a function workbook in excel -the aftermath

问题描述

几天前，我发布了一个问题，目的是找出如何编写可让我在函数工作簿和输入/表工作簿之间进行通信的代码.@DarthSpeedious帮助我提出了一个代码，该代码在另一个工作簿中计算后返回了正确的值.

A couple of days ago I posted a question in order to find out how i could write a code that let me communicate between a function workbook and a input/table workbook. I was graciously helped by @DarthSpeedious to come up with a code that returned the correct value after computing in another workbook.

但是，它仅返回一个计算.为了尝试循环上次获得帮助的代码，我尝试为其中一个输入变量创建一个数组，以用作循环过程的计数器.运行代码后，VBA会冻结我，因此我无法调试代码.我尝试了几种构造数组和循环参数的不同方法，但是没有一种成功.

However, it only returned a single calculation. In order to try to loop the code that I was helped with the last time, I tried to create an array for one of the input variables to use as an counter for the loop process. Upon running the code, VBA keeps freezing up on me, and thus I have no way of debugging the code. I've tried several different ways of constructing both the array and the loop argument but none has been successful.

有人可以帮助我吗?

Public Sub Decompact()

    ' Gets input from another workbook
    Dim wb1 As Workbook                                                                             ' Declaring wb1 and wb2 as variable of type Workbook
    Dim wb2 As Workbook

    Set wb1 = Workbooks.Open("decompaction along exmpleline.xlsx")                                  'Note: In order to access data from another workbook, it should be open.
    Set wb2 = ThisWorkbook                                                                          'ThisWorkbook: refrence to the workbook having this code


    'Setting up array for y1
    last_row = Range("U3").End(xlDown).Row

    Dim y1()
    ReDim y1(last_row - 2, 1)

    'Storing values in array
    For i = 3 To last_row - 2
        y1(i, 0) = Range("U" & i + 2)
    Next


    'Looping decompaction
    For z = LBound(y1) To UBound(y1)

        wb1.Sheets("Shaly sst").Range("B3") = wb2.Sheets("Sheet1").Range("U" & i + 1)                   'Accept input of variable y1 in Ui and store it in cell B3 of book1
        wb1.Sheets("Shaly sst").Range("B2") = wb2.Sheets("Sheet1").Range("V" & i + 1)                   'Accept input of variable y2 in Vi and store it in cell B3 of book1

        wb2.Sheets("Sheet1").Range("AC" & i + 1) = wb1.Sheets("Shaly sst").Range("H3")                  'Output from book1 in H3 to a cell ACi in book2

     Next z

End Sub

图资源: https://db.tt/AxFG9snn

推荐答案

nvm，想通了:

Sub Decompact3()
' Gets input from another workbook
Dim wb1 As Workbook                                                         ' Declaring wb1 and wb2 as variable of type Workbook
Dim wb2 As Workbook
Dim LastRow As Long

Set wb1 = Workbooks.Open("decompaction along exmpleline.xlsx")                                      'Note: In order to access data from another workbook, it should be open.
Set wb2 = ThisWorkbook                                                      'ThisWorkbook: refrence to the workbook having this code

    With wb2.Sheets("Sheet1")
        LastRow = .Range("U" & .Rows.Count).End(xlUp).Row
    End With

    For i = 3 To LastRow
        wb1.Sheets("Shaly sst").Range("B3") = wb2.Sheets("Sheet1").Cells(i, 21)               'Access value stored in cell A1 of sheet1 in book1 and stre it in cell A1 of book2
        wb1.Sheets("Shaly sst").Range("B2") = wb2.Sheets("Sheet1").Cells(i, 22)

        wb2.Sheets("Sheet1").Cells(i, 29) = wb1.Sheets("Shaly sst").Range("H3")        'Store the output (cell C1 of book2) in cell C1 of book1
    Next i

End Sub

再次感谢DarthSpeedious的帮助！

Thanks again to DarthSpeedious for his help!

这篇关于表工作簿和函数工作簿在excel中进行通信的代码-后果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！