Solved

Error 1004, several smaller questions :)

Posted on 2000-03-15
34
214 Views
Last Modified: 2008-03-17
Below is my code. My problem is when LastRow is greater than 300 I get
unable to set xvalues property error 1004. Any help ?

Also I can't seem to get the xvalues to display on the chart, I get the correct number of tick marks but no numbers to go with them. Any ideas ?

One more small problem, at the bottom my code should remove excel from the memory, but if I check in the task manager excel is still running, how do I remove it from memory ?

I would award more points but running low :)

Option Explicit

Dim varNum As Long
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Dim LastRow As Long
Dim B3 As Long

Public Sub Data()

On Error Resume Next

'Start the excel COM and make it visible.
Set objExcel = GetObject("", "excel.application")
'Set objExcel = excel.Application ' Seems to cause a memory leak
objExcel.Visible = False
   
'Start a workbook.

Set objWorkbook = objExcel.Workbooks.Add

'Turn off the alerts, otherwise user will have to confirm actions.
objExcel.DisplayAlerts = False

'Ensure there is only one worksheet.
Do While objWorkbook.Worksheets.Count > 1
    Set objWorksheet = objWorkbook.Worksheets.Item(objWorkbook.Worksheets.Count)
    objWorksheet.Delete
Loop

'Set objWorksheet to the remaining worksheet.
Set objWorksheet = ActiveSheet

'Rename the sheet to Results.
objWorksheet.Name = "Results"


'Enter data
With objWorksheet.QueryTables.Add(Connection:="text;c:\WINDOWS\desktop\TRIAL.txt", Destination:=Range("A3"))
    .Name = "Results"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = xlWindows
    .TextFileStartRow = 2
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1)
    .Refresh BackgroundQuery:=False
End With
   

Range("B3").Select
Selection.End(xlDown).Select 'find Last Row
LastRow = Selection.Row

'Draw Chart with the data
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Results").Range("B3:B" & LastRow), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Results!R3C" & LastRow & ":C1"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Results"
With ActiveChart
    .HasTitle = True
    .ChartTitle.Characters.Text = "Results" ' Title
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Number of Cycles" ' X-Axis
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Root Mean Square Distance "   ' Y-Axis
End With
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveSheet.Shapes("Chart 1")
    .Left = 50
    .Top = 180
End With

'Headers
objWorksheet.Cells(1, 1) = "Number of Cycles"
objWorksheet.Cells(1, 1).Font.Bold = True
objWorksheet.Cells(1, 2) = "Root Mean Square Distance"
objWorksheet.Cells(1, 2).Font.Bold = True

Range("A:A", "B:B").Select
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .MergeCells = False
End With

Range("A:A", "B:B").Select
Selection.Columns.AutoFit
objExcel.Visible = True

'Turn back on alerts
objExcel.DisplayAlerts = True

'Free up memory
Set objExcel = Nothing
Set objWorksheet = Nothing
Set objWorkbook = Nothing
End Sub
0
Comment
Question by:Praetor
  • 18
  • 15
34 Comments
 
LVL 9

Expert Comment

by:samopal
ID: 2621174
It seems to me you understand my rejected answer http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=10310155  without any comments :-)))
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2621223
In the line:

ActiveChart.SeriesCollection(1).XValues = "=Results!R3C" & LastRow & ":C1"

You are building a formula with the value of Lastrow.  That's fine, but you can only have 256 columns in a worksheet, and in this case (with Lastrow=300) the formula would say:

=Results!R3C300:C1

Which is invalid.  I suspect it won't if Lastrow is > 256.

Wes
0
 

Author Comment

by:Praetor
ID: 2621227
Samopal. To be fair if u look at your answer it's a long way from what I needed. I admit I should have commented...sorry :)

Anyway can u help me out with this ?

Sorry for not commenting on your help :)
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2621239
Hmm.  I thought those were R1C1 coordinates.  I guess that's a swing and a miss...

Wes
0
 

Author Comment

by:Praetor
ID: 2621255
WesleyStewart I think I understand what I have done wrong, you haven't answered my question but u have certainly focussed my attention on the offending code.

I meant to have

ActiveChart.SeriesCollection(1).XValues = "=Results!R3C2:" & LastRow & "C1"


0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2621265
Well, something led me to that line of code.....:^)
0
 

Author Comment

by:Praetor
ID: 2621271
Still need help on points 1 & 2
0
 

Author Comment

by:Praetor
ID: 2621293
I meant I still need help on points 2 & 3
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2621311
If the code fails before it gets to the end, you won't execute the lines where you set Excel to nothing.

Use the New keyword rather than Getobject:

Set objExcel = New Excel.Application

Wes
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2621327
I think you need something along the lines of:

ActiveChart.ApplyDataLabels

to get the labels to show.

Wes
0
 

Author Comment

by:Praetor
ID: 2621724
WesleyStewart, sorry you misunderstood me, I don't want data labels on the chart, I want the xValues to be on the xaxis.

say my xvalues were 1,2,3,4,5 then on the axis I want the numbers 1,2,3,4,5.
0
 

Author Comment

by:Praetor
ID: 2623564
Adjusted points from 50 to 60
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2623627
Yes, but we are hot on it's trail....

ActiveCHart.ApplyDataLabels shows the data labels for the chart.  What you need is to reference the data series for which you want the labels to show.  Try this:

ActiveChart.SeriesCollection(1).ApplyDataLabels

or

ActiveChart.SeriesCollection(2).Apply
DataLabels

The trick will be returning a reference to the X-Axis series, which might be SeriesCollection(1) or might not be.  From Excel Help:

The series index number indicates the order in which the series were added to the chart. SeriesCollection(1) is the first series added to the chart, and SeriesCollection(SeriesCollection.Count) is the last one added.

I think we're almost there.


Wes
0
 

Author Comment

by:Praetor
ID: 2623907
Adjusted points from 60 to 70
0
 

Author Comment

by:Praetor
ID: 2623908
ActiveChart.SeriesCollection(1).XValues = "=Results!R3C" & LastColumn & ":C1"

Why doesn't this work ??
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2623968
I think your syntax is off.  Assuming Results is the active worksheet, I think the line should read:

ActiveChart.SeriesCollection(1).XValues = Worksheets(Results).Range("R3C" & LastColumn & ":C1")

or something to that effect.  Sorry I can't be more sure, but the syntax involving a variable is hard for me to figure out. I think your use of the "=" is off:

).XValues = "=Results!R3C"

because you don't need an "=" sign in the property.  Take a look at the help sample.  If you don't know how to find it:  Click on the object browser, type 'xvalues' in the lower combo box, and hit enter.  When you see 'xvalues' in the 'members' window in the right-hand side of the object browser, click on it and hit F1.

Wes

0
 

Author Comment

by:Praetor
ID: 2624373
What is weird though is if instead of having LastColumn I put in R1C1 co-ordinates it works fine. Problem is these would have to be specified at design time, and I need them to be determined at run time.

ActiveChart.SeriesCollection(1).XValues = "=Results!R3" & LastColumn "& :C1"

This is the offending line of code

Please someone help me !!
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 4

Expert Comment

by:wesleystewart
ID: 2624734
Where did LastColumn come from?  It is neither declared nor set to a value in your code above.  That right there could be the problem.

I have run some tests using strings to set the XValues property using the Range object.  It seems to work pretty well.  I'm having trouble getting R1C1 to work.  Can you paste a line that does work so I can see it?

Sorry this is taking so long.  This is quite a challenge.

Wes
0
 

Author Comment

by:Praetor
ID: 2625051
ok this is all the relevant module. I need this to work. WS thank for all your help. Hopefully get this sorted soon :)

Option Explicit

Dim varNum As Long
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim B3 As Long

Public Sub Data()

On Error Resume Next

'Start the excel COM and make it visible.
Set objExcel = GetObject("", "excel.application")
'Set objExcel = excel.Application ' Seems to cause a memory leak

objExcel.Visible = False
   
'Start a workbook.

Set objWorkbook = objExcel.Workbooks.Add

'Turn off the alerts, otherwise user will have to confirm actions.
objExcel.DisplayAlerts = False

'Ensure there is only one worksheet.
Do While objWorkbook.Worksheets.Count > 1
    Set objWorksheet = objWorkbook.Worksheets.Item(objWorkbook.Worksheets.Count)
    objWorksheet.Delete
Loop

'Set objWorksheet to the remaining worksheet.
Set objWorksheet = ActiveSheet

'Rename the sheet to Results.
objWorksheet.Name = "Results"

'Enter data
With objWorksheet.QueryTables.Add(Connection:="text;C:\WINDOWS\desktop\TRIAL.txt", Destination:=Range("A3"))
    .Name = "Results"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = xlWindows
    .TextFileStartRow = 2
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1)
    .Refresh BackgroundQuery:=False
End With
   
Range("B3").Select
Selection.End(xlDown).Select 'find Last Row
LastRow = Selection.Row
LastColumn = Selection.Column

'Draw Chart with the data
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Results").Range("B3:B" & LastRow), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Results!R3" & LastColumn & ":C1"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Results"
With ActiveChart
    .HasTitle = True
    .ChartTitle.Characters.Text = "Results" ' Title
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Number of Cycles" ' X-Axis
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Root Mean Square Distance "   ' Y-Axis
End With
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveSheet.Shapes("Chart 1")
    .Left = 50
    .Top = 180
End With

'Headers
objWorksheet.Cells(1, 1) = "Number of Cycles"
objWorksheet.Cells(1, 1).Font.Bold = True
objWorksheet.Cells(1, 2) = "Root Mean Square Distance"
objWorksheet.Cells(1, 2).Font.Bold = True


Range("A:A", "B:B").Select
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .MergeCells = False
End With

Range("A:A", "B:B").Select
Selection.Columns.AutoFit
objExcel.Visible = True

'Turn back on alerts
objExcel.DisplayAlerts = True

'Free up memory
Set objExcel = Nothing
Set objWorksheet = Nothing
Set objWorkbook = Nothing
End Sub

0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2625089
replace the troubled line with:

ActiveChart.SeriesCollection(1).XValues = ("Results!R3" & Lastcolumn & ":C1")

and give it a shot.  It works fine on my machine.  

One note:  In your code you are hardcoding LastRow and LastColumn in this section:

Range("B3").Select
Selection.End(xlDown).Select 'find Last Row
LastRow = Selection.Row
LastColumn = Selection.Column

Because your range is only one cell, LastRow will always be 4, and LastColumn will always be 2.

Wes
0
 

Author Comment

by:Praetor
ID: 2626021
please help me......below entire code for relevant section.








Option Explicit

Private Declare Function SendMessage Lib "User32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal Param As Long, lParam As Any) As Long
Dim N As Integer, M As Integer, x As Integer, Y As Integer, R As Integer, j As Integer
Dim i As Integer, z As Long, xproposed As Integer, yproposed As Integer, rand As Integer
Dim xdist As Integer, ydist As Integer, totdist As Integer, savecounter As Integer
Dim R1 As Integer, R2 As Integer, vectorR As Integer
Dim spacial(), xpoly(), ypoly()
Dim c As Long, counter As Long
Dim intM As String
Dim strNmsg As String
Dim strMmsg As String
Dim xrand As Single, yrand As Single

Private Sub cmdstart_Click()

On Error Resume Next

N = Val(txtchains.Text)
M = Val(txtarray.Text)
savecounter = Val(txtsavecount.Text)

If M >= 2 * N Then
    ReDim spacial(1 To M, 1 To M)
    Else
        MsgBox "m must be greater or equal to 2*n"
    Exit Sub
End If
   
ReDim xpoly(0 To N)
xpoly(1) = M / 2            'sets x co-ordinate of 1st bead

ReDim ypoly(0 To N)
ypoly(1) = M / 2            'sets y co-ordinate of 1st bead

For i = 2 To N Step 2
    xpoly(i) = xpoly(i - 1)            'move bead i, o step in x direction
    ypoly(i) = ypoly(i - 1) + 1       'move bead i, 1 step in y direction
    xpoly(i + 1) = xpoly(i) + 1      'move bead i+1, 1 step in x direction
    ypoly(i + 1) = ypoly(i)             'move bead i+1, 0 step in y direction
Next i
 
For i = 1 To N
    spacial(xpoly(i), ypoly(i)) = 1
Next i

c = Val(txtnumbercycles.Text)   'number of cycles for progamme to execute

counter = 0

For z = 1 To c          'loop for specified number of cycles
   
Call cal
   
Open "C:\WINDOWS\desktop\TRIAL.txt" For Append As #1   'writes z and vectorR to specified file
Write #1, z, vectorR
Close #1
   
txtcycle.Text = Format(z)
txtcycle.Refresh

Next z

Call Data 'need to add call data when routine finished

End Sub

Public Sub cal()
   
1   R = Int((N * Rnd) + 1)  'create random integer R between 1 and N inclusive
   If R = N Or R = 1 Then
        xrand = Rnd
        If xrand > 0.5 Then
            x = 1
        Else                'create random move in x direction
            x = -1
        End If
   
        yrand = Rnd         'create random move in y direction
        If yrand > 0.5 Then
            Y = 1
        Else
            Y = -1
        End If
   Else
        GoTo 2
   End If
 
       
   xproposed = xpoly(R) + x       'add random x move onto original x
   yproposed = ypoly(R) + Y       'add random y move onto original y

   If spacial(xproposed, yproposed) = 1 Then    'checks to see if proposed co-ordinates are occupied
       GoTo 4
   End If
           
   If R = N Then
       xdist = xproposed - xpoly(R - 1)
       ydist = yproposed - ypoly(R - 1)
       End If
   
   If R = 1 Then
       xdist = xproposed - xpoly(2)
       ydist = yproposed - ypoly(2)
   End If
   
   totdist = ((xdist) ^ 2) + ((ydist) ^ 2)
       
   If totdist <> 1 Then   'checks to see if nearest neighbour distance when moved will still equal 1
       GoTo 4
   Else
       spacial(xpoly(R), ypoly(R)) = 0      'clears original position in array
       xpoly(R) = xproposed
       ypoly(R) = yproposed
       spacial(xpoly(R), ypoly(R)) = 1  'update spacial array with new position
       Call edge
   End If
   
   GoTo 4
   
'Changed xrand and yrand to single, cause that's what Rnd function returns
'Rnd returns a random number between 0 and 1, so if it's an integer it would allways be
'either 0 or 1, most likely 1 cause an integer will always round up

2   xrand = Rnd                  'generate random number
      If xrand > 0.5 Then
          x = 1
      Else
          x = (-1)
      End If
       
   yrand = Rnd
   If yrand > 0.5 Then
      Y = 1
   Else
      Y = -1
   End If
         
   xproposed = xpoly(R) + x   'add random x move onto original x
   yproposed = ypoly(R) + Y   'add random y move onto original y

   If spacial(xproposed, yproposed) = 1 Then   'check to see if lattice position occupied
       GoTo 4
   End If
   
   xdist = xproposed - xpoly(R - 1)
   ydist = yproposed - ypoly(R - 1)

   totdist = ((xdist) ^ 2) + ((ydist) ^ 2)
   If totdist <> 1 Then   'check to see if unit distance from first nearest neighbours is maintained
        GoTo 4
   End If
   
   xdist = xproposed - xpoly(R + 1)
   ydist = yproposed - ypoly(R + 1)
   totdist = ((xdist) ^ 2) + ((ydist) ^ 2)
   If totdist <> 1 Then    'check to see if unit distance from second nearest neighbours is maintained
       GoTo 4
   End If
   
   spacial(xpoly(R), ypoly(R)) = 0  'sets original co-ordinates in spacial array to 0
   xpoly(R) = xproposed
   ypoly(R) = yproposed
   spacial(xpoly(R), ypoly(R)) = 1  'sets new co-ordinates in spacial arrray to 1
   Call edge
   
4  counter = counter + 1 'add one to the counter
   If counter = savecounter Then  'checks to see if every bead in chain has been moved
        counter = 1
        R1 = xpoly(N) - xpoly(1)
        R2 = ypoly(N) - ypoly(1)
        vectorR = (((R1) ^ 2) + ((R2) ^ 2)) 'calculate length of vector between first bead & end bead
        txtvector.Text = Format(vectorR)
        txtvector.Refresh
   Else
        GoTo 1
   End If
           
End Sub

Public Sub Form_Load()

SendMessage cmdstart.hWnd, &HF4&, &H0&, 0&  'changes cmdstart button to C++ style

Call FileExist("C:\Program Files\Microsoft Office\Office\EXCEL.EXE")

Call FileExist1("C:\WINDOWS\desktop\TRIAL.txt")

End Sub

Private Sub txtchains_LostFocus()

N = Val(txtchains.Text)
    If N Mod 2 = 0 Then
        MsgBox "Chains must be an odd number!"
        txtchains.SetFocus
    Else
        Exit Sub
    End If

End Sub

Private Sub txtarray_LostFocus()

M = Val(txtarray.Text)
If M Mod 2 <> 0 Then
    MsgBox "Array must be an even number!"
    txtarray.SetFocus
    Else
        Exit Sub
End If

End Sub

Public Sub edge()

Dim xmove As Integer, ymove As Integer

'checks to see if either x or y co-ordinates approach the edge of the array
'if they do calculates the distance between the centre of the array and the
'centre bead and then moves all the beads back by the same distance

If xpoly(R) = 1 Or xpoly(R) = M Or ypoly(R) = 1 Or ypoly(R) = M Then
    xmove = xpoly((N + 1) / 2) - (M / 2)
    ymove = ypoly((N + 1) / 2) - (M / 2)
    For i = 1 To N
        spacial(xpoly(i), ypoly(i)) = 0
        xpoly(i) = xpoly(i) - xmove
        ypoly(i) = ypoly(i) - ymove
        spacial(xpoly(i), ypoly(i)) = 1
    Next i
End If

End Sub

Public Function FileExist(filename As String) As Integer

Dim q As Integer

q = Len(Dir$(filename))  'checks to see if installed copy of excel
If q = 0 Then
    MsgBox "This program requires a Working copy of Excel"
    Unload Me           'if excel not found program won't execute
Else
    Exit Function
End If

End Function

Public Function FileExist1(filename As String) As Integer

Dim q As Integer

q = Len(Dir$(filename))  'checks for file
If q = 0 Then
    Exit Function
Else
    Kill (filename)     'if file exists deletes it
End If

End Function


next bit in seperate module




Option Explicit

Dim varNum As Long
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim B3 As Long

Public Sub Data()

On Error Resume Next

'Start the excel COM and make it visible.
Set objExcel = GetObject("", "excel.application")
'Set objExcel = excel.Application ' Seems to cause a memory leak

objExcel.Visible = False
   
'Start a workbook.

Set objWorkbook = objExcel.Workbooks.Add

'Turn off the alerts, otherwise user will have to confirm actions.
objExcel.DisplayAlerts = False

'Ensure there is only one worksheet.
Do While objWorkbook.Worksheets.Count > 1
    Set objWorksheet = objWorkbook.Worksheets.Item(objWorkbook.Worksheets.Count)
    objWorksheet.Delete
Loop

'Set objWorksheet to the remaining worksheet.
Set objWorksheet = ActiveSheet

'Rename the sheet to Results.
objWorksheet.Name = "Results"

'Enter data
With objWorksheet.QueryTables.Add(Connection:="text;C:\WINDOWS\desktop\TRIAL.txt", Destination:=Range("A3"))
    .Name = "Results"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = xlWindows
    .TextFileStartRow = 2
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1)
    .Refresh BackgroundQuery:=False
End With
   
Range("B3").Select
Selection.End(xlDown).Select 'find Last Row
LastRow = Selection.Row
LastColumn = Selection.Column

'Draw Chart with the data
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Results").Range("B3:B" & LastRow), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Results!R3C1" & LastRow & ":C1"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Results"
With ActiveChart
    .HasTitle = True
    .ChartTitle.Characters.Text = "Results" ' Title
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Number of Cycles" ' X-Axis
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Root Mean Square Distance "   ' Y-Axis
End With
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveSheet.Shapes("Chart 1")
    .Left = 50
    .Top = 180
End With

'Headers
objWorksheet.Cells(1, 1) = "Number of Cycles"
objWorksheet.Cells(1, 1).Font.Bold = True
objWorksheet.Cells(1, 2) = "Root Mean Square Distance"
objWorksheet.Cells(1, 2).Font.Bold = True


Range("A:A", "B:B").Select
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .MergeCells = False
End With

Range("A:A", "B:B").Select
Selection.Columns.AutoFit
objExcel.Visible = True

'Turn back on alerts
objExcel.DisplayAlerts = True

'Free up memory
Set objExcel = Nothing
Set objWorksheet = Nothing
Set objWorkbook = Nothing
End Sub

0
 

Author Comment

by:Praetor
ID: 2627568
Adjusted points from 70 to 110
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2627651
LastRow and LastColumn seem to be switching places in your code, but if the line that doesn't work is still:

ActiveChart.SeriesCollection(1).XValues = "=Results!R3C1" & LastRow & ":C1"

Then replace that line with:

ActiveChart.SeriesCollection(1).XValues = ("Results!R3" & LastRow & ":C1")

and see if it works.  Otherwise, are you still encountering the same problem?  The Data() procedure works on my machine.  I can't test the whole thing without creating a bunch of files . . .

Wes
0
 

Author Comment

by:Praetor
ID: 2628425
yeah I agree it works but it doesn't do what I want it to do.

On the x axis it reads:
Results!R32:C1

I want it to be numbered

example
if you plotted y values against x values and the x values were 1,2,3,4,5

on the chart u would expect tick marks and 1,2,3,4,5 next to these tick marks, and at every tick mark there would be a point on the chart corresponding to that y value.

That is what I require.

WS we'll get there in the end, it's always the easy problems that r the most difficult so sort...give me an mapi problem anyday :)
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2628661
On my chart the data points are labeled on the chart but not on the lower scale.  To get rid of the "Results!R32:C1" at the bottom of the chart, just comment out this line:

ActiveChart.SeriesCollection(1).XValues = ("Results!R3" & Lastcolumn & ":C1")

It looks like this has been the problem all the time.  I don't know what setting the XValues property does, but apparently it doesn't affect the chart data.  I might be missing something . . .

Replace these lines of Data():

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Results").Range("B3:B" & LastRow), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Results!R3C1" & LastRow & ":C1"

with:

Charts.Add
ActiveChart.ChartType = xlLineStacked
ActiveChart.SetSourceData Source:=Sheets("Results").Range("B3:B" & Lastrow), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).ApplyDataLabels

Wes
0
 

Author Comment

by:Praetor
ID: 2628754
ActiveChart.SeriesCollection(1).XValues = "=Results!R3C1" & LastRow & ":C1"

this is the line that should make my values in column 1 the x axis. I need this line in the code, problem is not sure of the syntax. If I specify actual R1C1 co-ordinates it works fine. eg

ActiveChart.SeriesCollection 1).XValues="=Results!R3C1:R103C1"

I need to replace R103 by the row that the data goes down to and that is only determined at run time !!

This is really beginning to do my head in :)
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2628772
Please let me repeat:

That line does not affect the chart data in this particular code.  That line does not set the X-axis.  This line sets the X-axis and Y-axis data:

ActiveChart.SetSourceData Source:=Sheets("Results").Range("B3:B" & LastRow), PlotBy:=xlColumns

Paste it in and give it a shot.

Wes
0
 

Author Comment

by:Praetor
ID: 2628810
ActiveChart.SeriesCollection 1).XValues="=Results!R3C1:R103C1"

this code most definitely sets the numbers to be displayed on the x axis !!  I have just tried it. It takes my data in the 1st column and places these values along the x axis.

I can't take much more....
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2628898
First:  Did you try it like I suggested yet?  Please leave out that line and try running the code.

Just to make sure I understand:

Since your data is only one column wide (:=Sheets("Results").Range("B3:B" & LastRow), PlotBy:=xlColumns) then how can you get data labels on the Y (up and down) and X (left to right) axes?  I have labels next to each data point, and in addition the Y-axis is scaled.  On the X-axis I have each data's ordinal number, as in the first data point is "1", the next is "2", and so on.

Let's say I have only 2 data, "10" and "12"

Test case:

On my chart the Y-axis is scaled from 9 to 12.5.  The X-axis has a "1" and a "2", numbering each data point.  Next to each data point is the data label.  In this case would you want a "10" and a "12" at the bottom (X-axis) also?

Wes
0
 

Author Comment

by:Praetor
ID: 2628933
no, my data is 2 columns wide. The first column are the y values. Second the x values. I want the x ordinal numbers changed to the data in the 1st column.

as far as I was aware:

ActiveChart.SeriesCollection(1).XValues = "=Results!R3C1" & LastRow & ":C1"

specified the range for the x values in cloumn 1....maybe all my code is bs :)
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2628953
Your code is setting your chart to one column.

This is one column:

Range("B3").Select
Selection.End(xlDown).Select 'find Last Row
LastRow = Selection.Row
LastColumn = Selection.Column

and this is one column:

ActiveChart.SetSourceData Source:=Sheets("Results").Range("B3:B" & LastRow), PlotBy:=xlColumns

Yes, in that case your code is screwed.  I suggest you get some Excel VBA books and start from scratch.

Wes
0
 

Author Comment

by:Praetor
ID: 2629001
Wes, I have sorted it all I needed to do was to alter the formatting of the x axis..........thank fcuk for that :)

if u can sort out this the points r yours :

One more small problem, at the bottom my code should remove excel from the memory, but if I check in the task manager excel is still running, how do I remove it from memory ?



0
 
LVL 4

Accepted Solution

by:
wesleystewart earned 110 total points
ID: 2629030
I actually believe I already answered that part, but to summarize:

First:  Set Excel objects to nothing at the end of the code:

Set objExcel = Nothing
Set objWorksheet = Nothing
Set objWorkbook = Nothing

You have to do this for EVERY procedure that instantiates them.

Next:  Be aware that if the code fails PRIOR to reaching the above lines in code, it won't clear out the objects from memory and they will stay in the taskbar.

Your Sub Data() works properly as far as objects are concerned.  Is there any chance that you have any other apps running that are creating Excel objects?
0
 

Author Comment

by:Praetor
ID: 2629048
Oh fcuk it, you've helped me enough, here r your points. Thanks for all your help. Got there in the end :)
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now