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"

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

PraetorAuthor Commented:

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

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Hmm. I thought those were R1C1 coordinates. I guess that's a swing and a miss...

Wes

0

PraetorAuthor Commented:

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.

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.

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

PraetorAuthor Commented:

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.

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

PraetorAuthor Commented:

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"

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

Wes

0

PraetorAuthor Commented:

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

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

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"

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

PraetorAuthor Commented:

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 :)

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:

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 . . .

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

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.

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

PraetorAuthor Commented:

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.

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

Wes

0

PraetorAuthor Commented:

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 ?

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

PraetorAuthor Commented:

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

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.