Hi Haydar --
Tried sending you email but it bounced (address:
').
I have a sample spreadsheet to send you if you let me know where to send it.
Here's the text of the message I tried to send:
Hi Haydar
In order to generate a 3d bubble chart, you need to provide 3 data elements
per bubble: the X and Y coordinates of the center of the bubble, and the
diameter.
If you want, you can add a 4th element (color), by specifying more than one
series of cells as you build the chart. In this case, Color tells me to which
Family a given bubble belongs.
Im attaching a spreadsheet with data and a chart to illustrate. Im also
appending a code segment to this message. To use the code, you need to drop
it into a
VB macro or sub(procedure).
Theres several hundred lines of code in the Excel app preceding the code
segment below, so some of it may not make sense. Ill try to give you an
overview:
Basically, Ive got a DB of about 50 columns by up to 200 rows. Each row
represents an investment opportunity. The Columns contain info related to
each opportunity risk, reward, cost, family, projected date of
availability
The bubble chart portion of the app includes a form which allows the user to
specify several search criteria (delivery date, industry, ), and what values
for each of the opportunities that fit the criteria should be used for X, Y
and Diam. When thats done, he/she clicks on a button and the app pulls the
relevant info out of the DB and plops it into a new worksheet. As it does so,
it drops the data into the sheet in family order, remembering where the
start & end rows are for each family.
In the code snippet below, the filtered & selected data has already been
plopped into a (recently cleared) sheet (BubbleData) in the workbook. Ive
done a global replace on one of my variable names to turn it into
DataSeries (think Family). Since I dont know how many Families there
may be in the DB (as time goes by new families of investments may get added),
I have to wander through the db and populate a list of family names and count
how many there are so theres a DataSeriesCount (number of
DataSeries/Families found in the DB), a DataSeriesIndex, etc. I also populate
some strings for titles and family names, and for every DataSeries theres a
string containing the Start of its range and the end of its range (in the
sheet BubbleData). I tried passing those values as ranges and couldnt
make it work, but passing them inside a string worked.
Disclaimer: Im new to
VB (this is my first
VB app) so there may be
easier/more elegant ways to do what Ive done here but I got this to work,
so I stopped improving it... Except I would like to be able to see the
specific name of the opportunity when I mouse over the resulting bubble,
instead of the X, Y and Diam values Havent figured out how to do that yet.
In the code snippet below, I have previously determined the average x and y
values ( I want the axes to bisect the chart show me 4 quadrants). And I
have title and Axis strings that let the user see what data fields were used
for X, Y and Diam, as well as other search criteria (filters).
Let me know if this helps. Or not J
Mark Hinrichs
Heres the
VB code:
----------------------------------------------------- cut here
-------------------------------------------------------------------------------
' Build Chart --- Data comes from sheet BubbleData, and Chart gets dumped
into sheet BubbleChart
Charts.Add
ActiveChart.ChartType = xlBubble3DEffect
ActiveChart.SetSourceData Source:=Sheets("BubbleData").Range( _
DataSeriesChartRangeStr(1)), PlotBy:=xlColumns
For DataSeriesIndex = 2 To DataSeriesCount
ActiveChart.SeriesCollection.NewSeries ' Add all new series
before defining ranges & titles
Next DataSeriesIndex
DataSeriesNameStr = DataSeriesName(1)
ActiveChart.SeriesCollection(1).Name = DataSeriesNameStr
For DataSeriesIndex = 2 To DataSeriesCount
DataSeriesNameStr = DataSeriesName(DataSeriesIndex)
ChartRangeStartRowStr = DataSeriesStartRow(DataSeriesIndex) ' cast
integer vals into strings
ChartRangeEndRowStr = DataSeriesEndRow(DataSeriesIndex)
XValsStr = "=BubbleData!R" + ChartRangeStartRowStr + "C2:R" +
ChartRangeEndRowStr + "C2"
YValsStr = "=BubbleData!R" + ChartRangeStartRowStr + "C3:R" +
ChartRangeEndRowStr + "C3"
BubbleSizeStr = "=BubbleData!R" + ChartRangeStartRowStr + "C4:R" +
ChartRangeEndRowStr + "C4"
ActiveChart.SeriesCollection(DataSeriesIndex).XVal ues = XValsStr
ActiveChart.SeriesCollection(DataSeriesIndex).Name = DataSeriesNameStr
ActiveChart.SeriesCollection(DataSeriesIndex).Bubb leSizes =
BubbleSizeStr
ActiveChart.SeriesCollection(DataSeriesIndex).Valu es = YValsStr
Next DataSeriesIndex
ActiveChart.Location Whe=xlLocationAsObject, Name:="BubbleChart"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = TitleStr
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = XaxisStr
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = YaxisStr
End With
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlCustom
.CrossesAt = YAxisAvg
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlCustom
.CrossesAt = XAxisAvg
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Set ThisChart =
ActiveSheet.ChartObjects(ActiveSheet.ChartObjects. Count) ' Scale the chart
to consume the region A1 to P37
With Range("A1:P37")
ThisChart.Left = .Left
ThisChart.Top = .Top
ThisChart.Width = .Width
ThisChart.Height = .Height
End With
"Haydar" wrote:
dear mark
do i understand you correctly that you have a tool that draws bubbles
in a sheet?
my problem is that i can´t use the bubble chart because it needs a
certain formatted sheet with x y and size.
i am desparetly searching for a solution and it would be great if you
could send me the macro or tool or just give be a hint!
thanks a lot
best regards
haydar
--
Haydar
------------------------------------------------------------------------
Haydar's Profile: http://www.excelforum.com/member.php...o&userid=26041
View this thread: http://www.excelforum.com/showthread...hreadid=380732