Forum Discussion
SteveNBethesda
Jun 14, 2022Copper Contributor
Macro Type Error
I just upgraded to Office 365 and now one of my macros is generating a Type Mismatch error. The macro is pasted below, and the offending code is highlighted in red, bold italics. I would greatly appr...
flexyourdata
Jun 14, 2022Iron Contributor
I think you can forgo the VBA and just use conditional formatting for this.
Nevertheless, if the VBA is important, then I would recommend a couple of things to straighten this out:
1) add Option Explicit to the top of the module - this will force you to declare all variables and reduce the likelihood of type errors, or at least increase your chances of avoiding them in the first place
2) dim processed as boolean
3) dim paid as boolean
4) Don't use separate variables for NotProcessed and NotPaid. If you dim processed and paid as boolean, you can just:
If processed and Not paid Then
... etc
Nevertheless, if the VBA is important, then I would recommend a couple of things to straighten this out:
1) add Option Explicit to the top of the module - this will force you to declare all variables and reduce the likelihood of type errors, or at least increase your chances of avoiding them in the first place
2) dim processed as boolean
3) dim paid as boolean
4) Don't use separate variables for NotProcessed and NotPaid. If you dim processed and paid as boolean, you can just:
If processed and Not paid Then
... etc
- SteveNBethesdaJun 15, 2022Copper Contributor
Thank you for your excellent suggestions! I hadn't thought about Conditional Formatting, but that's a great recommendation.
Declaring variables in one location is also a good habit, in fact, back in the day when I was a programmer, that was always my style and recommendation. However, I'm not a VBA programmer and typically fly by the seat of my pants! With that said, I changed all my variables to "variant" and that seems to work.
Thanks again for your comments and suggestions!!!
- mtarlerJun 16, 2022Silver ContributorA few more suggestions:
never use .select, selection. and ActiveCell. if you can avoid it. Just use the cell reference.
as for the line with an issue you don't even need the AND as the first IF statement is IF NOTPROCESSED so therefore the ELSEIF must have PROCESSED = TRUE so just drop that and use ELSEIF NOTPAID.
As already noted you can drop the extra PROCESSED & PAID variables
But most importantly, as suggested, just use conditional formatting.- SteveNBethesdaJun 16, 2022Copper ContributorThanks for the additional suggestions. I plan to create a copy of my spreadsheet and experiment with all the suggested REPLIES.
Have a great day!!!