Posts by tomsoyer

Hi,

Does anyone know a more efficient way of filtering a date field of a recordset by year instead of specific dates? For example, instead of a combersome method like this:

rsRecordSet.Filter = "StartDate >= #1/1/1995# AND StartDate <= #12/31/1995#"

is there a better way of filtering records that have the StartDate in 1995?

Thanks,

Tom

Re: Create Objects Dynamically

I have a custom class x, and I want to creat objects of x as objx1, objx2, objx3, etc. But I don't know exactly how many objects of class x I need before hand. Is VBA capable of generating these objects at run time?

Hi,

Is there a way to create objects dynamically? for example, I know that I need to many create objects of a class, but I don't know how many objects I need before hand. Does VB allow you to create objects on the fly? If so, how can it be done? Thanks.

Tom

Re: Vba Performance - Applications Interfering With Each Other

I found the cause of slow performance. It was a UDF. Once it was deleted, performance was back to normal.

Hi,

I developed two separate Excel applications using VBA, each has about 10 class modules. A strange thing happened after I combined both applications under one file (i.e., all class modules and worksheets are in one file): the speed of one application, app A, would run as much as 5 times slower than before when the other application was not running (the two applications are always run separately and never simultaneously). If I delete just the worksheets associated with the other application, app B, then app A's performance would get back to normal. If I delete only the class modules associated with app B, then app A's performance would improve, but not by much. I don't understand why one application would affect the other's performance when the other application was not even running. Has anyone had this problem before? How can I diagnose the root cause of this performance issue?

Thanks,

Tom

Hi,

Does anyone know if there is a way to check whether a stock chart has both up bars and down bars?

Here is the problem I have: I have serveral charts that use the xlStockOHLC chart type to display stock data. The Excel default color for down bars is black, and white for up bars. I don't like the default colors, so I tried to change the bars' color via VBA:

with oChart.ChartGroups(1)
.UpBars.Interior.ColorIndex=10
.DownBars.Interior.ColorIndex=3
end with

This code works only when a chart has both up and down bars. In cases where a chart is filled with all down bars (and zero up bars), or vice versa, the code causes an error (cannot set interior colorindex). Apparently, if one tries to access or set any property or access any method of the UpBars object when there are no up bars, Excel throws a runtime error. It seems that Excel still creates the UpBars object even if there are no up bars, but any access to properties and methods of the UpBars object causes an error. This means I need to check if a chart has up bars before setting the interior property of the UpBars object. But I couldn't figure out how to do this without going back and check the original data. Does anyone know if there is a quick way of checking whether a chart has both up bars and down bars?

Thanks,

Tom

Re: Xlcolorindexautomatic With Validation

Thanks guys! Changing the palette colors worked very well. No more yellow lines on white background!

Hi,

I am building several line charts dynamically via VBA, and each chart has multiple lines. Since I don't know how many lines a chart would have, I use the xlColorIndexAutomatic value for xlColorIndex property of the lines. The problem I ran into is that Excel would automatically generate yellow lines against a white background, making these lines difficult to see. Is there a way to modify, or validate the color index values generated by xlColorIndexAutomatic so that lighter colors, such as yellow, would not be used against a white background? I tried to read and validate the colorindex property of each series object, but it seems that they are aways equal to -4105 once xlColorIndexAutomatic was assigned to a series, e.g., all colored lines would have -4105 as their color index value.

Thanks,

Tom

Re: Constructing A Shaperange Collection For Dynamically Generated Charts

Thanks Andy! Your code works.

I have a follow up question. I noticed that the following code, although similar to yours, does not work:

Dim vntShapeName as Variant
Dim arrShapeName(1) as String 
Dim myDocument As Worksheet 
Dim myRange As ShapeRange 
 
Set myDocument = Worksheets(1) 
arrShapeName(0) = "Big Star" 
arrShapeName(1) = "Little Star" 
vntShapeName=arrShapeName
Set myRange = myDocument.Shapes.Range(vntShapeName)

According to Microsoft documentation, a variant of array and an array of variant are accessed the same way. So I am wondering why in this case one works and the other does not? Any idea?

Thanks,

Tom

Hi,

I have a question about how to construct ShapeRange collection objects for dynamic shapes. I have a set of dynamically generated charts that I would like to group some of them together via VBA. This means that I need to construct a ShapeRange collection object dynamically. Unfortunately, the example provided by Microsoft only deals with static shapes, i.e., the number of shapes to be grouped and their names are known ahead of time. Below is the example from Microsoft:

----------------------------Microsoft Example--------------------------
The following example constructs a shape range that contains the shapes named "Big Star" and "Little Star" on myDocument and applies a gradient fill to them.

Set myDocument = Worksheets(1)
Set myRange = myDocument.Shapes.Range(Array("Big Star", "Little Star"))
myRange.Fill.PresetGradient _
    msoGradientHorizontal, 1, msoGradientBrass

------------------------------End Example-----------------------------

The example above works for static shapes with predefined names. For dynamic shapes where their names are generated dynamically, one would think that replacing "Big Star", "Little Star" with a variable would do the trick, but it does not. For example the following code would generate an error message (shape not found):

Dim strShapeNames as string
Set myDocument = Worksheets(1)
strShapeNames="""Big Star"", ""Little Star"""
Set myRange = myDocument.Shapes.Range(Array(strShapeNames))

Does this mean it is not possible to group dynamically generated charts? Has anyone tried to construct ShapeRange objects for dynamically generated charts?

Thanks,

Tom