I don't need it, just a curiosity that popped in my head.
I do have a legit question since I'm very light in using objects. With late binding, what do I need to release? And If I need to release it all, is there a fast way to do so (since I'm currently at 9 objects).
Lots of separate posts, I'll answer them in order...as I'm not sure what you still do or don't need....
You virtually never need to set objects back to nothing in VBA. They terminate when they go out of scope. The one exception might be dao recordsets if numerous are opening in a loop, but even there, I'm pretty sure it's more about recordset.close versus set recordsetvariable=nothing.
If you're creating Classes and stuff like that, there might be different rules, but you're not doing that and I'm unsure as I have never truly used them.
But, not having to Set to Nothing notwithstanding, you still need to take practical steps like:
- Closing (application.quit) any program instance (Excel, etc) that you open.
- Closing open workbooks, etc
It currently errors on "Set rngDestination =" line
Error 1004: Application-defined or object-defined error
I was going to say check the values of lastcol and lastrow, to make sure that it makes sense to define the range as upper-left A2:##, but it looks like you got it working now? YES - that's a great example where (I personally) find it easier to just always define granular variables - because now you need something nice and short, like fixing
Cells() to instead of
ws.Cells()
For the format conditions - That's an example of a function that is too 'out there' to have memorized - so I'd be only relying on whatever insight the macro gives me (or if all else fails check the documentation ha) - what is the error?
Wait - you're using late binding and do NOT have any references to Excel checked in Tools>References? If "correct", then the problem is xlExpression
When late binding, ALL special little excel constants have to be converted to their numerical value.
When in doubt, I just google: "xlExpression enumeration" and usually the top result is the MS page with the corresponding Number value.
Somebody once provided a text file on the web you could paste in a module that would declare ALL excel constants, so that you could continue making references that looked like that even while late binding, but I can never find it any more