Traveller Trade Tool

[Terry Mixon]

Looking at the unmodified (I gave up on modifying it) version 1.3 sheet, I ran into an error, I think. You'll have to tell me.

First, the setup parameters.



Then the purchase numbers for illegal cybernetics.




Then the sales numbers.




The numbers seemed high, so I went to see what I rolled on the trade goods tab.




Unless I'm missing something, the purchase price should have been 212,500 and the sale price 500,000. I'm assuming streetwise mods are already taken into account, so I'm at a loss as to how the final numbers are different than on the trade goods tab.

What am I missing?

 

[myrmidon009]

Yeah, it's pasting the sale price where the sales DM goes and that is because I added a column. I can't make head or tail out of the macro to even know what to experiment with to try and change it to the correct column. I updated my copy to put the DMs back to the values they should have been and made the one column Profit / Ton to match everything else.

To your earlier question - Yes, you are correct. Adding the column threw off the macro. It's just copying values from a specific column, so if a column gets added, that throws off the code. To fix that, I think I can use named ranges instead of columns. The named ranges should stay linked to the correct data even if columns are added.

Unless I'm missing something, the purchase price should have been 212,500 and the sale price 500,000. I'm assuming streetwise mods are already taken into account, so I'm at a loss as to how the final numbers are different than on the trade goods tab.

Regarding your second question, I can't replicate the problem on my end. I think what is happening is that the file has been recalculated since you ran the macro that copies and pastes the data. Any time you save the file, it recalculates the entire workbook. This means that the trade goods on the 'Trade Goods' tab are recalculated. Once that happens, they will no longer match the copied values on the 'Purchases' and 'Sales' tabs. Try running the 'Calculate Available Goods' macro and then checking the values right after that. If there's still a mismatch then, let me know.

I'll work on updating the tool to use named ranges.

 

[Terry Mixon]

To your earlier question - Yes, you are correct. Adding the column threw off the macro. It's just copying values from a specific column, so if a column gets added, that throws off the code. To fix that, I think I can use named ranges instead of columns. The named ranges should stay linked to the correct data even if columns are added.

I appreciate that. It's hard modifying this great tool when I don't have macro skills and I'm unlikely to develop them.

Regarding your second question, I can't replicate the problem on my end. I think what is happening is that the file has been recalculated since you ran the macro that copies and pastes the data. Any time you save the file, it recalculates the entire workbook. This means that the trade goods on the 'Trade Goods' tab are recalculated. Once that happens, they will no longer match the copied values on the 'Purchases' and 'Sales' tabs. Try running the 'Calculate Available Goods' macro and then checking the values right after that. If there's still a mismatch then, let me know.

I'll work on updating the tool to use named ranges.

I'd readily believe I did it with all my messing around. If I see it going forward, I'll mention it.

 

[myrmidon009]

Here is v1.3.1. The major changes are as follows:

• Updated the tool to use named ranges for all cells from which or into which data is copied.
• Updated code to use named ranges instead of column numbers. (Note: this should allow users to add columns without throwing off the macro. Adding rows may cause issues.)
• Added a note on the 'Instructions' tab about how the values on the 'Purchases' and 'Sales' tab may not match, why that may happen, and explaining that such an occurrence is okay. (I hope this explanation makes sense.)

Thanks, as always, for the feedback!

 

[Terry Mixon]

Here is v1.3.1. The major changes are as follows:

• Updated the tool to use named ranges for all cells from which or into which data is copied.
• Updated code to use named ranges instead of column numbers. (Note: this should allow users to add columns without throwing off the macro. Adding rows may cause issues.)
• Added a note on the 'Instructions' tab about how the values on the 'Purchases' and 'Sales' tab may not match, why that may happen, and explaining that such an occurrence is okay. (I hope this explanation makes sense.)

Thanks, as always, for the feedback!

I'll dig into this more tomorrow. Thanks.

 

[Terry Mixon]

The Trade Goods tab still shows this. It even has the same roll numbers. The goods don't have these numbers. Purchase price 212,500 and sale price 350,000.



I had it recalculate them by calculating the purchases. Got this. Listed purchase price is now 150,000 and sale price is 287,500. Nowhere close to what is on the Trade Goods tab. The numbers I see seem more likely than the Trade Goods tab, so I'm not sure what is going on.



Is it using the law level difference as a DM? That is supposed to be for odd jobs rather than the main illegal categories. It would explain the large roll to sell, but not why it doesn't show on the other tabs.

My input information in case it matters.

 

[myrmidon009]

Is it using the law level difference as a DM? That is supposed to be for odd jobs rather than the main illegal categories. It would explain the large roll to sell, but not why it doesn't show on the other tabs.

It's not using the law level difference. I'm not seeing any issues on my end. Could you send me your file and I'll take a look at it?

 

[Terry Mixon]

It's not using the law level difference. I'm not seeing any issues on my end. Could you send me your file and I'll take a look at it?

Here it is. I just verified the numbers are different on the Trade Goods tab than the other locations.

 

[Terry Mixon]

I tried to manually generate some trades and hit a runtime error when I hit the calculate available trades button.

 

[Terry Mixon]

I was crossing from one subsector to another and hit an error with jump distance calculation. No amount of refreshing or hitting buttons corrects it.



That lack causes a follow on error.



I tried turning around to go back but the error is persisting. Now I've broken it completely.

Also, guessing which subsector a world in is a pain. Can that be added to the map?

 

[Terry Mixon]

When I calculate available purchases, the sheet clears out one of the columns I added. I suspect there is a rogue reference that is not tied to a range in there.

Man, I am breaking this thing today.

 

[myrmidon009]

I have a busy week and so may not have time to revise the tool for a little while. In the interim, I figured I'd let you know at least what's going on and provide some working solutions.

• The code failing on the highlighted line (with the range "PurPurPrice"): The "PurPurPrice" range name is a typo in the code. I had tested it on my end. I must have accidentally undone something, or made an update to the range name after I tested it. It should be "PurPrice". If you just change "PurPurPrice" to "PurPrice" on that highlighted line, it should run.
• The problem of your added column being deleted: The macro clears out all the old data in a range of multiple columns. I think it's everything left of the gray cells. If you add anything in that range, it'll get deleted. I will change it to clear out each column individually. That should fix it. In the meantime, moving it to the right of the gray columns should fix it.
• The error with the Jump Distance: I'll have to look at that. Figuring out that equation is actually fairly tricky. There's a lot of logic behind it. I'll have to look into it. If it's being buggy, you might just have to enter it manually till I get it fixed.
• Which subsector: I could add it to the map. There is also a link to travellermap.com, just below where you select the sector, subsector, and system. When you click on that link, it should open the map centered on the purchase system. That should allow you to see what's nearby - subsectors, etc.

I hope that helps.

 

[Terry Mixon]

I have a busy week and so may not have time to revise the tool for a little while. In the interim, I figured I'd let you know at least what's going on and provide some working solutions.

• The code failing on the highlighted line (with the range "PurPurPrice"): The "PurPurPrice" range name is a typo in the code. I had tested it on my end. I must have accidentally undone something, or made an update to the range name after I tested it. It should be "PurPrice". If you just change "PurPurPrice" to "PurPrice" on that highlighted line, it should run.
• The problem of your added column being deleted: The macro clears out all the old data in a range of multiple columns. I think it's everything left of the gray cells. If you add anything in that range, it'll get deleted. I will change it to clear out each column individually. That should fix it. In the meantime, moving it to the right of the gray columns should fix it.
• The error with the Jump Distance: I'll have to look at that. Figuring out that equation is actually fairly tricky. There's a lot of logic behind it. I'll have to look into it. If it's being buggy, you might just have to enter it manually till I get it fixed.
• Which subsector: I could add it to the map. There is also a link to travellermap.com, just below where you select the sector, subsector, and system. When you click on that link, it should open the map centered on the purchase system. That should allow you to see what's nearby - subsectors, etc.

I hope that helps.

Thanks. I’ll manage until you can update it.

Yeah, adding the ability to add columns but only in certain areas is pretty limiting. Having it use separate named ranges is better.

As for the subsector name. Not everyone will be online when they use it, like me, so clicking the link might not be an option. My opinion is that should be added. Also, if you can't find it to add to the drop down, it won't be linked, so chicken and egg.

 

[Terry Mixon]

• The code failing on the highlighted line (with the range "PurPurPrice"): The "PurPurPrice" range name is a typo in the code. I had tested it on my end. I must have accidentally undone something, or made an update to the range name after I tested it. It should be "PurPrice". If you just change "PurPurPrice" to "PurPrice" on that highlighted line, it should run.

I removed the extra Pur and it doesn't crash, but it doesn't populate either.

 

[Terry Mixon]

When moving from the Solomoni Rim sector to Alpha Crucis, it allowed me to select the new sector but then refused to populate the subsector list. I had to remove the cell validation to allow me to type in the new subsector and then it populated the systems. I put the validation back in after the fact, but someone is going to run into this again.

I am just blowing up things left and right as I sail into the unknown.

 

[myrmidon009]

When moving from the Solomoni Rim sector to Alpha Crucis, it allowed me to select the new sector but then refused to populate the subsector list. I had to remove the cell validation to allow me to type in the new subsector and then it populated the systems. I put the validation back in after the fact, but someone is going to run into this again.

I am just blowing up things left and right as I sail into the unknown.

For updating the subsector list or the systems list, you have to click the 'Update Systems Box' button after selecting a new sector or subsector. This brings in the new subsectors (for a new sector) or new systems (for a new subsector). This process is covered in step 2 of how to use the tool on the 'Instructions' tab. Let me know if that's not clear enough.

This is because the workbook is manually calculating things. The 'Update Systems Box' just recalculates the 'Trade Goods' spreadsheet to update the drop-downs.

 

[Terry Mixon]

For updating the subsector list or the systems list, you have to click the 'Update Systems Box' button after selecting a new sector or subsector. This brings in the new subsectors (for a new sector) or new systems (for a new subsector). This process is covered in step 2 of how to use the tool on the 'Instructions' tab. Let me know if that's not clear enough.

This is because the workbook is manually calculating things. The 'Update Systems Box' just recalculates the 'Trade Goods' spreadsheet to update the drop-downs.

I did. It still didn't do it.

 

[myrmidon009]

I removed the extra Pur and it doesn't crash, but it doesn't populate either.

This is a conditional formatting thing. I identified the source. It will be fixed in the next version.

 

[myrmidon009]

Here is v1.4. The major changes are as follows:

• Corrected the look-up for the Sale Subsector drop-down on the 'Input' tab. (This should fix problems with the subsectors not updating.)
• Fixed jump distance calculations. (This should fix problems with the jump distance calculations.) (Calculating distances between hexes is hard! Thanks to the website: https://www.redblobgames.com/grids/hexagons/.)
• Fixed conditional formatting on 'Purchases' tab when selecting manual entry. (This should fix the problem of not being able to enter goods manually after selecting manual entry.)
• Corrected look-up of sale system hex on 'Mapping' tab.
• Added map of subsectors for sale sector on 'Mapping' tab.
• Corrected row calculations when moving left or right from origin system on 'Mapping' tab.
• Updated code to clear data on 'Purchases' tab using named ranges for each column individually, rather than a single named range covering multiple columns. (This should fix problems with the code clearing columns added by the user.)
• Corrected typo of 'PurPrice' range in code. (This should fix the error that pops up when calculating trade goods after selecting manual entry.)

I didn't add the subsector to the map on the 'Input' tab. There is a map of the subsectors for both the purchase and sale sectors on the 'Mapping' tab.

Thanks, as always, for the feedback!

 

[Terry Mixon]

Here is v1.4. The major changes are as follows:

• Corrected the look-up for the Sale Subsector drop-down on the 'Input' tab. (This should fix problems with the subsectors not updating.)
• Fixed jump distance calculations. (This should fix problems with the jump distance calculations.) (Calculating distances between hexes is hard! Thanks to the website: https://www.redblobgames.com/grids/hexagons/.)
• Fixed conditional formatting on 'Purchases' tab when selecting manual entry. (This should fix the problem of not being able to enter goods manually after selecting manual entry.)
• Corrected look-up of sale system hex on 'Mapping' tab.
• Added map of subsectors for sale sector on 'Mapping' tab.
• Corrected row calculations when moving left or right from origin system on 'Mapping' tab.
• Updated code to clear data on 'Purchases' tab using named ranges for each column individually, rather than a single named range covering multiple columns. (This should fix problems with the code clearing columns added by the user.)
• Corrected typo of 'PurPrice' range in code. (This should fix the error that pops up when calculating trade goods after selecting manual entry.)

I didn't add the subsector to the map on the 'Input' tab. There is a map of the subsectors for both the purchase and sale sectors on the 'Mapping' tab.

Thanks, as always, for the feedback!

I’ll look on the mapping tab for it then. I’ll also give it a try later today. Thanks.