将数据从子子传输到功能，再传输回主子

Transfer data from sub-Sub to Function and back to main Sub

提问人：Nikita Pelevin 提问时间：5/28/2021 更新时间：6/28/2021 访问量：144

问：

我正在尝试编写在函数中填充数组并将结果返回给主 Sub 的代码。但是这个函数是从另一个 Sub （sub-Sub） 调用的。为了了解这将如何工作，我尝试一步一步地进行。并编写了以下代码：

Sub CATMain()
    Dim val1
    Call WalkDownTree() 'Here I call Sub to walk down Product Tree in CATIA
    val1 = ParamTable(PartNumber, Name, Material, Texture, Color, Quantity)
    For i=0 To UBound(val1)
        MsgBox val1(i)
    Next

End Sub

Sub WalkDownTree() 'Simplified code of the walk down tree to understand data transfer
    PartNumber = "PartNumber"
    Name = "Name"
    Material = "Material"
    Texture = "Texture"
    Color = "Color"
    Quantity = 1
    Call ParamTable(PartNumber, Name, Material, Texture, Color, Quantity)
End Sub

Function ParamTable(PartNumber, Name, Material, Texture, Color, Quantity) 'Simplified array filing code. At original code I get all data from Part
    Dim BOMTable(6,1000)
    BOMTable(1,k) = PartNumber 
    BOMTable(2,k) = Name
    BOMTable(3,k) = Material
    BOMTable(4,k) = Texture
    BOMTable(5,k) = Color
    BOMTable(6,k) = 1
    ParamTable = BOMTable
End Function

但是我在“MsgBox val1（i）”行有错误：“下标超出范围”。 我错过了什么？ 当函数从子子中填充时，也许存在更简单的方法将数组从函数转移到主子？

VBScript CATIA

评论

0赞 Geert Bellekens 5/28/2021
如果是二维数组，您希望在中找到什么。您尚未指定第二个维度。val1val1(0)
0赞 Nikita Pelevin 5/28/2021
@GeertBellekens，你说得对！谢谢！但是现在“MsgBox”的数组是空的。显然，我在 Function 和 Sub 之间错误地传输了数据。

答：

-1赞 DJakub 6/25/2021 #1

我建议不要在变量声明上懈怠，即使它不是运行所必需的。理解是必要的。

保存所有数据的主要全局变量应声明在顶部

Option Explicit
Dim BOMData() As Variant

然后编写 CATMain（）

Sub CATMain()

    Dim ItemCount As Integer 'This would be how many rows i will have in BOM or how many parts i will read i have to get this info from CATIA
    ReDim BOMData(0 To ItemCount - 1, 0 To 5) As Variant 'This is making the collection
    Dim Row As Integer
    Dim Col As Integer

    For Row = 0 To ItemCount - 1 'populate the BOM collection
       Call WalkDownTree(Row)
    Next
  
    Dim TempStr As String
    For Row = 0 To ItemCount - 1 'just write each row to msgbox
        For Col = 0 To 5
            TempStr = TempStr & BOMData(Row, Col) & " "
        Next
        Call MsgBox(TempStr)
        TempStr=""
    Next

End Sub

这就是填充集合的潜艇的样子......您正在遍历行，或者可能是项目（产品、部件等）

Sub WalkDownTree(Row As Integer)

    BOMData(Row, 0) = "PartNumber"
    BOMData(Row, 1) = "Name"
    BOMData(Row, 2) = "Material"
    BOMData(Row, 3) = "Texture"
    BOMData(Row, 4) = "Color"
    BOMData(Row, 5) = 1

End Sub

此解决方案的优势可以为您带来像这样简单的 excel 导出

Sub WriteToExcel()

    Dim ExlApp As Excel.Application
    Set ExlApp = GetObject(, "EXCEL.Application")
    
    Dim ExlWorkBook As Workbook
    Set ExlWorkBook = ExlApp.Workbooks.Open("C:\Test.xls")
    
    Dim ExlSheet As Worksheet
    Set ExlSheet = ExlWorkBook.Worksheets.Item(1)
    ExlSheet.Range("A1").Resize((UBound(BOMData, 1) - LBound(BOMData, 1) + 1), (UBound(BOMData, 2) - LBound(BOMData, 2) + 1)).Value = BOMData

End Sub

编辑

这是 VBScript 版本。但是，如果您想认真开发某些东西，我建议为 Catia 使用 VBA 或 VB .Net 语言。如果没有必要，使用 VBScript 只是在折磨自己

    Dim BOMData()
    Sub CATMain()

    Dim ItemCount 
    ItemCount = 10 'how many items you have
    ReDim BOMData (ItemCount - 1, 5) 
    Dim Row 
    Dim Col 

    For Row = 0 To ItemCount - 1 'populate the BOM collection
       Call WalkDownTree(Row)
    Next
  
    Dim TempStr
    For Row = 0 To ItemCount - 1 'just write each row to msgbox
        For Col = 0 To 5
            TempStr = TempStr & BOMData(Row, Col) & " "
        Next
        Call MsgBox(TempStr)
        TempStr=""
    Next

    End Sub
    
    Sub WalkDownTree(Row)

    BOMData(Row, 0) = "PartNumber"
    BOMData(Row, 1) = "Name"
    BOMData(Row, 2) = "Material"
    BOMData(Row, 3) = "Texture"
    BOMData(Row, 4) = "Color"
    BOMData(Row, 5) = 1

    End Sub

评论

0赞 Geert Bellekens 6/26/2021
问题是关于 VBscript，而不是 VBA。您的代码不是有效的 VBScript。
0赞 Nikita Pelevin 6/28/2021
我有一个解决方案。看看我最后的评论。
0赞 DJakub 6/28/2021
@Geert Bellekens true，我没有注意到 vbscript 标签......
0赞 Nikita Pelevin 6/28/2021 #2

一段时间后，我有了一个解决方案 此代码有效：

Dim PartNumber, Name, Material, Texture, Color, Quantity, k
Dim BOMTable(6,1000)

Sub CATMain()
    Dim val1
    Call WalkDownTree()
    val1 = ParamTable(PartNumber, Name, Material, Texture, Color, Quantity, k)
    For i = 1 To UBound(val1, 1)
        For j = 1 To k-1
        MsgBox val1(i,j)
        Next
    Next

End Sub

Sub WalkDownTree()
    For k = 1 To 3
        PartNumber = "PartNumber" & k
        Name = "Name" & k
        Material = "Material" & k
        Texture = "Texture" & k
        Color = "Color" & k
        Quantity = 1
        Call ParamTable(PartNumber, Name, Material, Texture, Color, Quantity, k)
    Next
End Sub

Function ParamTable(PartNumber, Name, Material, Texture, Color, Quantity, k)
    BOMTable(1,k) = PartNumber 
    BOMTable(2,k) = Name
    BOMTable(3,k) = Material
    BOMTable(4,k) = Texture
    BOMTable(5,k) = Color
    BOMTable(6,k) = Quantity
    ParamTable = BOMTable
End Function

上一个：调用子错误 800A0414 VBS 时无法使用括号

下一个：jQuery 中的 FadeIn 产品卡动画无法正常工作