What Is VBA
This chapter is completely devoid of any hands-on training material. It does, however, contain some essential background information that assists you in becoming an Excel programmer. In other words, this chapter paves the way for everything else that follows and gives you a feel for how Excel programming fits into the overall scheme of the universe.
Read more
COMPUTER BASED MODELING FOR ENGINEERS USING EXCEL AND VBA
Many engineering curriculum around the country are re-evaluating their introductory computer programming requirement. At our university, several departments have been changing from the traditional Java or C++ course to something more applicable to their discipline. This paper will address the development of a joint course that was taught separately in both Industrial and Textile Engineering. Students from both departments were not using their programming knowledge in remaining courses or when they graduated. Furthermore, the introductory Java (C++) class was being taught as a service course to masses of students at one time in our university’s theater. The students were not enthusiastic about the course and certainly not developing the computer modeling skills that we felt were necessary (i.e., given a problem can they develop a method for solving the problem). Therefore, we decided drop the introductory course and develop a new course in Excel and Visual Basic for Applications to better address the needs of our industry and faculty colleagues. This new course was not intended to duplicate the traditional computer science method of presenting programming. Our goal was to educate students to model problems relevant to our disciplines, solve these problems using modeling tools, and then analyze these solutions through decision support (i.e., become “power users” and not programmers). This paper will address the critical development of a series of “InClassLabs” and their impact on student learning and our two curricula. Many of our homework and cases studies come from industrial sponsor data and represent real cases. The paper will discuss the fundamental issues that lead the two faculty members to develop this computer-intensive course. Of special interest is the classroom environment bolstered by the use of in-class teaching assistants and the use of Tablet PCs. Student evaluations are used to provide insight into the teaching strategies employed.
Read more
Implementing Large-Scale Optimization Models in Excel Using VBA
We discuss the importance of spreadsheets for optimization modeling, including a description of their limitations for large-scale problems. We then describe efficient ways to overcome these limits. Our approach makes use of Excel’s standard functionality but augments Excel with its programming language, Visual Basic for Applications (VBA), where necessary. We show how using VBA within Excel to generate and solve large linear programs (LPs) overcomes many of the problems inherent in purely spreadsheet-based models and greatly increases model usability. The techniques described were instrumental in our successful development of a large-scale procurement/distribution LP that resulted in savings of approximately $1,000,000 in the first year, with even greater annual savings expected in the future.
Read more
Talkin Bout Code Generation Getting Animated With SAS and Excel
You’ve probably seen how SAS? can be used to write SAS code. So why not Excel code? Using SAS to generate Excel VBA code can make lighter work of your “Office” chores. It can also help you bridge those brief gaps between compatible releases of SAS, Excel and Windows, allowing you to exploit new MS-Office features while the folks in Cary address what the folks in Redmond have wrought. In this paper, SAS is used to generate the VBA code behind an animated Excel Chart that presents SAS data using some of the cool tricks available with the copy of Excel 2007 that’s sitting on your new Vista Home Basic Edition PC.
Read more
Porting Excel/VBA to Calc/StarBasic
Although OpenOffice 1.1 Calc is able to read Microsoft Excel workbooks, compatibility extends primarily to functionality found in worksheets. Excel workbooks with Visual Basic for Applications (VBA) macros embedded do not function in Calc, even though VBA and StarBasic (SB) are syntactically the same. The reason Excel/VBA workbooks do not work under Calc/SB is due to the differences in the underlying object models for Excel and Calc. The intent of this document is to show, by way of examples, how to port VBA macros accessing Excel objects to the equivalent SB macros accessing Calc objects. This manual is written from the perspective of an experienced Excel/VBA programmer. Hence the reader is assumed to know the VBA language and is familiar with the MS Excel Object Model. This document is not a tutorial on SB.
Read more