• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with excel chart

J

james_kay

New Member
Hi ,

I am a beginner in VBA but I need to amend a VBA code from an old colleague

The purpose of the code is to

1) create a chart of all elements on the vertical axis) against a 24 hour time represented in seconds on the horizontal axis.

the problem is that

1) the chart get created ,but not all the elements on the vertical axis gets shown on the vertical axis.

it seems to skip a few elements .

I found a way of manually forcing it to show all elements by going to Menu - Format axis -> axis options -> interval between labels --> specify interval unit = 1.

But i would like to put this in the VBA code below this so that all rows on the y axis gets a row on my chart.

The VBA code is below

Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW" () As Long
Declare Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long
Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (MyDest As Any, MySource As Any, ByVal MySize As Long)

Function CmdToSTr(Cmd As Long) As String
Dim Buffer() As Byte
Dim StrLen As Long

If Cmd Then
StrLen = lstrlenW(Cmd) * 2
If StrLen Then
ReDim Buffer(0 To (StrLen - 1)) As Byte
CopyMemory Buffer(0), ByVal Cmd, StrLen
CmdToSTr = Buffer
End If
End If
End Function

Sub processinput(cmdline)

filepos = Application.Find("/e/", cmdline)
If Not IsError(filepos) Then
ifile = Mid(cmdline, filepos + 3)

loadfile (ifile)

Call createprocesscharts

ActiveWorkbook.Close True

End If

End Sub

Sub createprocesscharts()
Dim sets(2, 365) As Long
Dim limits(2, 365) As Long
Dim dates(365) As String
c = 1
r = 2
Sheets("Sheet1").Select
dt = Cells(r, 5)
sets(1, c) = r
limits(1, c) = Cells(r, 2)
While (Cells(r, 5) <> "")
If Cells(r, 5) <> dt Then
sets(2, c) = r - 1
limits(2, c) = Cells(r - 1, 2)
dates(c) = Format(Cells(r - 1, 5), "ddd, d mmm yyyy")
dt = Cells(r, 5)
c = c + 1
sets(1, c) = r
limits(1, c) = Cells(r, 2)
End If
r = r + 1
Wend
sets(2, c) = r - 1
limits(2, c) = Cells(r - 1, 2)
dates(c) = Format(Cells(r - 1, 5), "ddd, d mmm yyyy")

For i = 1 To c
Debug.Print sets(1, i) & " " & sets(2, i) & " " & limits(1, i) & " " & limits(2, i) & " " & dates(i)
Call makechart(sets(1, i), sets(2, i), dates(i), limits(1, i), limits(2, i))
Next

End Sub

Sub makechart(r1 As Long, r2 As Long, dt As String, l1 As Long, l2 As Long)
Sheets("Sheet1").Select
rstart = "$A$" & r1
rend = "$C$" & r2
rg = "'Sheet1'!" & rstart & ":" & rend
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range(rg)
ActiveChart.ChartType = xlBarStacked
ActiveChart.Legend.Select
Selection.Delete
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = l1
ActiveChart.Axes(xlValue).MaximumScale = l2
ActiveChart.Axes(xlValue).Select
ActiveSheet.ChartObjects(1).Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Interior.ColorIndex = xlNone
ActiveSheet.ChartObjects(1).Activate
ActiveChart.ChartArea.Select
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=dt

End Sub

Sub loadfile(ifile)
cdir = CurDir()
qfile = "TEXT;" & cdir & "" & ifile
With ActiveSheet.QueryTables.Add(Connection:= _
qfile _
, Destination:=Range("$A$1"))
.Name = ifile
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

End Sub
 
James


Firstly, Welcome to the Chandoo.org forums.


Is there any chance you can paste a sample file showing the problem

You post a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Thanks very much Hui, I have uploaded a sample file -link below


http://dl.dropbox.com/u/36755932/request_id.10-Oct-2012-10-01-53.log.csv.180000.220000.xls
 
Hi Narayan ,


Thanks the line of code you suggested did the trick!.

I can see all the jobs in the new chart, it looks a bit untidy though ;-)

is there any way to increase the chart area in order to make the labels tidier ?


new chart is here

http://dl.dropbox.com/u/36755932/new_chart.xls


Thanks for your help


James.
 
Hi James ,


I don't think there is any possibility of increasing the chart area ; the chart is restricted by the Page Layout ; if you are not going to print the chart , then you can use any larger page size , A3 or even A2 , and increase the size of the chart ; if not , there is no option.


The alternative is to reduce the font size , from 10 to 8 , and make it BOLD.


Another option is to change the chart type from Stacked Bar to Stacked Column , so that the category labels appear on the X-axis ; given that the width of the chart is more than the height , it may look better.


Narayan
 
Hi Narayan , I will like to try the "reduce the font size , from 10 to 8 , and make it BOLD" you suggested . I have this as the code to do it

[pre]
Code: 
ActiveChart.Axes(xlCategory).AxisTitle.Font.FontStyle = "Bold"
ActiveChart.Axes(xlCategory).AxisTitle.Font.Size = 8
[/pre]

is this correct ? where do i insert it in my code above?


Thanks

James.
 
Hi James ,


The best way is to replace the entire makechart procedure in your workbook with the following version , which incorporates your changes :

[pre]
Code: 
Sub makechart(r1 As Long, r2 As Long, dt As String, l1 As Long, l2 As Long)
Sheets("Sheet1").Select
rstart = "$A$" & r1
rend = "$C$" & r2
rg = "'Sheet1'!" & rstart & ":" & rend

ActiveSheet.Shapes.AddChart.Select

With ActiveChart
.SetSourceData Source:=Range(rg)
.ChartType = xlBarStacked
.Legend.Delete
.Axes(xlValue).MinimumScale = l1
.Axes(xlValue).MaximumScale = l2
.SeriesCollection(1).Interior.ColorIndex = xlNone
.Axes(xlCategory).TickLabelSpacing = 1
.Axes(xlCategory).TickLabels.Font.Bold = True
.Axes(xlCategory).TickLabels.Font.Size = 8
.Location Where:=xlLocationAsNewSheet, Name:=dt
End With
End Sub
[/pre]
Just copy this code and paste it in your workbook , replacing the code which is already present in it.


Narayan
 
You must log in or register to reply here.
Back
Top