AverageAmy
New Member
- Joined
- Nov 26, 2011
- Messages
- 25
My company has authorized me to update our spreadsheets and included VBA macros to Excel 2010 from Excel 2000. The short version of my question is: What is the best reference for help in formulating a plan for the VBA conversion process so I can avoid some of the Trial and Error process?
Background information:
I have references that cover general Excel 2010 VBA stuff (such as VBA and Macros: Microsoft Excel 2010) but I was hoping to find something that was specific to the issues one might encounter when converting macros from a previous version to Excel 2010.
I would say I have average to slightly above average skills writing macros in Excel VBA.
Note that there will be a transition period in our office where some people will be using Excel 2000, while others will be using Excel 2010. There is one other person who shares responsibility for writing and editing our Excel VBA macros, and both of us now have Excel 2000 (in XP Virtual mode) and Excel 2010 (in Windows 7) installed on our computers. Other people in the office run the macros but do not write or edit them.
We are a company that runs advanced geotechnical (engineering properties of soil) tests, with a data acquisition system that saves the recorded data to a text file. The number of data columns is variable by test type (but usually 10 or less), and the number of rows depends on the length of test (and can be as many as 60,000 rows). The text file gets imported into an Excel template.
There is an Excel workbook template set up specifically for each of our roughly 25 test types. The user opens the template and is forced to “Save As” with an identifying name. Each workbook has a couple of sheets for data entry (specimen size, mass, project name, etc.). The other 10 to 12 sheets are specific to the type and desired format of results. The results sheets have buttons used to run macros (many subs, 100s to 1000s of lines of code) we have written. There are a number of macros, some of which: import the text file, filter the data (usually to reduce the number of data points based on user input), convert the volts in the text file to engineering units, analyze the data, plot the data, pick maximum points, etc. Sometimes, we also convert these spreadsheets with formulas to spreadsheets without formulas and without the macros included.
I know we will need to alter the code whenever we copy sheets from the older version to the newer version (different number of rows and columns). I’m sure there are numerous other instances of commands that are no longer supported that we will have to write IF statements for based on the version of Excel being run. I’m also thinking that we should consider have the macros in an Add-In instead of held in the individual workbooks to separate the macros from the data (a la LiveLessons Excel VBA and Macros, Lesson 49).
Thank you in advance for your advice on references for this topic.
Background information:
I have references that cover general Excel 2010 VBA stuff (such as VBA and Macros: Microsoft Excel 2010) but I was hoping to find something that was specific to the issues one might encounter when converting macros from a previous version to Excel 2010.
I would say I have average to slightly above average skills writing macros in Excel VBA.
Note that there will be a transition period in our office where some people will be using Excel 2000, while others will be using Excel 2010. There is one other person who shares responsibility for writing and editing our Excel VBA macros, and both of us now have Excel 2000 (in XP Virtual mode) and Excel 2010 (in Windows 7) installed on our computers. Other people in the office run the macros but do not write or edit them.
We are a company that runs advanced geotechnical (engineering properties of soil) tests, with a data acquisition system that saves the recorded data to a text file. The number of data columns is variable by test type (but usually 10 or less), and the number of rows depends on the length of test (and can be as many as 60,000 rows). The text file gets imported into an Excel template.
There is an Excel workbook template set up specifically for each of our roughly 25 test types. The user opens the template and is forced to “Save As” with an identifying name. Each workbook has a couple of sheets for data entry (specimen size, mass, project name, etc.). The other 10 to 12 sheets are specific to the type and desired format of results. The results sheets have buttons used to run macros (many subs, 100s to 1000s of lines of code) we have written. There are a number of macros, some of which: import the text file, filter the data (usually to reduce the number of data points based on user input), convert the volts in the text file to engineering units, analyze the data, plot the data, pick maximum points, etc. Sometimes, we also convert these spreadsheets with formulas to spreadsheets without formulas and without the macros included.
I know we will need to alter the code whenever we copy sheets from the older version to the newer version (different number of rows and columns). I’m sure there are numerous other instances of commands that are no longer supported that we will have to write IF statements for based on the version of Excel being run. I’m also thinking that we should consider have the macros in an Add-In instead of held in the individual workbooks to separate the macros from the data (a la LiveLessons Excel VBA and Macros, Lesson 49).
Thank you in advance for your advice on references for this topic.
