如何绕循环VBA循环 [英] How do I loop around loops VBA

问题描述

我非常感谢你的帮助，因为我是VBA的新手。我有一个Infopath表单，基本上以XML格式存储用户填写的数据。然后，它将传递给Excel来处理数据。有些数据在整个过名称和日期。但是，由于表格试图容纳在同一日期内记录的多个交易，因此某些数据对于每个交易是唯一的。价格/货币。



因此我试图创建一个代码，使每个事务所独有的数据从1到10循环（可以是最大的事务量）记录在同一表格内）现在它们被重命名为价格1，价格2等。所有这些，相同的数据（例如名称和日期）仍然保留在表格中，每笔交易都保存在数据库在继续下一个事务的数据之前。如果没有更多事务，代码应该停止运行。



当我点击运行时，它会提示我输入宏的名称并且根本不运行。我不知道怎么去编辑它。真的很感谢你的帮助。谢谢！







调用此代码的其他代码为：

调用此代码的其他代码是：
 Sub sub_form（dicData As Dictionary）
 
 Dim dicTransaction As Dictionary 
 Dim key As变体
 Dim key2 As Variant 
 For Each key在dicData.Keys 
 blnWait = True 
如果InStr（key，REPEAT_KEY）则
设置dicTransaction = dicData（key） 
 dicTransaction（exchangeRate）= dicTransaction（exchangeRate）/ 100 
 
调用sub_PreLoad（dicTransaction）
结束如果
下一个
 
 set dicTrade = Nothing 
 Set key = Nothing 
 Set key2 = Nothing 
 
 
 sub_Preload过程如下所示：
 
 Sub sub_PreLoad （dicData As Dictionary）
 
 Dim i As Integer：i = 1 
 Dim j As Integer 
 Dim no As Integer 
 
 Dim vTemp As Variant 
 vTemp = Range（Range（rInputStart）。Offset（1,1），_ 
 fnc_LastCellInCol（Range（rinputstart））。Offset（-1） .Offset（0,1））。value 
 Dim vKey As Variant 
 
 For i = LBound（vTemp，1）To UBound（vTemp，1）
 If Not vTemp（ i，1）= vbNullString然后
如果没有> 1那么
 vFormKey =拆分（vTemp（i，1），|）
对于j = LBound（vKey）到UBound（vKey） ）
如果dicData.Exists（vKey（j））那么
范围（rInputStart）。偏移（i，2）.value = dicData_ 
（vKey（j））
结束如果
下一个j 
否则：调用sub_inputData 
结束如果
下一个i 
 
设置vTemp = Nothing 
设置vKey = Nothing 
 
 End Sub 

（Ps：我在其他论坛上交叉发布，因为我真的很想寻求他是其他人的专家LP。我很抱歉。



我尝试过：



我试过以下代码：

  Sub  sub_inputData（ dicData AsDictionary）
  Dim  ws  As 工作表：设置 ws = ActiveSheet 
  Dim  i  As  整数 
  Dim  j 作为 整数 
  Dim  vTemp 作为 变体 
范围（  rInputStart）。Parent.Calculate 
 vTemp =范围（范围（  rInputStart）。偏移量（ 1 ），_ 
范围（  rInputStart).结束（xlDown）.Offset（ 0 ， 2 ））。value 
 
  Dim 价格  长 
  Dim 货币作为字符串：currency = vbNullString 
  Dim  exchangeRate  as 字符串：exchangeRate = vbNullString 
 < span class =code-keyword> Dim  remark  as 字符串：remark = vbNullString 
 
 对于 j = 1To10 
 
 price = price& dicData（  price&  CStr  （j））&   | 
 price = price（  rPriceManual）。value ' 此时，将调用其他函数将价格转换为不同的货币等，只要价格正确插入表中 
 
 currency = currency& dicData（  dl_currency& CStr（j））&   | 
 exchangeRate =（exchangeRate& dicData（  exchange_rate& CStr（j））&   |）/ 100 
备注=评论& dicData（ 备注& CStr（j））&   | 
 
 对于 i = LBound（ vTemp， 1 ）ToUBound（vTemp， 1 ）
 如果 vTemp（i， 1 ）=   currency 和 dicData（dl_currency）<> vbNullString 然后 
 vTemp（i， 3 ）=货币
  Endif  
 如果 vTemp（i， 2 ） =  备注 然后 
 vTemp（i， 3 ）=备注
  EndIf  
 如果 vTemp（i， 2 ）=   exchangeRate 然后 
 vTemp（i， 3 ）= exchangeRate 
  EndIf  
 下一步 i 
 下一步 j 
 结束  Sub  

决方案

在Excel可以点击查看 - >宏菜单项显示可用的宏。然后，您可以根据需要运行，调试（步入）或编辑它们。

Quote：

我点击运行，它提示我输入宏的名称，并且根本不运行。我不知道怎么去编辑它。真的很感谢你的帮助。谢谢！

嗯......看看你的程序：

  Sub  sub_inputData（dicData  As  Dictionary）

它希望字典对象作为输入参数。



如果你想运行/执行一个程序，你必须创建另一个一，将在输入参数中调用 sub_inputData 过程：

 < span class =code-keyword> Sub  Whatever（）
 ' 声明字典对象稍后使用 
  Dim  oDict  As  Dictionary 
  启动变量 
 设置 oDict = 新字典
 ' 填充字典对象 
 oDict.Add  键， 值 
 '  ...  
 
 ' 在sub_inputData过程中使用字典对象 
 sub_inputData oDict 
 
 结束  Sub  

欲了解更多详情，请参阅：语言参考VBA | MSDN [ ^ ]

Hi,

I really appreciate your help in this as I am new to VBA. I have an Infopath form that basically stores data that users fill in, in XML format. Then, it will be passed to Excel to treat the data. Some data remains constant throughout e.g. Names and Date. However, as the form is trying to accommodate multiple transactions to be recorded within the same date, some data is unique to each transaction e.g. Price/Currency.

As such I am trying to create a code such that data that is unique to each transaction will be looped over from 1 to 10 (max amount of transactions that can be recorded within the same form) and they are now renamed as 'Price 1', 'Price 2' etc. All this while, data that is the same (e.g. name and date) still remains in the table and each transaction is saved into the Database before moving on to the next transaction's data. If there is no more transaction, the code should stop running.

When i click run, it prompts me for the name of the macro and does not run at all. I'm not sure how to go about editing it too. Will really appreciate your help. Thank you!



The other codes that is calling this code are:

The other code that is calling this code is this: 
    Sub sub_form (dicData As Dictionary)
    
    Dim dicTransaction As Dictionary
    Dim key As Variant
    Dim key2 As Variant
    For Each key In dicData.Keys
        blnWait = True
        If InStr(key, REPEAT_KEY) Then
            Set dicTransaction = dicData(key)
            dicTransaction("exchangeRate") = dicTransaction("exchangeRate") / 100
            
            Call sub_PreLoad(dicTransaction)
         End If
    Next
    
    Set dicTrade = Nothing
    Set key = Nothing
    Set key2 = Nothing


The sub_Preload procedure looks like this: 

    Sub sub_PreLoad(dicData As Dictionary)
       
    Dim i As Integer: i = 1
    Dim j As Integer
    Dim no As Integer
    
    Dim vTemp As Variant
    vTemp = Range(Range("rInputStart").Offset(1, 1), _ 
    fnc_LastCellInCol(Range("rinputstart")).Offset(-1).Offset(0, 1)).value
    Dim vKey As Variant
    
    For i = LBound(vTemp, 1) To UBound(vTemp, 1)
        If Not vTemp(i, 1) = vbNullString Then
            If no>1 Then
                vFormKey = Split(vTemp(i, 1), "|")
                For j = LBound(vKey) To UBound(vKey)
                    If dicData.Exists(vKey(j)) Then
                        Range("rInputStart").Offset(i, 2).value = dicData_
                        (vKey(j))
                    End If
                Next j
            Else: Call sub_inputData
        End If
    Next i
    
    Set vTemp = Nothing
    Set vKey = Nothing
    
    End Sub

(P.s: I have cross-posted in other forums because I am really anxious to seek others' experts help. I'm so sorry about that.

What I have tried:

I have tried the following code:

Sub sub_inputData(dicData AsDictionary)
Dim ws As Worksheet: Set ws =ActiveSheet
Dim i As Integer
Dim j As Integer
Dim vTemp As Variant
Range("rInputStart").Parent.Calculate
vTemp =Range(Range("rInputStart").Offset(1),_
Range("rInputStart").End(xlDown).Offset(0,2)).value

Dim price as Long  
Dim currency As String: currency = vbNullString
Dim exchangeRate as String: exchangeRate = vbNullString 
Dim remark as String: remark = vbNullString 

For j =1To10

price = price & dicData ("price" & CStr (j))&"|"
price = price ("rPriceManual").value 'and at this point, other functions will be called to convert the prices to different currencies etc,aslongas the prices are inserted into the table correctly

currency = currency & dicData("dl_currency"&CStr(j))&"|"
exchangeRate =(exchangeRate & dicData("exchange_rate"&CStr(j))&"|")/100 
Remark= remark & dicData("remarks"&CStr(j))&"|"

  For i =LBound(vTemp,1)ToUBound(vTemp,1)
   If vTemp(i,1)="currency"And dicData(dl_currency)<> vbNullString Then
      vTemp(i,3)= currency
   Endif 
   If vTemp(i,2)="remark"Then
     vTemp(i,3)=Remark
   EndIf
   If vTemp(i,2)="exchangeRate"Then
     vTemp(i,3)= exchangeRate
   EndIf
  Next i
Next j
End Sub

解决方案

In Excel you can click the View -> Macros menu item to show the available macros. You can then Run, Debug (Step into) or Edit them as required.

Quote:

When i click run, it prompts me for the name of the macro and does not run at all. I'm not sure how to go about editing it too. Will really appreciate your help. Thank you!

Well... Take a look at your procedure:

Sub sub_inputData(dicData As Dictionary)

It expects a dictionary object as an input parameter.

If you would like to run/execute a procedure, you have to create another one, which'll call sub_inputData procedure within input parameter:

Sub Whatever()
    'declare dictionary object for later use
    Dim oDict As Dictionary
    'initiate variable
    Set oDict = New Dictionary
    'fill dictionary object 
    oDict.Add "Key", "Value"
    '...

    'use dictionary object inside sub_inputData procedure
    sub_inputData oDict

End Sub

For further details, please see: Language reference VBA | MSDN[^]

这篇关于如何绕循环VBA循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！