Posts by Lucio5295

    Re: VBA code for pixel coordinates of a grid area

    Hi Pike,

    Thanks for your reply, but I was wondering something else.
    I need (if it exists) a chart property or something similar, that gets an object that represents the grid area contour of the chart (a rectangle with members like .Top, .Left, .Width, .Heigth).
    I tried Plotarea, but unfortunately it gives me a rectangle with .Top and. Left not coincident with Grid Area (please see my previous image.jpg - red line)

    Best Regards

    I would like to know the coordinates (in pixels) of the vertices of the grid area of a chart on the screen.
    I thought that the plot area coincided with the grid area and therefore I tried to draw a plotarea rectangle, but the two figures were not overlapped, but staggered.

    VBA Code:

    Set ac = ActiveSheet.ChartObjects(ActiveChartName$)Set pa = ActiveSheet.ChartObjects(ActiveChartName$).Chart.PlotAreaActiveSheet.Shapes.AddShape(msoShapeRectangle, ac.Left + pa.InsideLeft, ac.Top + pa.InsideTop, pa.InsideWidth, pa.InsideHeight).Select

    Result (red line):


    I would begrateful if you could tell me the correct code
    Thanks foryour help

    Re: Transform a VBA module in a compiled Add-In for Excel

    Dear royUK,
    There is too much emotion in your remarks. Calm down, itdoesn't seem the case.
    From you I expect open contributions (by the way, on the siteyou pointed out I did not find useful things to solve my problems).
    Thanks and regards.


    Re: Transform a VBA module in a compiled Add-In for Excel

    If can be useful, I had this aswer in The Code Cage forum:
    "You can only create dll files with VBA if you have a Developer edition of Office XP (or 2000, I think).
    Why do you want to do this? If it's for performance, unless you can use C++ I don't think you will benefit. "


    Good morning.
    For a technical application I'm using Excel 2013 as a user interface, to show inputs and results with tables and charts.
    In this VBA project, calculations are made in a large module that contains only subroutines for number-crunching.
    In these subroutines the inputs and outputs are Public variables and there is no reference to Excel objects.
    I would like to turn this VBA module in a compiled Add-In, running under the Main program written again in VBA.

    Question: what is the easiest way to create this compiled add-in and how to do it?

    I don't know C++, but I read somewhere that you can create compiled add-ins for Excel also using VBA.
    I would be grateful if you could advise me some papers which explain the best procedure in VB or VBA, in a simple and comprehensive way,with examples step by step.

    Any suggestion will be welcome.
    Thanks and regards.

    Re: Error 1004 in range definition

    Yes, it works very well.
    At this point, however, I have two programs that use the same subroutine and the same worksheets.
    It would be nice to understand why the first program works without . Address and here I must insert . Address
    Greetings and thanks

    Good morning,
    I'm using Excel 2010. In a subroutine that has always worked well in other programs, it now appears an error message 1004 in the definition of a range

    With Worksheets(Ws$)
    Debug.Print .Cells(5, 3).Value                          'OK
    Debug.Print .Range("C5").Value                   'OK
    Debug.Print .Range(.Cells(5, 3)).Value 'Error 1004
    End With

    I do not understand. Any idea?
    Thanks for your helping

    Re: Many XY curves with one SeriesCollection

    Hi Herbds7,
    So many questions and doubts, I will try to answer in order.

    I'm not using Excel as a spreadsheet to make tables and calculations, but only as a userinterface for input / output data. Due to his complexity, the processing is outside.

    - The data input is processed by an underlying VBA program, that transmits to the worksheet only the outputs to display in tables and charts.
    - This program has also a graphic module, a wireframe modeller for surfaces.
    - This modeller generates sequences of points (XY coordinates) to draw 2D curves.
    - These curves are grouped into "families" or homogeneous groups of curves.
    - Each family includes 10-100 curves and goes on the chart with his own format (line color,thickness..etc) to distinguish it from the others.

    My worksheet is just one example to explain a simple way, what I'm trying to do.

    Those numbers you see in the Range (. Cells (5,2). Cells (42,3)) come from three two-dimensional arrays stored in the memory of the PC.

    They represent the grid of three B-splines, but you can considerer them as one of the many families of curves generated by the system.
    As regards Excel they are only three curves XY, to draw with the same format.

    As I mentioned earlier, I want to feed these data to a uniqueSeriesCollection (not three) via software, without damping data in a range on the worksheet.

    The result I want to achieve are three distinct 2D curves.

    We have seen how this task can be accomplished by placing these data in a vertical range with empty cells between a set of data and the other.
    I am convinced that there must be also a way to realize the same thing via software, giving to the SeriesCollection these arrays, or something else that emulates that range.
    The part [.. Particular sequence...] is the one that I don’t know how to write, for this target.

    ch.SeriesCollection (SC $). XValues = [.. Particular sequenceof X coordinates of all the curves ..]
    ch.SeriesCollection (SC $). = Values [.. Particular sequenceof Y coordinates of all the curves ..]

    I hope it's clearer now.

    I looked at what you sent me, but it is really another thing. I do not believe may serve me.
    However, thanks anyway.


    Re: Many XY curves with one SeriesCollection

    Hi Herbds7,thank you for your answer
    You did a nice job with a classic solution, o
    ne SeriesCollection for each curve, in our example:

    Three curves XY = three arrays assigned to three different SeriesCollection()

    My goal is instead rather different: One SeriesCollection for three curves.
    With reference to my previous worksheet, this can be accomplished by assigning a range and
    writing something like this:
    ch.SeriesCollection (SC $). XValues=. Range (.Cells (5, 2),.Cells (42, 2))
    ch.SeriesCollection (SC $). Values =. Range (. Cells (5, 3),. Cells (42, 3))

    Unfortunately, in my applications I can not work with ranges, because I have to draw hundreds of curves and I do not want to fill the worksheet with numbers that serve only to graphic outputs.
    For this reason I would like to know if exists a VBA syntax, which enable the assigning of the XYcoordinates for all the curves, grouped into a single data file. Somethinglike this:

    ch.SeriesCollection (SC $). XValues = [.. Particular sequence of X coordinates of all the curves ..]
    ch.SeriesCollection (SC $). Values = [.. Particular sequence of Y coordinates ofall the curves..]


    Good morning,
    I’m working with Excel 2010 to plot, on an existing chart, n distinct XY curves , in one shot (1=<n<=100,each curve about 10-100 points) .
    To do this, I’m using a single SeriesCollection (because easy to manage,with a unique format for all the n curves, and above all, erasable all at once when no longer needed),whose source is a vertical range on two columns, as the red lines in the attached spreadsheet.
    My question is: it is possible to feed the same SeriesCollection via software, without dumping the data on a range?

    I have tried various kind of arrays, and/or recursive calls of subroutines, but without success.
    I obtain even a SeriesCollection that gives a single curve instead of a set of distinct curves(see VBA macro in the spreadsheet).
    Thanks for any