Consulting

Results 1 to 10 of 10

Thread: How to refer to an existing Worksheet Name

  1. #1

    How to refer to an existing Worksheet Name

    Dear All,

    I wish to compare different stock instrument (Instrument1, Instrument2, Instrument3 and so on). Each Instrument has several types of data (Close, MA1, MA2, MA3 and so on).

    I created specific names for the Instruments with formulas as:

    Instrument1Close:
    =OFFSET(Instrumet1Data!$M$103; Instrumet1Data!$I$6;0; Instrumet1Data!$I$5;1)

    Instrument1MA1:
    =OFFSET(Instrumet1Data!$N$103; Instrumet1Data!$I$6;0; Instrumet1Data!$I$5;1)

    Instrument2Close:
    =OFFSET(Instrumet1Data!$O$103; Instrumet1Data!$I$6;0; Instrumet1Data!$I$5;1)

    Instrument2MA1:
    =OFFSET(Instrumet1Data!$P$103; Instrumet1Data!$I$6;0; Instrumet1Data!$I$5;1)

    And so on.

    I want to see them on a chart. The code for the chart is the following:
    The following DO NOT WORK:
    .XValues = ThisWorkbook.Names("Date").RefersToRange          
    .Values = ThisWorkbook.Names(InstrumentName & "Close").RefersToRange                                                          
    .XValues = ThisWorkbook.Names("Date").RefersToRange          
    .Values = ThisWorkbook.Names(InstrumentName & "MA1").RefersToRange
    The following work:
    .XValues = "='" & ThisWorkbook.Name & "'!Date"
    .Values = "='" & ThisWorkbook.Name & "'!Instrument1Close"
    ALSO, The following DO NOT WORK:
    Y1AxisMax = Application.WorksheetFunction.Max(ThisWorkbook.Names(InstrumentName & "Close").RefersToRange)
    Y1AxisMin = Application.WorksheetFunction.Min(ThisWorkbook.Names(InstrumentNamee & "Close").RefersToRange)
    'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn
    ' CREATE A CHART AND DELETE SERIE
    'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn
    i = 1
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Compare'!$A$103:$B$150")
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    ActiveSheet.ChartObjects(i).Name = "Chart" & (i)
     
    ActiveSheet.ChartObjects("Chart" & i).Activate
    With ActiveChart
        .SeriesCollection(1).Delete
    End With
    j = 1
    'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn
    'ADD SERIES TO THE CHART
    'nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn
    InstrumentName= Cells(InstrumentNameRow, InstrumentNameColumn).Value
    For i = 1 To InstrumentNumber
        'ActiveSheet.ChartObjects("Chart" & i).Activate
            With ActiveChart
                'j = 1
                .SeriesCollection.NewSeries
                With .SeriesCollection(j)
                    .Name = InstrumentName & "Close"
                    ‘.XValues = "='" & ThisWorkbook.Name & "'!Date"    ‘This WORK
                    ‘.Values = "='" & ThisWorkbook.Name & "'!Instrument1Close"        ‘This WORK
                  
                    .XValues = ThisWorkbook.Names("Date").RefersToRange    ‘Don’t WORK  
                    .Values = ThisWorkbook.Names(InstrumentName & "Close").RefersToRange  ‘Don’t WORK
                End With
                
                j = j + 1
                .SeriesCollection.NewSeries
                With .SeriesCollection(j)
                    .Name = InstrumentumNeve & "CloseSzlkban"
                    .XValues = ThisWorkbook.Names("Date").RefersToRange
                    .Values = ThisWorkbook.Names(InstrumentName & "MA1").RefersToRange
                End With
                'Stop
            End With
            j = j + 1
            k = k + 1
            InstrumentNameRow = InstrumentNameRow + 1
            InstrumentName= Cells(InstrumentNameRow, InstrumentNameColumn).Value
     
    Next i

    Thank you very much in advance for your help!

    Peter
    Last edited by Paul_Hossler; 03-07-2021 at 08:03 AM.

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,293
    Attach your workbook with realistic data in.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,669
    Location
    Peter -- we like to use CODE tags to format and set off macro code

    I added them for you, but next time you can use the [#] icon to add CODE and \CODE tags and them just paste your macro between
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Dear p45cal and Paul,

    Thank you for your prompt answer.

    My workbook is in Hungarian, so I had to rewrite it for an English version, and find how to attach it.

    Unfortunately, I did not find how to attach it. I go to “Go Advanced”, choose and add my file, then I “upload” it, but appears nothing in the “Home” window, so I cannot drag it to the next window.

    Can you help me?

    I did not make any other post in any other forum. This is my first forum with my first post.

    Many thanks again for your help!

    Peter

  5. #5
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,293
    It might be too big?
    Save as xlsb and try again.
    Failing that upload to a file-sharing site (there are loads) and give the link to it (and permission of course).
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,669
    Location
    It's not always intuitive

    These are inserted 'Inline' but for workbooks, etc. I usually just click [Done] when I have the file(s) uploaded

    Capture.JPG

    Capture2.JPG

    Capture3.JPG
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    Dear p45cal,

    You were right, the file was too big.

    Thanks again.

    Peter
    Attached Files Attached Files

  8. #8
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,293
    Instead of:
    .XValues = "='" & ThisWorkbook.Name & "'!MyDate"
    .Values = "='" & ThisWorkbook.Name & "'!DJIAClose"
    you can try:
    .XValues = Range("MyDate")
    .Values = Range("DJIAClose")
    and there'd be nothing wrong with the likes of:
    .Values = Range(InstrumentName & "Close")
    as long as that named range exists and IntrumentName contained a valid string.

    (You have no valid SPCLose name in your attachement because it refers to a sheet SPData which I don't have.)

    Can I have a broader view of what you're trying to do?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    Dear p45cal,

    It works, it works, thank you so much!

    I red somewhere that by comparing different indexes, one can estimate the actual state of the market.
    Let's say if 2-3-4 indexes start to decline or grow it is probable that the others will follow, so one can take positions eventually in the indexes in late.
    It should also work for stocks also.

    I wanted to see it on a chart.

    I attach the modified file.

    Thanks again for your precious help!

    Best regards,

    Peter
    Attached Files Attached Files

  10. #10
    Dear Paul,

    Thanks to you also for your help!

    Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •