Subscript out of range with a simple sql :-( [newbie to VBA]

Maximus


Hi all,

can anyone assist on this one (I'm newbie to VBA)
I'm trying to get the list of items from mySql (I have a successful connection)

for some reason I get this error message:
Run-time error '9':
Subscript out of range.

thanks for any pointers!

Code Snippet

Private Sub btnTestReadInMysqlData_Click()

Dim objDB, arrRecord, strRecord, strOutput
Dim oRS, nRec, oFld
Dim row

'Database connection & select all from Table
Set objDB = DBConnect()
Set oRS = objDB.Execute("SELECT * FROM `production` WHERE id<1000000")

'Dump Records from Table
nRec = 0
row = 1
Do While Not oRS.EOF
For Each oFld In oRS.Fields

'WScript.Echo oFld.Name & " = " & oFld.Value

Worksheets("Tabelle1").Cells(row, 1).Value = oFld.Value

'increment
row = row + 1

Next
oRS.MoveNext
Loop

End Sub


Function DBConnect()
Set objDB = CreateObject("ADODB.Connection")
objDB.Open "ago_usgroups"
Set DBConnect = objDB
End Function

Keithyboy1

The worksheet you are writing the recordset to "Tabelle1". Are you sure it exists and have spelt the name correctly

It would help if you could highlight the specific line of code where the error message occurs.

Maximus

THANK YOU!

rucshaca

Iam getting the exact same problem. I just started in VBA last week. I am trying to learn from a problem. This is an already built code and iam learning.

The code works fine when i open following FOUR files:

1) 2263A1.txt

2) 2263A1(version 1).txt

3) 2263A1(version 2).txt

4) 2263A2.txt

The following 2 files contain DATA and some Macros.

5) Data Arrangement 2.3- 04-26-07

6) LN STD Ct

Iam getting problem "Run-time error 91 Subscript Out of Range" and the debugger points me to the line with the * and marked in RED whenever I open only one file i.e. "2263A1.txt" instead of opening all 4 (1 through 4)

'find the first workbook of data and activate it

For i = 1 To Application.Workbooks.Count Step 1
If InStr(Application.Workbooks(i).Name, "Data Arrangement") = 0 And InStr(Application.Workbooks(i).Name, "Macro") = 0 _
And InStr(Application.Workbooks(i).Name, "STDCt") = 0 Then
firstbook = i
Exit For
End If
Next
If doFourPlates Or doTwoPlates Then
* Application.Workbooks(firstbook - 1 + wb).Activate
End If

If StrComp(Worksheets(1).Range("A1"), "SDS 2.3") = 0 Then
Adjust
End If

Please let me know how to fix or atleast give me some directions. Thanks and much appreciated.

Andy Pope

Hi,

The problem is probably caused by the calculated index value being zero.

assuming the variable firstbook = 1 then
firstbook - 1 = 0

so if the variable wb is zero or empty then the index will be zero.

It's not possible to say for definite as not all your code has been posted.

Make sure all your variables are declared by using Option Explict at the top of the code module. This way any variables you are using that have not been declared will cause an error which you can investigate.

rucshaca

This variable is defined as:

Dim firstbook As String

So, do you want me to change to:

Dim firstbook As Explict

how can i print or show the values of these varialbes at runtime when i run the macro via VBE.

i wanted to do something like:

If doFourPlates Or doTwoPlates Then

// this is how i can do in JAVA.

// System.out.println ("my firstbook value is= " + firstbook );

// System.out.println ("my work book value is= " + wb );

* Application.Workbooks(firstbook - 1 + wb).Activate
End If

and where to see these values is there any log generated or how to see these values in VBE

Thanks for your time.

Andy Pope

No. The explicit Option goes at the top of the code module, outside of all routines.

To see the values you could use a message box.

Msgbox "Firstbook=" & firstbook

Or the immediate window in VBE, CTRL+G to display.

Debug.Print "Firstbook=",firstbook

There is no log.


The topic of debugging is quite a large one.
This sites may help
http://www.cpearson.com/excel/Debug.htm

rucshaca

It's weired. I just started my PC (btw it's MAC) and now it;s breaking at the YELLOW line:

I did some message boxes and got some results back as pop ups and the variable values are ...

sourceSheet = 1

sample = 0

what is the line of code doing please help me understand.

nextset = 0

For i = 4 To 500 Step 1

If StrComp(Worksheets(sourceSheet).Cells(i, sample), "Sample Name", 1) = 0 Then

nextset = i

Exit For

End If

Next

Andy Pope

Direct from the help file.
The strcomp function compares to strings and returns a value

If StrComp returns
string1 is less than string2 -1
string1 is equal to string2 0
string1 is greater than string2 1
string1 or string2 is Null Null

In your code the content of a cell is being compared to "Sample Name"

The 1 is an argument that determines the type of comparison.
Constant Value Description
vbUseCompareOption -1 Performs a comparison using the setting of the Option Compare statement.
vbBinaryCompare 0 Performs a binary comparison.
vbTextCompare 1 Performs a textual comparison.
vbDatabaseCompare 2 Microsoft Access only. Performs a comparison based on information in your database.


And you will have problems if the column index variable, sample, is zero.

rucshaca

I think iam getting very close to resolving this issue, thanks for your help.

Now, that i know what my variable values are .... i corrected them. But when i run the Macro, it gets me the result and generate my graph BUT my macro gives me an exception as well.

-----------------
Runtime Error 1004
Application-defined or object-defined error
-----------------
The code that gets highlighted while i debug is: Please see below in RED.


With ActiveChart
.ChartGroups(1).GapWidth = 100
.HasTitle = True
.ChartTitle.Font.Size = 14
.ChartTitle.Font.Name = "Helvetica"
.ChartTitle.Font.FontStyle = "Bold"
.ChartTitle.Text = projectnumber & plate & " Plate " & platenumbersArray(wb - 1) & vbLf & Worksheets(sourceSheet).Range(col1 & "2") & " vs. " & Worksheets(sourceSheet).Range(col2 & "2")
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory).AxisTitle.Font.Name = "Helvetica"
.Axes(xlCategory).AxisTitle.Font.FontStyle = "Italic"
.Axes(xlCategory).AxisTitle.Font.Size = 12
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Sample Number"
.Axes(xlValue).MajorUnit = 1
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue).MaximumScale = scalemax
.Axes(xlValue).MinimumScale = scalemin
.Axes(xlValue).AxisTitle.Font.Name = "Helvetica"
.Axes(xlValue).AxisTitle.Font.FontStyle = "Italic"
.Axes(xlValue).AxisTitle.Font.Size = 12
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = Worksheets(sourceSheet).Range(datarow & "2")
.Axes(xlValue).TickLabels.NumberFormat = "0"
.Axes(xlValue).HasMajorGridlines = True
.Axes(xlValue).HasMinorGridlines = False
.Axes(xlValue).MajorTickMark = xlOutside
.Axes(xlValue).TickLabelPosition = xlNextToAxis
.HasLegend = False
.ApplyDataLabels Type:=xlDataLabelsShowLabel, LegendKey:=False
.SeriesCollection(1).DataLabels.Font.Size = 9
.HasDataTable = False
.Axes(xlValue).TickLabels.Alignment = xlHAlignCenter
.Axes(xlValue).TickLabels.Font.Size = 9

End With

Andy Pope

Hi,

That alignment will only work if you have multiple rows of category labels. The it applies to the outer labels on the category axis and not the Value one as the code implies.

Even so when recording the action the constant used was xlCenter.

What type of chart are you creating
Did you record that code

rucshaca

Sorry about getting back to you late. I have resolve most of the issues using your debug statements.

Just got stuck ....what does the following means

especially

& "!R3C27:R98C27", MinusValues:= _
"=" & wsName & "!R3C27:R98C27"

------------------------------------------------------

ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlBoth, _
Type:=xlCustom, Amount:="=" & wsName _
& "!R3C27:R98C27", MinusValues:= _
"=" & wsName & "!R3C27:R98C27"

------------------------------------------------------

Andy Pope

It is setting the error bars to use values stored in cells.
The cell reference is in R1C1 notation.

AA3:AA27

rucshaca

you mean to say:

R3C27:R98C27 === AA3:AA27

Andy Pope

Well actually I really meant,

R3C27:R98C27 === AA3:AA98


Row3Column27:Row98Column27