Abandoned [RELEASE] Trade Computer Extension

Bragolatch · Mar 15, 2015

ghostdragon said:
Sorry if I am just being dense here, but I have installed the app and it all seems to load fine. The problem I am having is that the system I am in isn't on the list, but when I click on database (or any of the other buttons except options), it changes to 'LOCKED'. Has anyone seen this and knows how I can fix it? I don't get any errors when the app loads, so not sure if this is some issue with local rights to access the file?

Did you follow the instructions on the first post and install the Access Runtime for your version of Excel ?

eventure · Mar 15, 2015

dave562 said:
There seems to be an error in the Me.BTN_Save.Caption function.

Intermittently when I attempt to Save a Commodity record, the Debug mode will display the breakpoint below. (see image)

I am able to close TCE.xlsm (No Save) and re-open it okay. The only data loss is whatever record failed to save.

View attachment 21341

Did you use the V1.37d update? Is this a haltpoint, or you got an error?

ghostdragon said:
Sorry if I am just being dense here, but I have installed the app and it all seems to load fine. The problem I am having is that the system I am in isn't on the list, but when I click on database (or any of the other buttons except options), it changes to 'LOCKED'. Has anyone seen this and knows how I can fix it? I don't get any errors when the app loads, so not sure if this is some issue with local rights to access the file?

Please check you ShipLog panel. What did it says? Check also your AppConfig.xml file.

toepick · Mar 15, 2015

Hey..awesome work..
when I run the OCR Scan off the commodity window when I get to minerals it is always switching Bauxite and Painite. So Bauxite always shows as the price of Painite, and Painite as the price of Bauxite.

Using 1.37d ontop of 1.37 installer and the two station database

thanks!

Distance · Mar 15, 2015

toepick said:
Hey..awesome work..
when I run the OCR Scan off the commodity window when I get to minerals it is always switching Bauxite and Painite. So Bauxite always shows as the price of Painite, and Painite as the price of Bauxite.

Using 1.37d ontop of 1.37 installer and the two station database

thanks!

You need to add it in to EliteOCR correctly :

https://forums.frontier.co.uk/showthread.php?t=68771&page=29&p=1925134&viewfull=1#post1925134

Bragolatch · Mar 15, 2015

Eventure said:
Selecting method is changed, click and hold to select all stations, Control + Click to unselected.

Update (V1.37d) released:
- Removed error routine, till fixed.

Ok, I started fresh again with V1.37 Full non-installer and Update V1.37d

No more error 9 on opening

So I tried to import the "can be imported" db you sent me and I still get the exact same behavior with the 1004 error ....

Then I tried to replace the mdb directly, with my latest one (NOT the one modified by you)

It actually seemed to work, loaded fine, I could see all my stations and was able to scan and save the commodities for my current position.
The TRADE panel opened and again everything was working and I could see all my stations.
One had an obvious mistake that gave me the kind of profit we can only dream of lol
So I selected that station as my position (even if I was not actually there)
Clicked on COMMODITY and fixed the price with a more logical value. Saved and screwed the hole db lol

Here is a pic of the NEW DESTINATION panel afterwards :

It's weird that the position panel is still showing all the station properly and the Destination one looks like that.
From what I understand they both get their info from the same tables !!!

anyway I expected an error so my I kept my final try for the other db you sent me TCE.mdb_final_import

I closed down everything, replaced Tce.mdb with that one and started TCE again.

On the second loading square I got the debug version of error 1004:
( which is a bit different from the previous one!)

Sub Set_Vars()

AzStars = Worksheets("Data").Cells(2, 2).Value
AzRegStations = Worksheets("Data").Cells(3, 2).Value
AzURegStations = Worksheets("Data").Cells(4, 2).Value
AzPrices = Worksheets("Data").Cells(5, 2).Value
AzGoods = Worksheets("Data").Cells(6, 2).Value
AzCategory = Worksheets("Data").Cells(7, 2).Value
AzEconomy = Worksheets("Data").Cells(8, 2).Value
AzJurisdiction = Worksheets("Data").Cells(9, 2).Value
AzLang = Worksheets("Data").Cells(14, 2).Value
AzType = Worksheets("Data").Cells(18, 2).Value
AzLang_Eng = Worksheets("Data").Cells(15, 2).Value
AzLang_Deu = Worksheets("Data").Cells(16, 2).Value
AzLang_Fra = Worksheets("Data").Cells(17, 2).Value

If Worksheets("DB_Stars").Cells(2, 1).Value <> 1 Then
Worksheets("DB_Stars").Activate
ActiveWorkbook.Worksheets("DB_Stars").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DB_Stars").Sort.SortFields.Add Key:=Range( _
"A2:A1000001"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("DB_Stars").Sort
.SetRange Range("A1:G1000001")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If

If Worksheets("DB_Economy").Cells(2, 1).Value <> 1 Then
Worksheets("DB_Economy").Activate
ActiveWorkbook.Worksheets("DB_Economy").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DB_Economy").Sort.SortFields.Add Key:=Range( _
"A2:A101"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("DB_Economy").Sort
.SetRange Range("A1:B101")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If

If Worksheets("DB_Goods").Cells(2, 1).Value <> 1 Then
Worksheets("DB_Goods").Activate
ActiveWorkbook.Worksheets("DB_Goods").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DB_Goods").Sort.SortFields.Add Key:=Range( _
"A2:A1001"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("DB_Goods").Sort
.SetRange Range("A1

1001")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If

If Worksheets("DB_Jurisdiction").Cells(2, 1).Value <> 1 Then
Worksheets("DB_Jurisdiction").Activate
ActiveWorkbook.Worksheets("DB_Jurisdiction").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DB_Jurisdiction").Sort.SortFields.Add Key:=Range( _
"A2:A101"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("DB_Jurisdiction").Sort
.SetRange Range("A1:B101")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If

Ar_DBStars = Worksheets("DB_Stars").Range("A2:G" & AzStars + 1)
Ar_DBRegStations = Worksheets("DB_Stations").Range("A2:Q" & AzRegStations + 1)
Ar_DBURegStations = Worksheets("DB_Stations_UR").Range("A2:Q" & AzURegStations + 1)
Ar_DBPrices = Worksheets("DB_StPrices").Range("A2:F" & AzPrices + 1)
Ar_DBGoods = Worksheets("DB_Goods").Range("A2:F" & AzGoods + 1)
Ar_DBCategory = Worksheets("DB_Category").Range("A2:B" & AzCategory + 1)
Ar_DBEconomy = Worksheets("DB_Economy").Range("A2:G" & AzEconomy + 1)
Ar_DBJurisdiction = Worksheets("DB_Jurisdiction").Range("A2:B" & AzJurisdiction + 1)
Ar_Type = Worksheets("DB_Stationtype").Range("A2:C" & AzType + 1)

End Sub

I'll just try running V1.37d with a brand new database for a while and see how that goes.
Please let me/us know, when you make the new version, If you think it will be able to import our old screwed up db.

If not, it's just too bad and part of the development process !

Personally, I think that it is more important to work toward the end result then on importing old db !!!

We haven't been using it that long and we can restart from scratch !

Those who are not willing to, can always stick with the last version that was working for them !

That way, you can just make all the changes you wish,
get to the final, stable, version faster.

And then work on a way to import old db into the final version if you still wish to do so.

Your choice thought, you're the boss

eventure · Mar 15, 2015

Bragolatch said:
Ok, I started fresh again with V1.37 Full non-installer and Update V1.37d

No more error 9 on opening

So I tried to import the "can be imported" db you sent me and I still get the exact same behavior with the 1004 error ....

Then I tried to replace the mdb directly, with my latest one (NOT the one modified by you)

It actually seemed to work, loaded fine, I could see all my stations and was able to scan and save the commodities for my current position.
The TRADE panel opened and again everything was working and I could see all my stations.
One had an obvious mistake that gave me the kind of profit we can only dream of lol
So I selected that station as my position (even if I was not actually there)
Clicked on COMMODITY and fixed the price with a more logical value. Saved and screwed the hole db lol

Here is a pic of the NEW DESTINATION panel afterwards :

View attachment 21412

It's weird that the position panel is still showing all the station properly and the Destination one looks like that.
From what I understand they both get their info from the same tables !!!

anyway I expected an error so my I kept my final try for the other db you sent me TCE.mdb_final_import

I closed down everything, replaced Tce.mdb with that one and started TCE again.

On the second loading square I got the debug version of error 1004:
( which is a bit different from the previous one!)

View attachment 21413

Sub Set_Vars()

AzStars = Worksheets("Data").Cells(2, 2).Value
AzRegStations = Worksheets("Data").Cells(3, 2).Value
AzURegStations = Worksheets("Data").Cells(4, 2).Value
AzPrices = Worksheets("Data").Cells(5, 2).Value
AzGoods = Worksheets("Data").Cells(6, 2).Value
AzCategory = Worksheets("Data").Cells(7, 2).Value
AzEconomy = Worksheets("Data").Cells(8, 2).Value
AzJurisdiction = Worksheets("Data").Cells(9, 2).Value
AzLang = Worksheets("Data").Cells(14, 2).Value
AzType = Worksheets("Data").Cells(18, 2).Value
AzLang_Eng = Worksheets("Data").Cells(15, 2).Value
AzLang_Deu = Worksheets("Data").Cells(16, 2).Value
AzLang_Fra = Worksheets("Data").Cells(17, 2).Value

If Worksheets("DB_Stars").Cells(2, 1).Value <> 1 Then
Worksheets("DB_Stars").Activate
ActiveWorkbook.Worksheets("DB_Stars").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DB_Stars").Sort.SortFields.Add Key:=Range( _
"A2:A1000001"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("DB_Stars").Sort
.SetRange Range("A1:G1000001")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If

If Worksheets("DB_Economy").Cells(2, 1).Value <> 1 Then
Worksheets("DB_Economy").Activate
ActiveWorkbook.Worksheets("DB_Economy").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DB_Economy").Sort.SortFields.Add Key:=Range( _
"A2:A101"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("DB_Economy").Sort
.SetRange Range("A1:B101")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If

If Worksheets("DB_Goods").Cells(2, 1).Value <> 1 Then
Worksheets("DB_Goods").Activate
ActiveWorkbook.Worksheets("DB_Goods").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DB_Goods").Sort.SortFields.Add Key:=Range( _
"A2:A1001"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("DB_Goods").Sort
.SetRange Range("A11001")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If

If Worksheets("DB_Jurisdiction").Cells(2, 1).Value <> 1 Then
Worksheets("DB_Jurisdiction").Activate
ActiveWorkbook.Worksheets("DB_Jurisdiction").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DB_Jurisdiction").Sort.SortFields.Add Key:=Range( _
"A2:A101"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("DB_Jurisdiction").Sort
.SetRange Range("A1:B101")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If

Ar_DBStars = Worksheets("DB_Stars").Range("A2:G" & AzStars + 1)
Ar_DBRegStations = Worksheets("DB_Stations").Range("A2:Q" & AzRegStations + 1)
Ar_DBURegStations = Worksheets("DB_Stations_UR").Range("A2:Q" & AzURegStations + 1)
Ar_DBPrices = Worksheets("DB_StPrices").Range("A2:F" & AzPrices + 1)
Ar_DBGoods = Worksheets("DB_Goods").Range("A2:F" & AzGoods + 1)
Ar_DBCategory = Worksheets("DB_Category").Range("A2:B" & AzCategory + 1)
Ar_DBEconomy = Worksheets("DB_Economy").Range("A2:G" & AzEconomy + 1)
Ar_DBJurisdiction = Worksheets("DB_Jurisdiction").Range("A2:B" & AzJurisdiction + 1)
Ar_Type = Worksheets("DB_Stationtype").Range("A2:C" & AzType + 1)

End Sub

Please open the TCE.mdb and check the Stars table and their name. Your error says, that no stars were imported.

toepick · Mar 15, 2015

Distance said:
You need to add it in to EliteOCR correctly :

https://forums.frontier.co.uk/showthread.php?t=68771&page=29&p=1925134&viewfull=1#post1925134

Ahh! Checking that out now, thank you Distance!!

BennyBoyW · Mar 15, 2015

Bragolatch said:
We haven't been using it that long and we can restart from scratch !

Those who are not willing to, can always stick with the last version that was working for them !

That way, you can just make all the changes you wish,
get to the final, stable, version faster.

And then work on a way to import old db into the final version if you still wish to do so.

Your choice thought, you're the boss

I totally agree

Bragolatch · Mar 15, 2015

Eventure said:
Please open the TCE.mdb and check the Stars table and their name. Your error says, that no stars were imported.

The star table has 22378 entries and they seem fine to me !
only 3 have notes and 40 have SOK (36 of them are 1 and 4 are 3)

What is SOK ? (state of exploration ? no, partial, complete ?)

Bragolatch · Mar 15, 2015

Bragolatch said:
I'll just try running V1.37d with a brand new database for a while and see how that goes.

Added 1 station, jumped to another system that was already in the db,
added another station and when I went to select a destination :

Run-time error 13:

Private Sub ScrollBar1_Change()

Dim itxt1 As String, itxt2 As String, itxt3 As String, itxt4 As String, itxt5 As String, itxt6 As String, itxt7 As String, itxt8 As String, itxt9 As String, iTxt10 As String, ID As Integer
Dim iName1 As Variant, iName2 As Variant, iName3 As Variant, iName4 As Variant, iName5 As Variant, iName6 As Variant, iName7 As Variant, iName8 As Variant, iName9 As Variant, iName10 As Variant
Dim a As Long

itxt1 = "Back_"
itxt2 = "System"
itxt3 = "Station"
itxt4 = "Eco"
itxt8 = "Distance"
itxt9 = "Profit"
iTxt10 = "Update"

For a = 1 + Me.ScrollBar1.Value To 23 + Me.ScrollBar1.Value
'------> Zeile
iName1 = itxt1 & a - Me.ScrollBar1.Value
Me.Controls(iName1).Visible = True
'------> System
iName2 = itxt2 & a - Me.ScrollBar1.Value
Me.Controls(iName2).Caption = Worksheets("Systems").Cells(1 + a, 13).Text
Me.Controls(iName2).Visible = True
'------> Station
iName3 = itxt3 & a - Me.ScrollBar1.Value
Me.Controls(iName3).Caption = Worksheets("Systems").Cells(1 + a, 15).Text
Me.Controls(iName3).Visible = True
'------> Economy
iName4 = itxt4 & a - Me.ScrollBar1.Value
If Worksheets("Systems").Cells(1 + a, 17).Text = "" Then
Me.Controls(iName4).Caption = "NO DATA"
Else
Me.Controls(iName4).Caption = Worksheets("Systems").Cells(1 + a, 17).Text
End If
Me.Controls(iName4).Visible = True
'------> Distance
iName8 = itxt8 & a - Me.ScrollBar1.Value
Me.Controls(iName8).Caption = Worksheets("Systems").Cells(1 + a, 20).Text & " LY"
Me.Controls(iName8).Visible = True
'------> Discount
iName9 = itxt9 & a - Me.ScrollBar1.Value
If Worksheets("Systems").Cells(1 + a, 21).Value = 999 Or Worksheets("Systems").Cells(1 + a, 21).Value = "" Then
Me.Controls(iName9).Caption = "NO DATA"
Else
Me.Controls(iName9).Caption = Worksheets("Systems").Cells(1 + a, 21).Value & " Cr."
End If
Me.Controls(iName9).Visible = True
'------> Update
iName10 = iTxt10 & a - Me.ScrollBar1.Value
If Worksheets("Systems").Cells(1 + a, 22).Value = 0 Then
Me.Controls(iName10).Caption = "NO DATA"
Else
Me.Controls(iName10).Caption = Worksheets("Systems").Cells(1 + a, 22).Text
End If
Me.Controls(iName10).Visible = True
If Worksheets("Systems").Cells(1 + a, 14).Value = Worksheets("Werte").Cells(13, 2).Value Then
Me.Controls(iName1).BackColor = &H80FF&
Me.Controls(iName2).ForeColor = &H0&
Me.Controls(iName3).ForeColor = &H0&
Me.Controls(iName4).ForeColor = &H0&
Me.Controls(iName8).ForeColor = &H0&
Me.Controls(iName9).ForeColor = &H0&
Me.Controls(iName10).ForeColor = &H0&
Else
Me.Controls(iName1).BackColor = &H102030
Me.Controls(iName2).ForeColor = &HFFFFFF
Me.Controls(iName3).ForeColor = &HFFFFFF
Me.Controls(iName4).ForeColor = &HFFFFFF
Me.Controls(iName8).ForeColor = &HFFFFFF
Me.Controls(iName9).ForeColor = &HFFFFFF
Me.Controls(iName10).ForeColor = &HFFFFFF
End If
Next a

End Sub

eventure · Mar 15, 2015

Bragolatch said:
Added 1 station, jumped to another system that was already in the db,
added another station and when I went to select a destination :

Run-time error 13:

Private Sub ScrollBar1_Change()

Dim itxt1 As String, itxt2 As String, itxt3 As String, itxt4 As String, itxt5 As String, itxt6 As String, itxt7 As String, itxt8 As String, itxt9 As String, iTxt10 As String, ID As Integer
Dim iName1 As Variant, iName2 As Variant, iName3 As Variant, iName4 As Variant, iName5 As Variant, iName6 As Variant, iName7 As Variant, iName8 As Variant, iName9 As Variant, iName10 As Variant
Dim a As Long

itxt1 = "Back_"
itxt2 = "System"
itxt3 = "Station"
itxt4 = "Eco"
itxt8 = "Distance"
itxt9 = "Profit"
iTxt10 = "Update"

For a = 1 + Me.ScrollBar1.Value To 23 + Me.ScrollBar1.Value
'------> Zeile
iName1 = itxt1 & a - Me.ScrollBar1.Value
Me.Controls(iName1).Visible = True
'------> System
iName2 = itxt2 & a - Me.ScrollBar1.Value
Me.Controls(iName2).Caption = Worksheets("Systems").Cells(1 + a, 13).Text
Me.Controls(iName2).Visible = True
'------> Station
iName3 = itxt3 & a - Me.ScrollBar1.Value
Me.Controls(iName3).Caption = Worksheets("Systems").Cells(1 + a, 15).Text
Me.Controls(iName3).Visible = True
'------> Economy
iName4 = itxt4 & a - Me.ScrollBar1.Value
If Worksheets("Systems").Cells(1 + a, 17).Text = "" Then
Me.Controls(iName4).Caption = "NO DATA"
Else
Me.Controls(iName4).Caption = Worksheets("Systems").Cells(1 + a, 17).Text
End If
Me.Controls(iName4).Visible = True
'------> Distance
iName8 = itxt8 & a - Me.ScrollBar1.Value
Me.Controls(iName8).Caption = Worksheets("Systems").Cells(1 + a, 20).Text & " LY"
Me.Controls(iName8).Visible = True
'------> Discount
iName9 = itxt9 & a - Me.ScrollBar1.Value
If Worksheets("Systems").Cells(1 + a, 21).Value = 999 Or Worksheets("Systems").Cells(1 + a, 21).Value = "" Then
Me.Controls(iName9).Caption = "NO DATA"
Else
Me.Controls(iName9).Caption = Worksheets("Systems").Cells(1 + a, 21).Value & " Cr."
End If
Me.Controls(iName9).Visible = True
'------> Update
iName10 = iTxt10 & a - Me.ScrollBar1.Value
If Worksheets("Systems").Cells(1 + a, 22).Value = 0 Then
Me.Controls(iName10).Caption = "NO DATA"
Else
Me.Controls(iName10).Caption = Worksheets("Systems").Cells(1 + a, 22).Text
End If
Me.Controls(iName10).Visible = True
If Worksheets("Systems").Cells(1 + a, 14).Value = Worksheets("Werte").Cells(13, 2).Value Then
Me.Controls(iName1).BackColor = &H80FF&
Me.Controls(iName2).ForeColor = &H0&
Me.Controls(iName3).ForeColor = &H0&
Me.Controls(iName4).ForeColor = &H0&
Me.Controls(iName8).ForeColor = &H0&
Me.Controls(iName9).ForeColor = &H0&
Me.Controls(iName10).ForeColor = &H0&
Else
Me.Controls(iName1).BackColor = &H102030
Me.Controls(iName2).ForeColor = &HFFFFFF
Me.Controls(iName3).ForeColor = &HFFFFFF
Me.Controls(iName4).ForeColor = &HFFFFFF
Me.Controls(iName8).ForeColor = &HFFFFFF
Me.Controls(iName9).ForeColor = &HFFFFFF
Me.Controls(iName10).ForeColor = &HFFFFFF
End If
Next a

End Sub

Cant recreate the error, please send my your database again. I will check, if there is the problem. SOK = State of Knowledge

Distance · Mar 15, 2015

Tested latest version (used the DB with just 2 stations). Everything seems to be working as intended now.

BennyBoyW · Mar 15, 2015

Distance said:
Tested latest version (used the DB with just 2 stations). Everything seems to be working as intended now.

Did I miss a release ? I'm still stumped on 1.37d

Distance · Mar 15, 2015

BennyBoyW said:
Did I miss a release ? I'm still stumped on 1.37d

Did you install 1.37b and then once it's installed copy over the file from 1.37d?

BennyBoyW · Mar 15, 2015

Yes and I immediately had errors running it

eventure · Mar 15, 2015

BennyBoyW said:
Yes and I immediately had errors running it

What error you got? Did you use the full-version and updated it?

BennyBoyW · Mar 15, 2015

BennyBoyW said:
Hi - sorry - it's me again.
I downloaded the version of the database that you sent the link for.
Then I cleared all TCE off my computer and installed from fresh.
Updated to 1.37c.
I now get an error on startup with a clean database ...... run time error "9" Subscript out of range

This is the error I reported

eventure · Mar 15, 2015

BennyBoyW said:
This is the error I reported

You posted it with V1.37c. V1.37d is actual. Clean database? With no entries, or what? Did you use the database, i send you?

Please post the codeline. Thanks in advance.

toepick · Mar 15, 2015

Runtime error 9

Sub Create_Matrix()

Dim a As Integer, b As Integer, c As Integer, i As Integer, j As Integer, x As Integer

If Matrix_ready = False Then
Worksheets("DB_Goods").Activate
ActiveWorkbook.Worksheets("DB_Goods").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DB_Goods").Sort.SortFields.Add Key:=Range( _
"A2:A1001"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("DB_Goods").Sort
.SetRange Range("A1

1001")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ArGoods = Worksheets("DB_Goods").Range("A2

" & AzGoods + 1)
ArData_Goods = Worksheets("Prices").Range("A2:C" & AzGoods + 1)
ArData_Stations = Worksheets("DB_Stations").Range("A2:Q" & AzRegStations + 1)
ReDim ArData_Buy(1 To AzGoods + 2, 1 To AzRegStations + 2)
ReDim ArData_Sell(1 To AzGoods + 2, 1 To AzRegStations + 2)
ReDim ArData_Stock(1 To AzGoods + 2, 1 To AzRegStations + 2)
'Fill arrays vorbereiten
ArData_Buy(1, 1) = "Station_ID"
ArData_Buy(2, 1) = "Star_ID"
For b = 3 To AzGoods + 2
ArData_Buy(b, 1) = ArData_Goods(b - 2, 1)
ArData_Buy(b, 2) = ArData_Goods(b - 2, 2)
DoEvents
Next b
For c = 3 To AzRegStations + 2
ArData_Buy(1, c) = ArData_Stations(c - 2, 1)
ArData_Buy(2, c) = ArData_Stations(c - 2, 17)
DoEvents
Next c
ArData_Sell = ArData_Buy
ArData_Stock = ArData_Buy
For a = 1 To AzRegStations
DoEvents
For b = 1 To AzGoods
ArData_Buy(b + 2, a + 2) = Ar_DBPrices((a - 1) * AzGoods + b, 4)
ArData_Sell(b + 2, a + 2) = Ar_DBPrices((a - 1) * AzGoods + b, 5)
ArData_Stock(b + 2, a + 2) = Ar_DBPrices((a - 1) * AzGoods + b, 6)
DoEvents
Next b
Next a
Worksheets("DB_Goods").Activate
ActiveWorkbook.Worksheets("DB_Goods").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DB_Goods").Sort.SortFields.Add Key:=Range( _
"B2:B1001"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("DB_Goods").Sort
.SetRange Range("A1

1001")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Panel_DB_Commodity.ComboBox1.RowSource = "DB_Goods!A2

" & AzGoods + 1
Matrix_ready = True
End If

End Sub

Curser over yellow text shows a subscript out of range message

This is after reinstall of 1.37b and replace file for 1.37d

Using old database however so I may just have to start a new db

eventure · Mar 15, 2015

toepick said:
Runtime error 9

Sub Create_Matrix()

Dim a As Integer, b As Integer, c As Integer, i As Integer, j As Integer, x As Integer

If Matrix_ready = False Then
Worksheets("DB_Goods").Activate
ActiveWorkbook.Worksheets("DB_Goods").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DB_Goods").Sort.SortFields.Add Key:=Range( _
"A2:A1001"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("DB_Goods").Sort
.SetRange Range("A11001")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ArGoods = Worksheets("DB_Goods").Range("A2" & AzGoods + 1)
ArData_Goods = Worksheets("Prices").Range("A2:C" & AzGoods + 1)
ArData_Stations = Worksheets("DB_Stations").Range("A2:Q" & AzRegStations + 1)
ReDim ArData_Buy(1 To AzGoods + 2, 1 To AzRegStations + 2)
ReDim ArData_Sell(1 To AzGoods + 2, 1 To AzRegStations + 2)
ReDim ArData_Stock(1 To AzGoods + 2, 1 To AzRegStations + 2)
'Fill arrays vorbereiten
ArData_Buy(1, 1) = "Station_ID"
ArData_Buy(2, 1) = "Star_ID"
For b = 3 To AzGoods + 2
ArData_Buy(b, 1) = ArData_Goods(b - 2, 1)
ArData_Buy(b, 2) = ArData_Goods(b - 2, 2)
DoEvents
Next b
For c = 3 To AzRegStations + 2
ArData_Buy(1, c) = ArData_Stations(c - 2, 1)
ArData_Buy(2, c) = ArData_Stations(c - 2, 17)
DoEvents
Next c
ArData_Sell = ArData_Buy
ArData_Stock = ArData_Buy
For a = 1 To AzRegStations
DoEvents
For b = 1 To AzGoods
ArData_Buy(b + 2, a + 2) = Ar_DBPrices((a - 1) * AzGoods + b, 4)
ArData_Sell(b + 2, a + 2) = Ar_DBPrices((a - 1) * AzGoods + b, 5)
ArData_Stock(b + 2, a + 2) = Ar_DBPrices((a - 1) * AzGoods + b, 6)
DoEvents
Next b
Next a
Worksheets("DB_Goods").Activate
ActiveWorkbook.Worksheets("DB_Goods").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DB_Goods").Sort.SortFields.Add Key:=Range( _
"B2:B1001"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("DB_Goods").Sort
.SetRange Range("A11001")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Panel_DB_Commodity.ComboBox1.RowSource = "DB_Goods!A2" & AzGoods + 1
Matrix_ready = True
End If

End Sub

Curser over yellow text shows a subscript out of range message

This is after reinstall of 1.37b and replace file for 1.37d

Using old database however so I may just have to start a new db

Please use the database with the actual full install or import your old. Please make a backup of your old database before importing .