Average in VBA in Excel

Look closely at what you’re trying to do in this code: You’re assigning a “long” value to each of l and m and then calling them in the average function as if they were references to a cell. I suggest that you add a range variable, assign the location of the data you want to … Read more

‘Cannot find DAO350.dll’ file error VB6

The DA0350.dll must be registered. Try this: On the Visual Basic 6.0 CD (or whatever your installation source is), locate the Dao350.dll file, and then copy it to the directory c:\program files\common files\microsoft shared\DAO. (The Dao350.dll file is located in the \os\system folder on the Visual Basic 6.0 CD.) To register this file, click Start, … Read more

What is the difference between “Form Controls” and “ActiveX Control” in Excel 2010?

Google is full of information on this. As Hans Passant said, Form controls are built in to Excel whereas ActiveX controls are loaded separately. Generally you’ll use Forms controls, they’re simpler. ActiveX controls allow for more flexible design and should be used when the job just can’t be done with a basic Forms control. Many user’s computers by default won’t trust ActiveX, and it will be disabled; … Read more

Compare Dates in VBA

Here’s how you can fix your code. Of course it’s not the best way to do date calculations, and you’ll also have to validate that there is text in the 2 text boxes, and maybe even use CDate() to parse the text to date values, but this will work for your current situation.

VBA: Run time error ’91’?

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

How to Clear Office Clipboard with VBA

Saw this on another post, and I have tested it with Word VBA. Just copy and paste into your code where ever you need to clear the Clipboard. Another thing I noticed is that when I .Quit a program, say Excel, it keeps asking me if I want to keep the data is the Clipboard. A work … Read more

macro run-time error ‘9’: subscript out of range

“Subscript out of range” indicates that you’ve tried to access an element from a collection that doesn’t exist. Is there a “Sheet1” in your workbook? If not, you’ll need to change that to the name of the worksheet you want to protect.

How to detect if user select cancel InputBox VBA Excel

If the user clicks Cancel, a zero-length string is returned. You can’t differentiate this from entering an empty string. You can however make your own custom InputBox class… EDIT to properly differentiate between empty string and cancel, according to this answer. Your example Would tell the user they canceled when they delete the default string, … Read more