|
VB 6 Helper Newsletter
|
Rod Stephens
|
Oct 25, 2008 07:08 PDT
|
Have a great week and thanks for subscribing!
Rod
RodSte-@vb-helper.com
----------
*** Now Available ***
Visual Basic 2008 Programmer's Reference
http://www.amazon.com/exec/obidos/ASIN/0470182628/vbhelper/
==========
VB6 Contents:
1. New HowTo: Round a number to the nearest multiple of a template in
Visual Basic 6
Both Contents:
2. New HowTo: Use VBA code in Excel to import a CSV file and use it to
make a line graph
==========
++++++++++
<VB6>
++++++++++
==========
1. New HowTo: Round a number to the nearest multiple of a template in
Visual Basic 6
http://www.vb-helper.com/howto_round_to_template.html
http://www.vb-helper.com/HowTo/howto_round_to_template.zip
Thanks to Randy Diven.
The RoundToTemplate function divides a value by a template number,
rounds the result, and then multiplies this by the template number.
Public Function RoundToTemplate(ByVal value As Double, ByVal template As
Double) As Double
Debug.Assert template > 0.0000000001
RoundToTemplate = template * Round(value / template, 0)
End Function
The result is the multiple of the template closest to the original
value.
If the template number is something "reasonable" such as 5, 25 or a
power of 10, the result makes good intuitive sense. If the template
number is something less intuitive, such as 17 or 33, the result is less
obvious.
Here are some sample results:
value template RoundtoTemplate
123.08875 25 125
20 120
0.01 123.09
0.05 123.10
17.17 120.19
7 126
==========
++++++++++
<Both>
++++++++++
==========
2. New HowTo: Use VBA code in Excel to import a CSV file and use it to
make a line graph
http://www.vb-helper.com/howto_excel_import_csv_linegraph.html
http://www.vb-helper.com/HowTo/howto_excel_import_csv_linegraph.zip
The following shows the format of the CSV (comma separated value) file
containing the data.
Date,Scores,Colors,Messes
10/1/08,10,5,3
10/2/08,15,8,12
10/3/08,7,9,7
10/4/08,11,14,9
When you click the worksheet's Import Data button, the following code
calls subroutine ImportLineGraph.
After loading the data, the code makes a new Chart object using the
imported data's result range as its source. The code sets a few chart
values such as the axis titles and is done.
Private Sub cmdImportData_Click()
ImportLineGraph "graphdata.csv", "Sheet1", "My Chart", "Date",
"Number"
End Sub
Subroutine ImportLineGraph first loads the CSV file. This example
assumes that the file has the format shown earlier. In particular, it
assumes that the file contains four columns containing a date and three
values that should be formatted as general data.
Sub ImportLineGraph(ByVal file_name As String, ByVal sheet_name As
String, ByVal chart_title As String, ByVal x_axis_title As String, ByVal
y_axis_title As String)
Dim work_sheet As Worksheet
Dim query_table As QueryTable
Dim new_chart As Chart
' Load the CSV file.
Set work_sheet = Sheets(sheet_name)
Set query_table = work_sheet.QueryTables.Add( _
Connection:="TEXT;" & file_name, _
Destination:=work_sheet.Range("A1"))
With query_table
.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
' Set the data types for the columns.
.TextFileColumnDataTypes = Array(xlMDYFormat, xlGeneralFormat,
xlGeneralFormat, xlGeneralFormat)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
' Make the line graph.
query_table.Destination.Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData _
Source:=query_table.ResultRange, _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:=sheet_name
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = chart_title
If Len(x_axis_title) = 0 Then
.Axes(xlCategory, xlPrimary).HasTitle = False
Else
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
x_axis_title
End If
If Len(y_axis_title) = 0 Then
.Axes(xlValue, xlPrimary).HasTitle = False
Else
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
y_axis_title
End If
End With
End Sub
==========
Archives:
http://www.topica.com/lists/VBHelper
http://www.topica.com/lists/VB6Helper
http://www.topica.com/lists/VBNetHelper
http://www.vb-helper.com/cgi-bin/mojo/mojo.cgi?flavor=archive&list=VB_Helper_Newsletter
Post questions at:
http://www.topica.com/lists/VBHelperQA
|
|
 |
|
