In VB object variables require the Set keyword to be assigned. Object properties that are objects also need to be Set. Runtime error 91 “object variable not set” is raised when the assignment doesn’t use that keyword. This is inherited from legacy Let keyword to assign values, and Set keyword to assign references; the Let eventually was deprecated (although still needed for defining properties) and the Set remained, … Read more
Yes. Or, if your Excel version is old enough,
Non VBA Solution: Use Conditional Formatting rule with formula: =ISNA(A1) (to highlight cells with all errors – not only #N/A, use =ISERROR(A1)) VBA Solution: Your code loops through 50 mln cells. To reduce number of cells, I use .SpecialCells(xlCellTypeFormulas, 16) and .SpecialCells(xlCellTypeConstants, 16)to return only cells with errors (note, I’m using If cell.Text = “#N/A” … Read more
I use Excel from Office 365 and want to use date picker form. Unfortunately, it is not listed in the list of additional controls (More Controls). How to include it into spreadsheet that I am developing? Is there something that I need to download and install?
I use Excel from Office 365 and want to use date picker form. Unfortunately, it is not listed in the list of additional controls (More Controls). How to include it into spreadsheet that I am developing? Is there something that I need to download and install?
Here’s a little trick I’m using lately and brings good results. I would like to share with those who have to fight often with VBA. 1.- Implement a public initiation subroutine in each of your custom classes. I call it InitiateProperties throughout all my classes. This method has to accept the arguments you would like … Read more
“Run-time error ‘3061’. Too few parameters. Expected 1.” I believe this happens when the field name(s) in your sql query do not match the table field name(s), i.e. a field name in the query is wrong or perhaps the table is missing the field altogether.
In the VBA editor, go to View, Toolbars, Customise… or right click on the tool bar and select Customise… Under the Commands tab, select the Edit menu on the left. Then approximately two thirds of the way down there’s two icons, Comment Block and Uncomment Block. Drag and drop these onto your toolbar and then you have easy access to highlight a block of code, and comment … Read more
Use the Application.Match function which allows for better ability to trap errors. When using the WorksheetFunction.Match, when a match is not found, it returns an error, which is what you’re experiencing. You could also potentially use the CountIf function: Neither of these approaches requires you to use the m1 variable, you can assign this variable within the True part of the If/Then statement, if you need … Read more
.) Check here. Basically do this: I’ll leave it to you to figure out the various error handling needed but these are among the error handling things I’d be considering: Check for an empty string being passed. Check for a string containing characters illegal in a file name/path 2.) How To Delete a File. Look at this. Basically … Read more