Posts by Andy Pope

  • custom Tab not appering when distributed as addin

    Re: custom Tab not appering when distributed as addin


    The reason the tab sometimes appears is due to the file containing both CustomUI and CustomUI14 sections.


    If you open the file in xl2007 the tab will appear.
    If you open the file in xl2010 or newer it will not. This is because excel detects both CUI and CUI14 sections and uses the CUI14 one, which has no xml code.


    You can use the editor to delete that section and then it should function on all versions from xl2007 to latest.

  • Produce Hybrid Of Scatter Chart & Bubble Chart

    Re: Produce Hybrid Of Scatter Chart & Bubble Chart


    Thank you.


    The value in cell M8 is the maximum size of the bubble, same as in F1 when doing it by formula for Area sizes.


    For those size charts 41 is the largest a bubble gets.


    Why calculating the by Area method only the maximum size is required. Minimum is always 2

  • Produce Hybrid Of Scatter Chart & Bubble Chart

    Re: Produce Hybrid Of Scatter Chart & Bubble Chart


    Actually the bubble chart is set to be by Area and my code does by width so that's why there was some differences. But there are always going to be differences as the appraoch is limited by the size you can set a data marker to.


    I have updated the existing routine and added a new one. So now you have code for Area and Width sizing.


    The whole process is restricted by the limit of marker sizes. There is no size smaller than 2 so particularly with the Area type the marker are just not small enough.


    The other limit is that marker sizes happen in integer increments and do not have the unlimited adjustment to radius as bubbles do.


    As to how to use this I think I confused you by providing too much information and not enough explanation.


    I have provide code to do the resizing. The routines do require some minimal information such as,
    • Range containing sizes
    • largest marker size
    • for ByWidth version smallest marker size


    All the other formula and values on the worksheet are only there to illustrate what the code is doing.



    Also attached xl2003 version, although AAE you might want to go and download the file format convertors for 2007.
    http://www.microsoft.com/downl…BB74CD1466&displaylang=en

  • Auto-Extending Graph/Chart Range

    Re: Auto-Extending Graph/Chart Range


    Create another series based on a range that has a formula.
    The formula should output #N/A for all but the cell that contains the last point.


    Apply data labels to the series. Only the valid points, which is 1, will display the label.

  • Add Range Rows Into Listbox

    Re: Insert Row Data Into Listbox


    If you use Additem to populate a listbox you are restricted to 10 columns.


    If you load the information into an array you can, but you will need to store an exact copy of the data to be displayed in the array. That is all the columns and all the rows of information.

  • Chart Code With SetElement Fails In Pre 2007

    Re: Code Incompatible Office 2000


    The compatibility of the file refers only to the structure and contents of sheets and objects rather than the code produced.


    You have a line of code that is completely new in xl2007, which is the SetElement.
    Also because you have used Select statements what worked in 2007 actually now deletes the new created chart.


    try this revision to the last section of your code


    [vba]
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range(BuscarFecha.Offset(0, 5), BuscarFecha.Offset(IterarRetornoAcumulado - 1, 6))
    ActiveChart.ChartType = xlLine
    ' not know before 2007
    'ActiveChart.SetElement (msoElementChartTitleAboveChart)
    ActiveChart.ChartTitle.Text = "Retorno Acumulado"
    ActiveChart.Legend.Select
    Selection.Delete
    ' this actually delete newly created chart.
    'ActiveSheet.ChartObjects("1").Activate
    'Selection.Cut
    Sheets("Rentabilidades").Select
    Range("B27").Select
    ActiveSheet.Paste

    [/vba]

  • Bullet Point Each Line Within A Single Cell

    Re: Bulleting Within A Single Cell


    Try this, select a cell with multiple lines and run the macro


    [vba]
    Sub MakeBullets()

    Dim vntLines As Variant
    Dim lngIndex As Long
    Dim strTemp As String

    vntLines = Split(ActiveCell.Value, vbLf)
    For lngIndex = LBound(vntLines) To UBound(vntLines)
    If Len(Trim(vntLines(lngIndex))) > 0 Then
    strTemp = strTemp & ChrW(9643) & " " & vntLines(lngIndex) & vbLf
    Else
    strTemp = strTemp & vbLf
    End If
    Next
    ActiveCell.Value = Left(strTemp, Len(strTemp) - 1)

    End Sub
    [/vba]

  • Interesting Integer Overflow

    Re: Interesting Integer Overflow


    Alternatives would be


    [vba]
    Select Case CLng(2)
    [/vba]


    [vba]
    Select Case 2&
    [/vba]


    or store the value in an appropriately typed variable.


    Interesting potentional error though.

  • Select Each Element In Chart

    Re: Secondary Horizontal Axis Values Chart


    If you select the chart you can use the cursor keys UP/DOWN to cycle through the chart elements.


    Or you could use the Dropdown selected in the Chart Tools > Layout > Current Selection group.