Test or check if sheet exists
Some folk dislike this approach because of an “inappropriate” use of error handling, but I think it’s considered acceptable in VBA… An alternative approach is to loop though all the sheets until you find a match.
vba
How to fill color in a cell in VBA?
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
How to install Date Picker form control in Excel 2016
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?
How to install Date Picker form control in Excel 2016
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?
VBA array sort function?
Take a look here:Edit: The referenced source (allexperts.com) has since closed, but here are the relevant author comments: There are many algorithms available on the web for sorting. The most versatile and usually the quickest is the Quicksort algorithm. Below is a function for it. Call it simply by passing an array of values (string … Read more
Row count where data exists
If you need VBA, you could do something quick like this: This will print the number of the last row with data in it. Obviously don’t need MsgBox in there if you’re using it for some other purpose, but lastRow will become that value nonetheless.
Loop without Do error
You need an: in your code.
Error: “Could Not Find Installable ISAM”
Place single quotes around the Extended Properties: Try it, it really works.
Pass arguments to Constructor in VBA
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
Scraping data from website using vba
There are several ways of doing this. This is an answer that I write hoping that all the basics of Internet Explorer automation will be found when browsing for the keywords “scraping data from website”, but remember that nothing’s worth as your own research (if you don’t want to stick to pre-written codes that you’re … Read more