Using programming to make sheets go fast

[joshuha]

For any of you that know a bit about programming or have access to Visual Studio, Java, or even Office VB scripting you can use coding to make some of the repetitve stuff in sheets go way way faster.

For example, the following is some C# code I wrote in about 10 minutes that creates all the field in a table for me. This table is replicated 3 times across my sheet so all I had to do was change the strings for the field name (From Order1 to Order2 for example) and the x values where they start.

I realize I could have made the code a bit cleaner by just say putting in a stopword like REPLACE_ME or something in the string and just using a replace. I didn't think of that at first but will probably update my code later to do that.

Also note depending on what you are trying to do (say just Gear1, Gear2, etc) you could use Excel put the static parts of the strings in their own cells, have the changing parts (the Y values, the gear #) in a cell with a formula and then make a final cell that has the combined value and then apply that to all rows. In my example each row has a different name (the different magic "modes") so I put those in a string list and cycle through the list.



string[] strArray = new string[15] {"None","Alt","Att","Con","Def","Heal","Ill","Inf","Mov","Rev","Sum","Tra","Ward","Ench","None"};

string line1_1 = "\t\t\t<numbercontrol name=\"Order3_";

string line1_2 = "_Lvl\">";

string line2_1 = "\t\t\t<bounds rect=\"367,";
int yval = 443-18;
string line2_2 = ",28,20\" />";

string line3 = "\t\t\t<hideonvalue value=\"0\"/>\r\n\t\t\t\t<displaysign />\r\n\t\t\t\t<noreset />";


string line4_1 = "\t\t\t<tabtarget next=\"Order3_";
string line4_2 = "_Mod\" prev=\"Order3_";
string line4_3 = "_Mod\" />";

string line5 = "\t\t\t</numbercontrol>";

string line6_1 = "\t\t\t<numbercontrol name=\"Order3_";

string line6_2 = "_Mod\">";

string line7_1 = "\t\t\t<bounds rect=\"403,";
string line7_2 = ",28,20\" />";

string line8 = "\t\t\t<hideonvalue value=\"0\"/>\r\n\t\t\t\t<displaysign />\r\n\t\t\t\t<noreset />";


string line9_1 = "\t\t\t<tabtarget next=\"Order3_";
string line9_2 = "_Lvl\" prev=\"Order3_";
string line9_3 = "_Lvl\" />";

string line10 = "\t\t\t</numbercontrol>";

string line11_1 = "\t\t\t<numbercontrol name=\"Order3_";

string line11_2 = "_Rtg\">";

string line12_1 = "\t\t\t<bounds rect=\"443,";
string line12_2 = ",28,20\" />";

string line13 = "\t\t\t<hideonvalue value=\"0\"/>\r\n\t\t\t\t<displaysign />\r\n\t\t\t\t<value type=\"readonly\" />";
string line14_1 = "\t\t\t<source name=\"Order3_";
string line14_2 = "_Lvl\" op=\"+\" />";
string line15_1 = "\t\t\t<source name=\"Order3_";
string line15_2 = "_Mod\" op=\"+\" />";
string line16 = "\t\t\t</numbercontrol>";

string masterstring = "";
for (int i = 1; i <= 13; i++)
{
string line1 = line1_1 + strArray[i] + line1_2;
yval = yval+18;
string line2 = line2_1 + yval + line2_2;

string line4 = line4_1 + strArray[i] + line4_2 + strArray[i - 1] + line4_3;
string line6 = line6_1 + strArray[i] + line6_2;
string line7 = line7_1 + yval + line7_2;
string line9 = line9_1 + strArray[i + 1] + line9_2 + strArray[i] + line9_3;
string line11 = line11_1 + strArray[i] + line11_2;
string line12 = line12_1 + yval + line12_2;
string line14 = line14_1 + strArray[i] + line14_2;
string line15 = line15_1 + strArray[i] + line15_2;
masterstring += line1 + "\r\n\t" + line2 + "\r\n\t" + line3 + "\r\n\t" + line4 + "\r\n" + line5 + "\r\n" + line6 + "\r\n\t" + line7 + "\r\n\t" + line8 + "\r\n\t" + line9 + "\r\n" + line10 + "\r\n" + line11 + "\r\n\t" + line12 + "\r\n\t" + line13 + "\r\n\t" + line14 + "\r\n\t" + line15 + "\r\n" + line16 + "\r\n";
textBox1.Text = masterstring;
}