|
Hi sloejack,
check your mail :) Quote:
|
Thanks Isskssko, I've updated the DL links in the first post as well as my sig. If any updates come out I'll upload it to the same location.
|
OK, I just dug in and made the changes suggested by AurekStormBlade. The speed increase is incredible, and I checked several of my schematics that I make all the time and got the same results as before the changes. Only thing I noticed was that the rank numbers didn't show up when the schematic was first calculated. I fixed this by adding an application.calculate after the clearing is done on the percentages. the new code looks like this:
Application.Calculation = xlCalculationManual 'Reset Experiment percent to base value if this is a new schematic, not a re-calc. If NoClear = False Then Range("Q1").Value = True Range("O29").Value = Split(Worksheets("Data all").Cells(intTarget, 107).Value, ";")(0) Range("O31").Value = Split(Worksheets("Data all").Cells(intTarget, 107).Value, ";")(1) Range("O33").Value = Split(Worksheets("Data all").Cells(intTarget, 107).Value, ";")(2) Range("O35").Value = Split(Worksheets("Data all").Cells(intTarget, 107).Value, ";")(3) Range("L204").Value = Worksheets("Data all").Cells(intTarget, 120).Value Range("D205").Value = Worksheets("Data all").Cells(intTarget, 121).Value Range("E205").Value = Worksheets("Data all").Cells(intTarget, 122).Value Range("F205").Value = Worksheets("Data all").Cells(intTarget, 123).Value Range("G205").Value = Worksheets("Data all").Cells(intTarget, 124).Value Range("L5").Value = Worksheets("Data all").Cells(intTarget, 125).Value Range("B9:B22").ClearContents Range("B9:B22").Validation.Delete Range("Q1").Value = False End If Application.Calculate I really hope this is working correctly, since it speeds things up a lot. If anyone sees any problems with these changes, please let me know. Thanks, RawlingsSc |
How are we doing?
Is there anyone that can carry the torch of this thread and this tool? I know many of us love the tool, and I will be trying RawlingsSC changes when I get back to home computer. Thank you to Sloejack for all the work you did on this mod, and I hope you can work on it as time allows. If you can't then hopefully another programmer can (I would love to help, but know very little about coding). Ok, just my two cents.
ZG |
Keep in mind these speed changes were for Office 2003 (and maybe Office XP), office 2000 should work just fine without these changes.
Zapato, I have considered taking up doing some work on the Spreadsheet, but have not fully decided yet. |
ok, i downloaded the tool and put the zlib.dll in the windows/system32 directory but when i click get resources it says "an error occured, please check the filename and try again. Then it cant downoad the status file and asks if i want the resources anyway, then it doesnt download the resources, i have tryed everything short of rewriting the download code but i don want to do that, i think it may be that i need to forward a port to my pc but i dont know which, can anyone help me?
p.s. I have used the search function and cant find anything about the error code, 53 |
I've considered throwing my hat into the ring for maintaining this tool as well.
The biggest item I see right now that needs attention is adding a couple more ingredient lines to the schematics to support the Master level ships in ShipWright. Other items that need attention are some rounding issues on Experimental Maximums, and Lube Oils getting overlooked in the Inert PetroChemical slots when they do not have a specific stat that the schematic expects. Example: Melee Weapons use SR primarily, but my Lube Oils are never selected for the Inert Petro slots. These 2 items should be fairly simple fixes (I think). Beyond that, speed issues should be addressed. The calculation switch looks to be way to go there. Try entering resources manually into the spreadsheet and you will see how bad the delay really is... The previous programmers all had plans to incorporate database backend for this (and probably a VC or VB front end), and I think that is a good idea, but that is definitely a long term goal. Translating some of these datasheets into a logical referential database is going to take a lot of time, not to mention the necessary programming changes that will be required. Plus once that happens, it will be harder for the users to tinker with the system themselves to customize it for their tastes and share those modifications with others. |
I will help
I will help in whatever capacity I can. I use the tool every day. I am on a work trip atm, but will be back in about a week. I prefer the excel format, because even though I know little about vba, I know zero about other ways of doing it. The big plus of excel is the ability to customize. For example, I added a sheet in mine to track what my harvestors are harvesting. Any recommendations on how I could start to learn VBA? Reverse engineering the spreadsheet would be interesting.
ZG |
Quote:
Honestly the best way, in the case of this application, is to open the Code page (Tools, Macro, Visual Basic editor) on the Schematic Sheet. Put a break point on the very first line, then select a schematic and "step" thru the code using the F8 key. This is how I am tearing apart the procedure calls. The structure of the application is pretty easy to read and interpret, though it does need more in the way of in-line documentation. When you get to a command you do not understand, highlight it and press F1. This will bring up any Help topics on that command. If that does not work, you can find references at MSDN.Microsoft.com concerning the object model and programming techniques in Excel. My problem on taking this up is a location to host new versions. I *think* I have web space available to me thru my SBCGlobal account, but I have never tried to store anything there. I'll tear thru this tonite and tomorrow and see if I really want to tackle it. The areas I mentioned in the previous message are my primary areas of interest and will be my main priority. |
I've been repetitively getting a horrible virus, and now its causing me to wipe my hard drive... I don't know enough about viruses, and all their methods of getting on your PC... but I decided I'd check out all posibilities. Is it possible for a virus to attach itself to the swgcraft download of this tool... i.e. when I update the latest SWG Craft resources? I can't think of where I might be getting this virus as most of the web sites I go to are SWG - Related... and the few that arn't, I also trust, like SWGCraft.com. Does anyone know of any SWG related websites that have been known for viruses/spywhere, etc?
|
Quote:
Just my opinion, but if the Resource download were the culprit then we'd all be noticing our Virus Scanners going nutso when we pull new data. One way to be sure is to pull the XML file as a direct download to your PC and do a scan on it manually. There is a link off of the Resource page of this site where the XML is available; this is the same file that the spreadsheet pulls when we get the latest resource from SWGCraft. Viruses can be propogated thru just about any port of communication you have on your computer...e-mail, surfing web sites, or thru item you copy/install from CD, diskette, etc. Any more computer users must be vigilant in protecting their own systems with Virus Scanners (I use Norton), Spyware detection and removal tools (I use Spybot), and Ad-Ware blockers (Ad-Aware). Other things you can do are setup a firewall, either hardware or software, and keep track of items that are trying to communicate to/from your computer. It's not a safe playground on the 'Net anymore. You are solely responsible for protecting your own systems using the best tools you can get your hands on. I cannot remember the addresses right now, but if you know which virus infected your system you can go to McAfee's or Symantec's sites and look it up to determine it's primary mode of propogation; this may help you zero in on where it came from. I hope this helps somewhat. I am by no means a Network Security specialist; my company has a 20 person team devoted to this stuff and I do well just keep up with the high level descriptions of what is going on to determine how it impacts applications and systems my team develops/has developed. |
Quote:
Just a quick update: After about 3 hours of tinkering last night; I am starting to see how to add additional ingredient lines to the schematics (adding 2 additional lines). I'm probably about 30% of the way thru making the necessary modifications for that. The hard part in all of this is that both the Schematic and Resource sheets have to be modified with new columns or rows and then I have to find all of the cell range references, both in formulas on the spreadsheets themselves and in the code (which using row number column number coordinates). Some pretty complex code going on, but I am slowly unraveling its mysteries (how do you eat an elephant? one bite at a time.) Once the schematic sheet is working with the new ingredient lines then I have to test *every* other function in this spreadsheet to see what all I have broken in the process. I will probably ask for volunteers at that point. |
Speed up tool this way
Open the code behind the Schematic Worksheet:
At the very top of the code look for this block and make the indicated modifications (between the arrows): Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Integer Dim strVal As String Dim rngIx As Range If Target.Address = Range("C3").Address Then If Range("C3").Value <> "" Then >> Application.Calculation = xlCalculationManual << Call CalcTotalSchematicClear >> Application.Calculation = xlCalculationAutomatic << End If End If Next open the Schematic Functions module, and do a search for Application.Calculation. Replace those lines with 'Application.Calculation Likewise remark out the Application.Calculate commands in the same module. Basically we are remarking out those lines so they will not run anymore. You should see a significant performance improvement. Caveat, this is not fully regression tested yet. While I do not believe there will be any inaccuracies in the data results if we do not do interim calculations, I cannot be 100% positive of that. Experiment with this at you own risk, preferrably on a backup copy of your spreadsheet. |
Well, I made these changes to try it out and it appears to have some problems. The initial load of the schematic is pretty quick. However, the rank numbers aren't there at the start. The real problems seem to occur when trying to change to another rank. Excel even seems to lock up, it takes so long. This was quite a bit faster with the previous changes I had done. I may have done something wrong in the coding, but I don't think so.
There are probably a couple places at least that need the interim calculations. P.S. Don't worry, I tried this out on a copy. 8-) |
I'm seeing the same things in regards to the rank. If you select the same schematic again the ranks show up. It appears that it toggles between having the ranks and not having the ranks each time you select a schematic (same or or different one). I never use the rank functions myself, so it was not on my radar.
Can be solved by turning the Application.Calculate lines back on. One or 2 of them is the key to this issue and it is just a matter of finding the right one(s) that need to be there. I have a pretty good idea on one of them.... Shame....I was getting schematics up in 3 seconds the way I had it. Did not see the problem until I jumped around to a VibroBlade schematic and it told me it was not experimentable. Reloaded the same schematic and it showed me the experimental parts again. Taking a break... getting myself confused jumping between the live version I have (for cell references) and the one I am modifiying. |
I am using the Doctor portion and noticed that the Tatooine Herbivore meat shows up in BLUE on my resource list. If I am correct that says that that specific herbivore meat is needed for something. But this is not true and because of this issue, it does not check other herbivore meats for my WTHT. Is there an easy way to fix that?
Polymer shows up blue as well but that I can deal with. |
Well, I tried putting the Application.Calculate statements back in. It fixed having the ranks there when first loading the schematic, but changing a rank takes an obscene amount of time still. I use the ranks because I keep a lot of schematics around for a long time, and need to select the right resources. If a better resource won't change the final product, then I don't remake my schematic until something else forces me. In fact, I would love to find a way to have it go back in rank farther than just 5. However, I can understand those that never use the ranks.
So far, I'm sticking with those first changes I made. They aren't blindingly fast, but they are quite a bit faster than it was. That said, if you have more things I can try, I'd be more than willing to try them out and see if I can break them. /smile |
Dose,
I just checked with my husband, who is a doc and uses the tool as well. We looked and there is one schematic that uses tat herbivore meat. It is the Poison resist buf - C. In the WTHT sheet, there is a category for generic herbivore meat, and right now on Ahazi it is pointing to Lok as the best. The category for tat herbivore meat is seperate and shows just that one schematic. I hope this helps. |
Quote:
Oh, I am sure we will be breaking this many, *many* times before all is said and done. ;) There is a way to force this to go beyond 5 resources on the rank; I stumbled into that area of the code yesterday afternoon, and it looks like this is being controlled by an array size. If that max size were brought out to be a user input on the Control Panel screen it should work (once I find all of the references). The trade off will be some speed; there is probably some maximum size where the tool will stall out because of the memory requirements. I'll add this to my list of research areas. Addendum, specifically for Rank changes after the initial Schematic is loaded: In the VBA code behind the Schematic page, go to the very bottom of the module: NextCheck2: Set rngIx = Application.Intersect(Target, Range("B9:B22")) If rngIx Is Nothing Then Exit Sub Else If Range("Q1").Value = False Then >> Application.Calculation = xlCalculationManual Call CalcTotalSchematicNoClear >> Application.Calculation = xlCalculationAutomatic End If End If End Sub Couple this with what I did before and remark out all of the other Application.Calculation commands in the SchematicFunctions module. I have reactivated, or added Application.Calculate lines at the following locations: Sub CalcBOM(), last line in module before the End Sub Quote:
CalcTotalSchematic() Quote:
and as last line before End Function in CalcTotalSchematic Quote:
CalcSubSchematics() Quote:
and Quote:
and as last line before End Sub Quote:
I've jumped thru several schematics this way and changed the ranks on them, and so far I have not gotten the "toggling" effect I had before. It is still probably slower than it could be, but it is better than it was originally. |
New version available
Let's see if this link works:
JnFs_SWGCTool_1_66_91.zip Open the SWG_Stuff folder and Select JnFs_SWGCTool_1_66_91.zip from the file list. Made several modifications to speed up the Schematics worksheet, and fixed some misspellings. Also added a Factory and Harvestor calculator that I have been using for nearly a year now; it is based very closely on the calculator used here on SWGCraft website. Please let me know if you cannot get to the link above. This is my first attempt at hosting something from my SBCGlobal account. Make a backup of your resource data and older version of the tool before attempting to use this one. Constructive Feedback welcome :) Edit: Modified the link above, the original link was carrying an expired session id. If this does not work I will go another directon. Strongbow, Intrepid Galaxy MWS/MSW/MArt/MMerch/MRM (2 toons) The ME Cantina |
Quote:
No workie for me. :( - Sent |
Me either..... :(
|
Try the new link I just posted....
|
Ok, I got the latest, the.91 version and it seems to work fine. I haven't found any problems yet, and the speed is much better.
Thanks |
hi
sorry guys, im sorry but, what exactly does this tool do, just a brief summary so i understand what this is all about thnsk :D :p
|
Jaurie,
In a nutshell: The tool will download the currently recorded resource spawns for your Galaxy, from the SWGCraft database in this site. Enter in the resources your character is currently storing (hoarding <G>) and configure the tool with the professions you play, or are interested in. When you choose a schematic, the tool will scan thru your resource inventory and tell you the best resources for that schematic, and it will tell you if you are missing any required resources for that schematic. It will also take the current resource spawns (downloaded above) and compare them to your resource inventory to determine if any of the current spawns are better than what you currently have. It is aimed at helping you improve the quality of you resource inventory and to help you produce the best possible items from your schematics using your resource inventory. Does that help? |
Quote:
Blackiceman, Did you set a directory on the Control Panel worksheet? The directory listed there has to exist; the program does not create it for you automicatically. You can use the browse button there to point to an existing directory or use that to create a directory for you. |
I may have found another bug ...
Last night, I had just entered some new resources into swgcraft ... waited until after the top of the hour for the new .csv to be generated from the site, but one resource was still not showing up (it was one of the JtL steel's). I looked at the csv and noticed the steel was at the very end of the list. I compared this against the "SWGCparsed" tab in the Excel file, and the resource was not anywhere to be found. After a bit of searching, in the GetCurrent() Sub of the DataImportFunctions module, I found a loop that I suspect may not be going far enough. Current Code: For i = 25 To (UBound(str) - 24) Step 24 My New Code: For i = 25 To (UBound(str)) Step 24 Tested this on my copy and it seemed to work. Now .. I just looked at your 1.66.91 ... and saw this change commented out beneath the original code (I don't recall seeing this last night when I made the change at home). Did you (Strongbow), put this commented line in there? Or ... was the '- 24' put in as a fix to an earlier problem? Edit: Hmm, I see this issue is listed as a Known Issue ... is this not the correct way to fix this problem? I will look into this a little more .... |
Quote:
Yup known issue that I have not researched out much yet. The line you see commented out was based on a suggestion someone here made a few weeks ago, but it caused some major problems right after the next update to the SWGCraft database. I'll have to tear apart the process that strips the header information off of the resource data in the downloaded XML file. WOrse comes to worse, I may have to drop in a line code that specifically goes after that line of data and then compare to make sure it is not a duplicate. I should probably start a new thread for this tool.... The copy that I uploaded last night was my personal copy that I have been modifying somewhat. One of the "features" I have partially implemented is a resource pricing calculator that is based on the stats of the resource compared to the theoretical max for that resource type. It is not fully implemented yet, but it will be in the next version. Look on the Resources page, in the columns way off to the right. I'm building it in such a way as to be fairly configurable by the end user (based on my original premise, that is). |
yes m8 thnks alot, so it will tell me all the resources currently on my galaxies so if i was lookin for skev gas it wud tell me?
|
o sorry one more thing, i keep gettin security on the macro's i lowerd all the shit and allowed signing or watever is ther summin im not doing ^^ soz for my utter noobness
|
Quote:
As long as the resources are kept updated here on SWGCraft, yes, it will tell you if Skevon Gas is available. Quote:
On the security, Open the tool and click the enable macros button. If that does not work, go to Tools, Macros, Security and lower the security level to medium or low (only use low if you have a good virus scanner), and click OK. Close and re-open the tool. On Medium setting, you will get the nag screen about enabling macros, so click Enable Macros. On Low setting you will not get the nag screen. Beyond this I am not sure where the problem might lie. Post the actual message you get from the tool and we'll try to assist. |
New Thread sounds like a good idea
Strongbow,
I think a new thread is a good idea since you now appear to be the principal architect behind the new coding with a bunch of us as "beta testers". I would like to say thank you for all the work you have put into keeping the tool alive and evolving! Thank you, ZG |
ooooo i though that it downloaded it straight off the swg server, btw how do i enable macro's?
|
Quote:
If your Security setting in Excel is set to Medium, you should get a dialog box when you open the SWGCTool asking whether to Enable or Disable macros. Click the enable button. If you are set to Low then macros are enabled by default. If you are set to High, you will get a message that your security settings are too high. I'm don't understand what you are asking in the first part of your statement.. |
Quote:
I think I have a solution for the brave do-it-yourselfers :-) In the DataImportFunctions module look for the following code block Quote:
Add the following code after the "next" statement: Quote:
The issue appears to be in how the raw data is broken down into a string array using comma as a separator. Originally there were double quotes and commas around the data, but the double quotes getstripped out as a first step. The very last chunk of useable data (which we do not have that separator, so all of the "junk" at the end of the file gets pulled into that array member too. So here after all of the data that we know is formatted correctly is loaded in, we check the location where the planet name should be and see if we can match it up. If so we go ahead and grab the data we need and process it in. This is a dirty, brute force way to do it and I will look for a more elegant solution later. Probably could put the planet check up in the original For...Next loop and remove that "-24" parameter from the increment counter and get the same result. Right now in Intrepid Galaxy there is not a resource in that last spot that I am concerned with so it is difficult to test. Another way to do this would be to use just the comma as a separator before stripping the double quotes, then strip the quotes off of the front and off of the back to the end of the string in each element. (just thought of this a minute ago). I will give that a go tomorrow. As it sits right now I should be able to have a new version ready in a couple of days. Just a few more functions to test, then let you fine folks break it :D |
Quote:
For whatever it is worth, I decided that this was a logic problem and that it was easier to import all the lines in the file and then delete the ones that were out of bounds age wise. This was the first step to setting the tool up to be more flexible with the age since JtL resources exist longer than their non-JtL siblings, not to mention flora and water ages. the file name is hard coded in this example, but the import is lightning fast and easier to strip data away after than filter before which is what is causing the last resource to be dropped. Code:
|
i was wondering if you are working on the issue that some resources are not added to the wtht sheet, even if they are downloaded and available in the csv file. i am not talking bout the last resource in list issue.
if you are on it, please add this to known bug list, so i stop buggin you about it, hehe besides taht i found an error in the schematic list, blast protection armor layer requires 3 units intrusive ore additionally. the listed ingredients are correct besides the missing ore. |
Is there a way to set this tool to use "2nd Best resources" when I generate certain schematics?
For examole I'd reallyprefer not to use my best copper and use the 400 plus k I have of the 2nd best stock for it. Am I just missing something? Thanks in advance!!! |
aeron,
After you select the schematic, and it populates with the necessary ingredients, look in Column B. You will see each resource line has a number 1. On the ingredient line you are interested in, select that field and you will get a drop-down list number from 1 to 5. Select the number 2 item and the schematic will regenerate using your second best resource for that slot. Note: I am working on a bug right now where Inert Petrochem slots are not picking up my high OQ Lube Oils in ship schematics. From what I have seen so far, this is is probably affecting other schematics as well. It's an issue where the weights are not properly being accounted for if the resource does not have *all* of the stats the schematic is looking at. snuff, Can you give me a an example of this? Is the resource showing up in the SWGParsed list? |
| All times are GMT +1. The time now is 08:35 PM. |
Powered by: vBulletin Version 3.0.8
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.