-
I need a quick way to create a filter by month on an autofiltered range. Right now I'm manually entering this: That's tedious. One option is to create another column with just the month and year in it and filter on that, but I don't like my data cluttered. So I wrote this [...] Read More...
-
Here's some code I have to generate a SQL statement Public Function BuildRepSQL(ByRef clsRepLine As CRepLine, eType As aiDBStatus) As String
Dim sReturn As String
With clsRepLine
Select Case eType
...
-
When I test formulas on a spreadsheet, it s a very manual process. I'm trying to figure out a way to automate this. Here's what I have so far.
I want to identify some input cells and output cells. Then record some known good values for the inputs and outputs and store them. [...] Read More.....
-
I ran into a little problem with Excel 2007 this week. When I have an Excel 2003 add-in loaded in 2007, it exhibits some strange behavior when copying sheets. Namely, in certain circumstances the CodeName property of the sheet gets changed. I'm not sure what all those circumstances are, but I do...
-
I'm porting an application to Excel 2007 and thought I'd share some problems I've run into. First, there's FileSearch. I use FileSearch to read CustomDocumentProperties from closed Excel files. That way I can determine if the file is a part of my app and relevant to the context before...
-
Dick has been kind enough to allow me to mirror posts from DDoE, but gets little back. Therefore as a guest poster I thought I would post some ramblings I made today on excel user group regarding my own particular coding techniques. (It also allows him the weekend off)
I have recently been doing most...
-
I have recently been doing most of my coding in Visual Studio, doing some 'real' work for a change, coding using VB.net, ASP.net and ADO.net. The environment in VS2008 is much improved over the VBE in MS Office (VBA). For example, it auto-indents and, with the addition of Developer Express's...
-
Whenever I need to determine if a cell is blank, I call the IsEmpty function on the Value property. If IsEmpty(ActiveCell.Value) Then This anonymous blogger uses vbNullString. If ActiveCell.Value = vbNullString In other news: In Firefox, Cntl+Click on a hyperlink and it will open that link in...
-
Elton is trying to create a QueryTable based on a named range in a closed Excel file. When the code tries to refresh the QueryTable object, he gets an SQL Syntax Error message. Although he's sure his SQL syntax is correct, it's almost certainly not. The syntax you get from the macro [...] Read...
-
Hi all,
So after Dicks (nice to read) rambling on golf, let’s do some Excel/VBA stuff again.
I intend to get a bit of discussion on this one, so bear with me.
I’m currently developing an Excel workbook for a customer. One of the interesting things with this project is that it consists of...
-
Hi All,
Excel has a host of built-in functions to help you do your computations. But sometimes you need to write your own functions using VBA. These functions are called “User Defined Functions” (UDF). UDF’s typically are placed in addins. As long as the addin is installed, the UDF’s...
-
There’s an interesting discussion at Smurf about the macro recorder. I rarely use it, but I used to use it quite often. I actually just used it last week for something to do with FormatConditions, but I don’t remember the details. Intellisense or the Object Browser usually gets me where I...
-
Hi all,
For a project I needed a quick way to display the content of an array to the user. I didn’t want to use a worksheet, but opted for a userform. The data I wanted to show was contained in an array. The array contained something like: Description
Before
After Cell Errors
100
10 Corrupt Names...
-
I previously mentioned that VBA is dying. At least one of my arguments was that MS couldn’t be bothered to port it to the new MAC architecture. Now c-net says:
Finally, the company announced that, for the next full release of Office for Mac, it is bringing back support for Visual Basic scripts...
-
The purpose of this article is to introduce various capabilities of a class property. It is not meant to serve as an introduction to classes and objects. See the references section for introductory pages.
The typical use of a Class Property is to assign a value or to query its value as in the [...] Read...