object variable not set: for loops
object variable not set: for loops
I really need quite a bit of help on this so I would really appreciate if you bear with me. I am new to VBA and hence I am facing difficulties running the code. It keeps returning me the error: object variable not set. May I know what is wrong with the code?
Thanks in advance for your help!
Sub sub_input (dicDat as Dictionary)
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 info as string
Dim currency As String: currency = vbNullString
Dim exchangeRate as String: exchangeRate = vbNullString
Dim remark as String: remark = vbNullString
For j =1 To 10
info= "info" & CStr(j)
info = Range("rInfoManual").value
currency= "dl_currency" & CStr(j)
exchangeRate = "exchange" & CStr(j)
Remark = "remarks" & CStr(j)
For i =LBound(vTemp,1)ToUBound(vTemp,1)
If vTemp(i,1)="currency"And dicDat(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
Currently dicData is defined as such: [However it is defined in Outlook VBA. Not sure how I should do to call the keys in Excel VBA]
Private Sub Code1()
Dim dicDat as Dictionary
Set dicDat = New dictionary
dicData.add "info1", item.info
dicData.add "info2", item.info
dicData.add "currency1", item.currency
dicData.add "currency2", item.currency
dicData.add "exchangeRate1", item.rate
dicData.add "exchangeRate2", item.rate
dicData.add "remark1", item.remark
dicData.add "remark2", item.remark
What happens is that I am inheriting an existing code that already runs properly with the keys info, currency, exchangeRate, remark etc. However, the keys now have changed to info (1-10), currency (1-10), etc. I need to amend the code to accommodate for the looping through of each keys. So it gets looped once, saved, then move to the 2nd set and so on.
Yes, the dictionary is created in another sub-procedure that is referenced to throughout the codes. I'm not sure how to refer to it as a variable. Could you show how?
– Lucky0683
Jul 2 at 9:02
Bit difficult to read through in the comment, but you could declare the dict as a global variable
Private dicDat As Scripting.Dictionary then set whatever keys and what not for it. Afterwards you could just reference it with dicDat( key) basically my point is, unless I'm missing something, it seems arbitrary here to have dictionary as a parameter when from your implemention it appears you'll always be using a specific one. Might be wrong though, I don't know how your program is exactly supposed to function. Just a guess– Rawrplus
Jul 2 at 9:06
Private dicDat As Scripting.Dictionary
dicDat( key)
Thank you, let me try it first. Can I just ask, is it possible to directly reference the dictionary that is defined in Outlook VBA in an Excel or the data has to be populated over to Excel again?
– Lucky0683
Jul 2 at 9:16
Just like with any object, it should be initalized
Set after it's declaration. Ideally edit your question with how your dicDat is currently defined. Might help others (myself included) answering the question– Rawrplus
Jul 2 at 9:18
Set
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Do you have the actually dictionary created somewhere when you're passing it to an argument? I doubt you do. You need to first initialize it and then set it to a new scripting dictionary - eg. check this guide. Seems kind of weird that you have a dictionary as a parameter as in whole? Why not just refer to your dictionary in a variable?
– Rawrplus
Jul 2 at 8:55