Excel Vba Programming for Dummies Pdf Download

Details About Excel VBA Programming for Dummies Pdf PDF

Book Title: Excel Vba Programming for Dummies
Name Of Author: John Walkenbach
Genre: Computers
Date Of Publish: 12 August 2004
Pages: 433
Language: English

Book Review:

Excel Vba Programming for Dummies if you want to follow along a link to the sample file is in the video description so to illustrate macros imagine this scenario you work in a bank and each week you get a report from your database that provide you updates on consumer loans

it shows things like the amount of the loan the term or length the interest rate the income of the consumer and the purpose for the loan and so on now the problem is when you get the file from the database each week

you've got to make lots of changes to it to get it ready for reporting and those are things we want to automate using a macro so before we can create a macro though we've got to add the Developer tab to the ribbon and Microsoft Excel

the way to do that is to go to the file menu and then at the bottom click on options you should see a window that appears and you want to click customize ribbon and on the right hand side

you'll be able to check developer that adds a tab to the ribbon in Microsoft Excel now the next thing we want to do is record a macro that's going to get everything started so let's give our macro a name

we'll call it loans report and then we'll give it a description so if we pull this file back up later we remember what this macro does can now you should see the stop recording button that means that Excel is now tracking every mouse click you perform the first thing

I want to do is change the font I don't like it the way it is so we're going to go ahead and change it to Calibri now what I want to do is format the header row so that stands out so I'm going to make the text and then I'll give it a blue background with white text and now what

I want to do is take all the columns that are in dollars and convert those to the currency format so that's the loan amount the installment the annual income the total payment and the last payment amount now what

I'm also going to do is delete the home ownership column that's not a column that we use on a regular basis that will go ahead and delete that because we don't need that in our reporting now the next thing

I need to do is to create a new column called debt to income so this is your monthly payment divided by your monthly income it's going to help us determine how risky a borrower might be

so I'm going to take my monthly installment the amount I pay or the consumer pays each month for the loan and then divide that by the annual income divided by 12 so whatever they make on a monthly basis now right now it's showing us as a dollar value we've got to change that

so it's a percentage the debt to income ratio should be a percentage and I'll take that formula drag and drop it all the way down and then I want to sort my data from the highest debt to income ratio to the lowest and now you see that the values are sorted in column C there the next thing

I'd like to do is to change a term in the loan status column rather than it's saying delinquent I'd like it to say charged off so I'll use control F and then click the replace tab and replace and change the language now to make that charged off term stand out even more

I'm going to go to the Home tab and use conditional formatting I want anytime I see charged off for it to appear in red so go to conditional formatting highlight cell rules and then equal to and I'll go ahead and typed in charged off when that appears the final thing

I want to do is take my columns and autofit them so they're the proper width and now what I'm going to do is go ahead and stop recording and take a look at my macro so I click on the macros button and I should see loans report my macro appear and it tells me what it does

I'm gonna click Edit and the code appears so this code captures every one of your mouse clicks and just by looking at it you might be able to tell what some of it does and so look at this line with selection dot font this is the start of a section of code that changed our the font of our table to Calibri

there's another section we changed our header row to bold and there was another section a little below that where we changed certain columns into the currency format now you don't have to make adjustments to the code but one thing that I do suggest is to add comments to it this is an actually code that's gonna run

it's really just for you to remember what the code performs the way to write a comment is to use a single apostrophe and just write out what the next section of code does so in this case change the font of the report and then I'm gonna go down and then after the end width after that section of code

I'm going to create some returns and then in the next comment I'm going to say format the header row and then after the end with a little farther down I'm gonna say format or change the appropriate columns and at the currency for minute if you're just adding notes for yourself

so if you pull the code up or pull the file up a few months from now or years from now you remember what it does now what I've got to do now is save the file but I've actually got to save it as a different file format because it's got a macro inside of it so instead of saving this as an Excel workbook

I have to save it as an Excel macro enabled workbook that allows the macros to be saved in the file now I can close my code window and we'll practice this to make sure it works so we'll go to the week

2 tab and then we'll click the macros button and then we'll run our macro to see if it works there we go it looks like we're good to go hopefully this helped and demonstrate how powerful macros can be even this example we only automated a few tasks but imagine if you have 50 a hundred or 200 tasks to automate it can make your life a lot easier

Books Pdf Download

Search This Blog

Age of Revolutions by Fareed Zakaria PDF Download