Ten Tips for Coding Excel VBA Macros

Ten commonsense suggestions to make coding Excel VBA faster and easier. These tips are based on Excel 2010 (but they work in nearly all versions) and many were inspired by the OReilly book Excel 2010 - The Missing Manual by Matthew MacDonald. 1 - Always test your macros in a throwaway test spreadsheet, usually a copy of one that its designed to work with. Undo doesnt work with macros, so if you code a macro that folds, spindles, and mutilates your spreadsheet, youre outta luck unless you have followed this tip. 2 - Using shortcut keys can be dangerous because Excel doesn’t warn you if you choose a shortcut key that Excel is already using. If this happens, Excel uses the shortcut key for the macro, not the built-in shortcut key. Think about how surprised your boss will be when he loads your macro and then Ctrl-C adds a random number to half the cells in his spreadsheet. Matthew MacDonald makes this suggestion in Excel 2010 - The Missing Manual. Here are some common key combinations that you should never assign to macro shortcuts because people use them too frequently: CtrlS (Save)CtrlP (Print)CtrlO (Open)CtrlN (New)CtrlX (Exit)CtrlZ (Undo)CtrlY (Redo/Repeat)CtrlC (Copy)CtrlX (Cut)CtrlV (Paste) To avoid problems, always use CtrlShiftletter macro key combinations, because these combinations are much less common than the Ctrlletter shortcut keys. And if you’re in doubt, don’t assign a shortcut key when you create a new, untested macro. 3 - Cant remember Alt-F8 (the default macro shortcut)? Do the names mean nothing to you? Since Excel will make macros in any opened workbook available to every other workbook that’s currently open, the easy way is to build your own macro library with all of your macros in a separate workbook. Open that workbook along with your other spreadsheets. As Matthew puts it, Imagine you’re editing a workbook named SalesReport.xlsx, and you open another workbook named MyMacroCollection.xlsm, which contains a few useful macros. You can use the macros contained in MyMacroCollection.xlsm with SalesReport.xlsx without a hitch. Matthew says this design makes it easy to share and reuse macros across workbooks (and between different people). 4 - And consider adding buttons to link to the macros in the worksheet that contains your macro library. You can arrange the buttons in any functional groupings that make sense to you and add text to the worksheet to explain what they do. Youll never wonder what a cryptically named macro actually does again. 5 - Microsofts new macro security architecture has been improved a lot, but its even more convenient to tell Excel to trust the files in certain folders on your computer (or on other computers). Pick a specific folder on your hard drive as a trusted location. If you open a workbook stored in this location, it’s automatically trusted. 6 - When youre coding a macro, dont try to build cell selection into the macro. Instead, assume that the cells that the macro will use have been pre-selected. Its easy for you to drag the mouse over the cells to select them. Coding a macro that is flexible enough to do the same thing is likely to be full of bugs and hard to program. If you want to program anything, try to figure out how to write validation code to check whether an appropriate selection has been made in the macro instead. 7 - You might think that Excel runs a macro against the workbook that contains the macro code, but this isn’t always true. Excel runs the macro in the active workbook. Thats the workbook that you looked at most recently. As Matthew explains it, If you have two workbooks open and you use the Windows taskbar to switch to the second workbook, and then back to the Visual Basic editor, Excel runs the macro on the second workbook. 8 - Matthew suggests that, For easier macro coding, try to arrange your windows so you can see the Excel window and the Visual Basic editor window at the same time, side-by-side. But Excel wont do it, (Arrange All on the View menu only arranges the Workbooks. Visual Basic is considered a different application window by Excel.) But Windows will. In Vista, close all but the two you want to arrange and right-click the Taskbar; select Show Windows Side by Side. In Windows 7, use the Snap feature. (Search online for Windows 7 features Snap for instructions.) 9 - Matthews top tip: Many programmers find long walks on the beach or guzzling a jug of Mountain Dew a helpful way to clear their heads. And of course, the mother of all VBA tips: 10 - The first thing to try when you cant think of the statements or keywords you need in your program code is to turn on the macro recorder and do a bunch of operations that seem to be similar. Then examine the generated code. It wont always point you to the right thing, but it often does. At a minimum, it will give you a place to start looking. Source MacDonald, Matthew. Excel 2010: The Missing Manual. 1 edition, OReilly Media, July 4, 2010.

Margaret Sanger And The Birth Control Movement Highlighted

Margaret Sanger and the Birth Control Movement highlighted a variety of important issues. These issues include women’s right to make decisions privately versus the right of a community to regulate moral behavior; the ethnic demographics of the American people; the ability of women to control their own physical destinies by limiting family size; and the idea that small families were the way to keep the American dream alive. The debate over birth control spoke to personal and political issues, which poses the question: Was birth control merely a matter of individual choice, or was it about power, wealth, opportunity and similar issues? Birth control was not merely a technique to expand the realm of personal freedom; it grew out of a radical†¦show more content†¦To the question â€Å"Why the Woman Rebel?† Sanger wrote â€Å"Because I believe that deep down in woman’s nature lies slumbering the spirit of revolt† and â€Å"Because I believe that throu gh the efforts of individual revolution will woman’s freedom emerge†. Both highlight how birth control was not a mere technique to personal freedom, but an avenue to power. These quotes emphasize Sanger’s belief that the birth control pill would unleash the spirit of freedom amongst women. She did not argue for the open distribution of contraceptive to promote personal freedom. However, she believed that limitation on family size would free women from the dangers of childbearing and give them the opportunity to become active outside the home. In addition, Document 1 acknowledges birth control’s ability to bring about radical social class change. Sanger includes her belief that women are â€Å"enslaved by the world machine†¦middle-class morality†. Her idea of social change not only involved embracing the liberation of woman, but also the working class. It is believed that the birth control campaign succeeded as it became â€Å"a movement by an d for the middle class†. Birth control provided middle-class women the opportunity to plan families without the stress of balancing growing expenses for a child that was not planned for. In The Woman Rebel Sanger introduces birth control’s larger mission of power and opportunity for women while incorporating the basis of social class.Show MoreRelatedWoman And The New Race2076 Words   |  9 PagesWithin the literature of ‘Woman and the New Race’, Sanger challenges the extent of the use of birth control and questions whom it will benefit aside from women should it become available. According to Sanger, birth control itself, often denounced as a violation of natural law, is nothing more or less than the facilitation of the process of weeding out the unfit, of preventing the birth of defectives or of those who will become defective . For Sanger, â€Å"in compliance with nature’s working plan, we mustRead MoreThe Black Female Body : Past And Present Es say1814 Words   |  8 Pagescounter-hegemonic body politic†, discusses the domination of the black female body and how there is little discussion on how the body has been â€Å"foregrounded as a site of conquest in all efforts of colonization†. According to hooks, black bodies are rarely highlighted in a way that counters the hegemonic representation of being represented as an enslaved female, naked on the auction block, or raped by white masters and enslaved black men (hooks 67). She describes the black body as being portrayed as dirty andRead MoreJudy Chicago Dinner Party Essay6539 Words   |  27 Pagesonly then living woman represented at the table. In contrast to those of the first wing, most of the women of the third wing are familiar figures: Mary Wollstonecraft, Sojourner Truth [4], Susan B. Anthony, Emily Dickinson, Virginia Woolf, and Margaret Sanger, among others. Most of the plate images are organically abstract representations of great women who have been served up and consumed by history. 4 The Apostles were chosen as exemplars who struggled to change and improve the condition of womenRead MoreHISTORICAL CONTEXT OF NURSING5706 Words   |  23 Pagesfamilies to care for the sick and to assist with childbirth Palestinians Under the leadership of Moses, the Hebrews that settled in Palestine developed a system of laws called the Mosaic Code, which was one of the first organized methods of disease control and prevention. It contained public health laws that dictated personal, family, and public hygiene. Examples include: â€Å"do not eat meat of dead animals 3 days old† and â€Å"isolate individuals with communicable diseases.† Little information on nursingRead MoreMidterm Review Essay9272 Words   |  38 Pagespoint | 6.) In the nineteenth century, pools, trusts, and mergers were: | a) | unheard of. | | b) | used only rarely. | | c) | against the law. | | d) | seen as beneficial by consumers. | | e) | ways that manufacturers sought to control the marketplace. | | | 0 / 1 point | 7.) Between 1897 and 1904, a wave of financial mergers led to the creation of the following corporations, all of which dominated major parts of the economy EXCEPT: | a) | U.S. Steel. | | b) | J.P

The Development Of Credit Unions Free Essays

A credit union is a member owned member controlled not-for-profit cooperative financial institution. Credit unions were formed to provide loans to its members at lower rates of interest than would be other wise available. The first credit union was formed by a group of farmers in Belgium, 1848, during a period of severe economic depression. We will write a custom essay sample on The Development Of Credit Unions or any similar topic only for you Order Now Townspeople pulled their money together to provide loans to each other. This cooperative approach helped farmers avoid paying the high rates being charged by â€Å"loan sharks†. These loans enabled a farmer to buy the necessities to plant a crop or to help members buy coal in bulk at lower prices. At the time of their origins the only financial assistance available was the local moneylenders. At the time of the depression people who took out loans were forced to pay expensive interest rates. Credit Unions serve low-income people. A person†s ability (income) to repay is considered more important than the desire to sustain the assets of the credit union. Members are borrowing their own money and that of their peers. By 1900 the first financial cooperative idea had spread from Germany to Canada. Canadas successful efforts influenced two Americans. Pierre Jay the Massachusetts bank commissioner and Edward A. Filene a Boston merchant. These two men helped organized public hearings on the credit union legislative in Massachusetts. This led the establishment of the first Credit Union Act in the United States in 1909. The growth of credit unions across the U.S. was slow. Fewer than 10 states passed credit union laws. 1934 Congress passed the Federal Credit Union Act. This act set the basic structure, which governs credit unions today, examples: Member control is democratically exercised regardless of the number of shares held. Loans, which are primary investment for credit union, are made exclusively to members. A board of directors supervises management By 1935 38 states and the District of Columbia had laws permitting the establishment of credit union and over 3,000 were in existence. In 1970†³s credit unions were battling with the government for federal share insurance and the campaign for national fund to support the community developed credit unions (CDCU). In 1970 congress established the National Credit Union Administration (NCUA) an independent agency responsible for regulating and chartering federal credit unions and NCUA and Congress also established the National Credit Union Share Insurance Fund. This enabled the NCUA to insure the shares of all the federal and state credit unions. In 1978 the Federal Credit Union Act was amended to establish a three-member board, appointed by the president, to head NCUA. Nationally, there are almost 11,000 credit unions with over 73 million members. The Credit Union National Association (CUNA) is the national trade for credit unions. In addition, there are 50 state credit union leagues and leagues for the District of Columbia and Puerto Rico. The credit union movement is growing throughout the world, including third-world countries and Europe where people need assistance with setting up consumer finance systems. There are over 37,000 credit unions worldwide in 87 nations with over 88 million members. How to cite The Development Of Credit Unions, Essay examples