34: Jon Acampora – ExcelCampus.com – VBA Pro Course

Excel MVP Jon Acampora of Excel Campus.com joins us to discuss his new VBA Pro Course.

The show aired 9:05pm Eastern on Wednesday September 2, 2015.

During the interview, Jon not only discussed his course, but also offered a complimentary copy of the course.  What did you have to do to win?

Go to the comments section of this post and answer the question…

What boring Excel task would you like to automate, or have automated with macros (VBA)?

From there, the Excel TV team selected a winner that was announced in the following episode.

Rick Grantham
Follow Me

Tags

Season 02


You may also like

August 5, 2014

14: Jon Acampora, Excel MVP
  • Dear Jon,

    I would like to ask you how is structure your VBA Pro online training.

    Apart of the theory, would it be possibel to practice the new VBA skills with any challenge/project that you will suggest in your training?

    Quite often after acquiring the theorical knowledge in any online excel training, I realize it is not enough to sort my excel daily tasks. The reason is that online training done in the past just taught the basics that are great, but not enough.

    VBA is really important for my job, so I wonder if it would be possible to practice with any complex challenge/project that you create during your training.

    Thanks in advance

    • Hi Cesar,
      That’s a great question. I believe I take more of a hands on approach to learning VBA. That means I use real world examples to explain the concepts.

      I also include all the Excel files I use in the lessons and training sessions. This allows you to immediately start practicing and implementing the techniques in your own projects.

      In the course you will learn how to do everything from automating a simple process to building an add-in application.

      Here is a link to a free video training series I put together on Macros & VBA. This is an introduction to the course and should give you a better idea of my teaching style.

      Free Training Series on Macros & VBA

      I hope that answers your question. Please let me know if you have any other questions.

      Thanks Cesar!

  • Would you say VBA to Excel is as essential as HTML is to a webpage? I understand the necessity in regards to having made a few macros, however I know I’ve only touched the tip of the iceberg. Thank you in advance!

    • Hi Eszrai,
      That is another great question! It depends on your job, but yes I do think it is as important as HTML (probably JavaScript these days). I think that is something we will talk about tonight. Stay tuned. 🙂

  • I personally have not seen a better structured and well explained VBA course like the one Jon offers. Jon’s course is extra-ordinary. it covers everything from the very basics to the advanced level.
    I deal with VBA everyday and i think if you deal with Microsoft Excel like 750 Millions users, i think learning VBA is very important.

  • What is one thing you have learned in your later years of VBA programming that you really wished you would have known when you first started using VBA?

    • Hi Ryan,
      Great to see you here my friend! Another really good question. Here are a few things that come to mind.
      1. A solid understanding of the Object Model and Properties and Methods.
      2. Loops – These allow us to automate repetitive tasks at lightning speed.
      3. Knowing when NOT to use VBA. This probably just comes with experience, but I try to use it as a last resort. This is highly dependent on the project, but it’s good to look at other ways to solve a problem before spending a lot of time building a VBA solution.

      This is a great question and I would like to learn what other experienced coders have to say. Thanks! 🙂

  • In my job we are continually pulling data from multiple systems and then having to blend formatting and do long vlookup lists to blend the data together. Do you think that recording a macro would be sufficient in blending the data sets into one report or would you use another option? Looking for a streamlined solution and as I have never written any of my own code in VBA I think I truly rely to much on the record macro functionality in Excel. Thanks.

    • Hi John,
      I would look at Power Query for this type of task. PQ will be able to do a lot of the work to pull the data and even do the vlookups (this is called merge in PQ). You might still need a little VBA to refresh the queries, depending on how automated you want to get. Here is an article I wrote on an overview of Power Query.

  • ENTER TO WIN a VBA Pro Course Membership:

    I will be giving away a membership to The VBA Pro Course.

    To enter the contest, leave a comment on this page answering the following question: What boring Excel task would you like to automate, or have automated with macros (VBA)?

    Watch Excel.TV on Tuesday Sept 15th to see if you won! Good Luck!!! 🙂

    • My task is completing a bank reconciliation with source data from third party credit card processors that charge fees make matching items difficult with items posted the general ledger. Also, timing of deposits coming through bank may not match dates on general ledger. Volume of transactions is overwhelming and make for a tedious task. How do we join data sources to rapidly do the reconciliation?

    • Hi Jon,
      Q: What boring Excel task do you want to automate, or have automated with a macro (VBA)?

      A: As a Sr. Financial Analyst for a large non-profit, I have a number of departments for which I prepare highly detailed Excel Budget/Actual/Forecast variance reports that are created using Longview for Excel. For each department, e.g., Talent Strategy, I send the entire file with thirty or so worksheets to the director. Then I send selected worksheets to the managers oF the sub-departments. I added a worksheet with a distribution matrix that allows me to enter the names of the worksheets that should be distributed; what addresses they should be sent to; the name of the recipients, (for the email salutation); CC and BCC addresses; and the text for the email. I then simply select a distribution and click a VBA button, to save the selected sheets including the cover sheet to a separate Excel file and attach it to an email with the parameters provided in the matrix. I can then just click send, or I can review the email and add any comments before sending.

      This has been very helpful not only to helpful facilitate an otherwise menial task, but has allowed me to maintain and standardize my distributions; and avoid sending reports to the wrong recipient.

      • There are many instances in Hyperion (or other systems) and other reports that the only identifier available is the user id (logon id) associated with who entered and who approved an entry. To find out more details on who and how to contact them I developed an Excel add-in that parses out the user id ([email protected]) and then uses Active Directory (AD, LDAP) to get the e-mail address for that user id.

        For any one item (e-mail, user id (logon id), employee id, user name) I can find the other items. Moreover, it can also be used to find that person’s title, department, manager, any level of manager(#) –up to six–or what I refer to as the org chart…every manager for that person, in order, up to the CEO.

        Each function is a standalone function and can be used for a variety of other uses. For example, I use the name, title, department, and manager for budget templates and headcount reporting.

        Depending upon how accurate and in-depth your AD configuration is you can get phone numbers (work, home, cell), work address (street, location: city, state, country), and other pertinent details.

        I also have a variation of it to keep track of members of Active Directory security groups so I know who has access to databases, folders on shared network drives, applications, etc.

    • Q. What boring Excel task would you like to automate, or have automated with macros (VBA)?
      A. I created a macro to loop through all of my sales analysis pivot table report workbooks for each of the sales people in my organization and refresh the data and then email them out to each individual each week.

  • I need to remove hidden question marks from a data file we receive from a vendor before we can upload it into our CRM. If the file is xlsx, the ? are hidden, if we save it off as .csv, we can see the ?’s all over the place, some leading, some trailing, some multiple times. Fine and replace removes everything in the cell, even if we use ~? for the find.

    Thank you.

  • Q. What boring Excel task would you like to automate, or have automated with macros (VBA)?

    A. There are 2 things that I use VBA for more than any others and would love to improve my coding skills:

    1. Read text files (either delimited or fixed length fields) in, manipulate the data, and create spreadsheets;
    2. Open 2 different workbooks (with common key fields) and for every row of a sheet in workbook 1, use VLOOKUP to pull in values from the second workbook, creating a 3rd workbook.

  • I have a metrics spreadsheet with a little VBA built into it. I added an icon to the QAT to make it easier to run. It opens a specific file generated by Access, copies all but the first row, pastes it into my metrics, updates all of the tables and charts, and closes the file with the raw data.

    It worked beautifully until I upgraded to 64-bit Office 2013 and the report can’t be generated in Access. (I’m not the keeper of that file so I’m stuck.) But it’s there waiting for me if/when our Access guru fixes his mess. 😉

  • One of the ways I have used VBA is to assist our corporate real estate staff in building models to analyze future lease expenses. Some of these models involve hundreds of leases, so building them out manually would require week of professional time. With VBA, I have created a system that reduces the time required to hours (most of which is spend reformatting the raw lease data into a usable format).

    One of the things I would like to learn is the creation of new object classes. I have read a number of explanations, but they have all left me befuddled. Based on VBA lessons you have already posted, I believe that you could provide clarity at last! If possible, please include this subject in your upcoming course.

  • Q. What boring Excel task would you like to automate, or have automated with macros (VBA)?
    A. Exporting charts/tables to powepoint presentations

  • Hi John, I will like to automate my monthly reconciliation procedures such that VBA will be able to compare the Sub-Ledger to the General Ledger and spot the difference and itemize the difference in form of a report.

    Thank You.

  • A task that I am struggling to automate is creating a batch of mail merged PDF documents from a data source and assigning a naming convention for each document that is created. For example, I have a data source that mail merges into a proforma template. The template pulls through a unique ID number from the data source. Each proforma is exported as a PDF document and the naming convention that is assigned to each document contains the unique ID. Is this even possible? Currently I have to connect the template to the data source, and then page by page ‘publish’ each one and manually assign the file name. There can be over 200 sometimes!!

  • Q. What boring Excel task would you like to automate, or have automated with macros (VBA)?

    I think most of the users including myself strugle with Splitting and Merging multiple files,
    for example, spliting a single workbook into multiple sheets, based on the unique data in a column. or based on worksheets. and vice verse combining all workbooks into a single workbook or single worksheet.

    thank you Mr. Rick, Mr. Option Explicit and Ms. Szilvia for the Excel TV and many thanks to Jon for his contribution to many excel tips and tricks at excelcampus.com

  • Question: What boring Excel task do you want to automate, or have automated with a macro (VBA)?
    Answer: I will talk about mutliple tasks that I had automated to achieve a broader goal of receiving feedback from our internal as well as external customers.
    (1) Created individual spreadsheets for all the sales personnel who were required to provide contact information for the deals/companies they had pitched to. This contact information was basically used by the Research team to send their prospect/client experience survey links.
    (2) All these emails were sent out from Excel. They included all the contact names for their (sales personnel’s) set of deals. These internal customers (sales personnel) were only required to put “Yes/No” against these contacts and send it back to me. The instructions on how to go about it were mentioned in the automated email as well as the actual excel file (as comments).
    (3) On receiving these files back, I used to put all of them in a particular folder. Consequently, using a VBA macro, the entire content in these files used to get collated in a single sheet of excel (one below the other). I was then just required to filter all those contact names which had “Yes” against them.
    (4) All these emails were personalized, where the subject line/body/attachment included the receiver’s name, for instance: “Yatin: Action Required”.
    (5) Eventually, all this information was shared with the survey analytics team and they ended up using it to send out their survey links using the same macro (the one that I had used for sourcing information from the internal customers).

  • Hi Jon, I just watched your “Writing my 1st Macro” tutorial and am quite impressed at your approach to teaching VBA. Good coverage; you don’t move too fast; you explain the context of the specific task you are addressing; in short, Bravo!

    I am interested in being able to manipulate form and drawing objects using VBA. For example, using the same macro button in a toggle manner, so that when it is clicked, it changes format to indicate that a macro was run and that to use the button again will run a different macro.

  • Q. What boring Excel task would you like to automate, or have automated with macros (VBA)?

    I had to find information from a report imported from a text file. I knew how certain variables were called, but didn’t know where they are. In addition, after finding those variables I had to find other values associated with them, but only within the same entry.

    It was very time consuming if it was done manually, but with the help of VBA code this turned out to be much faster.

  • Great post! Using Excel and VBA I automated the whole forecasting and budgeting process. From blank templates for several entities, sent out by Excel, received back filled out, then populated into one big spreadsheet. Saved a lot of time, but making it was time consuming. Yes please to more training!

    Br,

    E. L.

  • What boring Excel task do you want to automate, or have automated with a macro (VBA)?

    I am a trader, and i work on Excel for analysis (1 excel for every single stock).
    So, i update almost 40-50 stocks (excel) daily. and it always consume time to keep a check on
    which stock is updated or not.
    so, what i want is as soon as i update an excel, the list should get updated in a file. so that its easy to track.

  • Question: What boring Excel task do you want to automate, or have automated with a macro (VBA)?

    I have a report that I have to insert 3 blank rows every time a number in column A changes. I presently do it manually, but it would be nice to have it automated. There are other things on the report that I would like to automate as well. The number of rows is different on each week’s report, so I cannot specify an ending row number in VBA and don’t know how to write VBA to tell it when to stop. I would love to learn how to do this in VBA.

  • Q. What boring Excel task would you like to automate, or have automated with macros (VBA)?

    Aggregate data from hundreds of workbooks and distill down into usable worksheet reports; then send out 350 personalized emails with attached respective worksheet reports. VBA has been a HUGE time-saver!

  • Question: What boring Excel task do you want to automate, or have automated with a macro (VBA)?
    Every month I have to download our Sales Totals from approximately 150 salespeople. I do the same for our Accounts Receivable report. I pull this information from our SAP database. Once this information is in Excel, I create reports using Pivot Tables. I would love to learn how to do this with a macro(VBA)!

  • I want to automate copying headings from the first file in a folder into a new workbook. Then copying several ranges from each of the workbooks in the same folder and pasting each range into a that new workbook. The ranges from the first file are pasted into C4 and C9 in the new workbook and each of the ranges from the other files is pasted in the next column to the right. Every month the folder and file names are different but the incoming data is in the same ranges. I can get most of it to work except pasting into the next column.

  • I want to extra values from a pivot table. I will be pulling data from ameyo software i.e., call center call details. I put a pivot table for that raw data and every hour I will be changing the raw hence numbers will be changing. I want to extract those numbers to a template. It is a difficult task and I could not done that through vba. If you want I can send that file to you.

  • I need to convert and combine about 50 tables setup for simple data entry (rows = multiple metrics, columns = =7 years of months) into a single table so that I can presents the info dashboard style. I want to keep the data entry tables and want to do the conversion of new data monthly.

  • The big discussion is what should I use power query for vs VBA(old way). My opinion is whatever is faster and easier to develop.

  • Actually I enjoy with Excel feel like I am playing a game on excel that’s why The excel task never would boar myself. Of course I have already automate task with Excel.

  • All I do is write VBA to automate tasks for owners. I enjoy developing daily reports that show orders vs inventory and develop cost predictions for each order based on inventory (and pass history).

  • Jon

    I have been watching your videos on the free course, and wanted to ask if I can download the same so I can refer back to them at a later date, when trying to write some VBA code for myself. This is most useful as it will be a reminder of what to do!!

  • What boring Excel task would you like to automate, or have automated with macros ?
    I would like to figure out a way to automate the process of updating 1 spreadsheet when new data is updated in another spreadsheet. I sure that there is a process but I have not learned it yet.

  • : What boring Excel task do you want to automate, or have automated with a macro (VBA)?

    I’d like to automate teh formating of a file conversion and subsequent pivot table creation

  • What boring Excel task do you want to automate, or have automated with a macro (VBA)?

    hi jon very ineresting interview ,i will like to automate how to ejecute a vlookup on a close workbook.

    i use vlookup every day to complete my reports , a way to do it with out open the data base workbook will be grate.

    thanks jon

  • Hi Jon,

    I have watched your first three videos and your VBA pro course seems great. So here is a task that I had to do for a long time but after digging a little bit into VBA, I managed to automate the task saving at least 30 minutes a day.
    I have different tables from a POS stored into an SQL db, I am supposed to get the payments from the sales of all the outlets (around 10) as well as the categories of products and make sure everything matches. I also have to isolate one specific kind of payment, get the check number and find the categories of product for each check. Of course, each outlet must be separated. I then have to get all the informations and transfer them onto another file for our accounting department. With the macro I created, I need to open the “accounting” file, open the file containing the macro, refresh it and type the day I want. Excel, does all the sums (OK that does not include macros because there are a lot of sumifs) but also isolates my specific payments, the corresponding checks with the sales categories for each outlet. Once it is done, excel copies pastes the values onto the “accounting” file, switch back to my file at the exact cell where I type the next date.

  • I have a boring (don’t tell my boss) excel spread sheet that tracks books coming into our remote drop box from different libraries. I need the sheet to track each library, track each county the library is in, how many books per drop, then tally this monthly, quarterly, yearly.

  • I use VBA to automate the generation of a half dozen Excel reports before I arrive to work each day.

    Hope I win … thanks.

    Charles.

  • Q: What boring Excel task would you like to automate, or have automated with macros ?

    I enjoyed reading all the comments here. I got some ideas from them, too. The boring tasks I automate with macros include formatting my spreadsheets (sizing columns, formatting numbers and dates, and doing alternate row coloring), changing formats (text to number, or number to text), removing non-numeric characters from figure columns that have to be summed. I have also used macros in conjunction with a Windows automation scripting language called AutoHotKey (AHK) to simulate keystrokes in an old Unix database program, extract data to csv, pull it into Excel and format it as a price catalog and send it to a laser printer to print 10 copies once a week.

    But I would like to learn VBA coding properly instead of just using or tweaking snippets I find on the internet. I want to learn VBA across the spectrum of MS Office apps to automate and obtain more integration. Especially, I want to build solutions for my HR job that will integrate MS Access and MS Excel.

  • Hi Jon

    I often use the “Report filter pages” command to create multiple pivots. I noticed that even if the original pivot has a chart – the others that are created do not come with the chart. Would love to automate the process of replicating the original chart (formatting and style etc) for each new pivot. I put this question on the Mr Excel forum in 2012 and has no replies!

    Thanks

  • Hi,
    I have a spreadsheet that tracks the progress of our reports from start to finish. When each report is complete I enter the date of completion and then change the “status” column to complete (actually, I just delete the current status and leave it blank), and also bold the report name so that I can recognize the completed reports easily without scrolling. I would love to be able to automate the last two steps by entering the completion date.

  • Hi Jon,
    Q: What boring Excel task do you want to automate, or have automated with a macro (VBA)?
    A: I have to create a report from 4 different sources, lookup the employee ID information, summarize it and finally make some adjustments in the GL. The process is very manual and tedious, so I recently I created a macro that automates the whole process. From 4 hours, now I just spend 10 minutes on it.
    VBA is really awesome!
    Thanks,
    Pablo

  • My clients send what I will classify as bad data on a regular basis. One of the most tedious is when I get a worksheet with an entire address (Full name, Street, City, State, Zip) in one cell. All of our databases are setup with each of these items as a separate field. To make this even more irritating, the records don’t follow a uniform format and often include extra spaces and/or non-printing characters. So a simple =Clean(Trim()) won’t always work and text to columns can make an even bigger mess.

    I have a couple of formulas that help separate the names and zip Codes. I also have a USPS database that I use to look up the ZIP codes to get the City and State. The street address is still a mess and takes time to clean up. If there is any way to use VBA to help automate or facilitate these steps, I’d love to learn!

  • Hi Jon,
    Q: What boring Excel task do you want to automate, or have automated with a macro (VBA)?
    A: Every day I must send email by different people (data in their area of responsibility)…
    I need a macros)

  • Hi Jon,
    Q: What boring Excel task do you want to automate, or have automated with a macro (VBA)?
    A: I don’t really know what task I want to automate because I am just learning VBA and I am trying to figure out all the things I can do with it.

  • Hi Jon,
    Q: What boring Excel task do you want to automate, or have automated with a macro (VBA)?
    A: I would like to have good error log solution for all Excel errors that arise when Excel starts crashing down.

  • Hi Jon,
    Q: What boring Excel task do you want to automate, or have automated with a macro (VBA)?
    A: Task in Excel is always exciting when it is being automated. With the help of your website, I have learned a great deal each day in simplifying my work as a number-cruncher. One automation that made me proud was creating the report from the bank statements that I download from the internet in just 5, 4, 3, 2, 1, tada! Simply, a fulfillment it was! I look forward to making more macros!

  • Hi Jon,

    I would like to learn VBA and writing macros to work in a faster more efficient way – even performing simple tasks like updating or formatting spreadsheets and then taking my knowledge to the next level.

    Thank you.

    Arlene 🙂

  • One of the many boring task that I have implemented in Excel is automating the creation of test data when testing a new Excel application,

    I have written a macro that will populate the selected cells using the RAND function multiplied by 1000. I have assign this macro to the right mouse button.

  • In 2000, I did the whole City of Charlotte budget process in Excel with VBA code.

    The city had 26 business units (KBUs) and each business unit had its own folder. Each department in a business unit had a workbook. Police had 67 workbooks. I build 365 workbooks and loaded some 9500 sheets with data from the accounting department. This amounted to some 40 columns of data. Workbooks were build in VBA, loaded in VBA and had custom tool bars. There were approximately 70 users of these workbooks and most were unskilled in Excel. Each sheet in the workbook was for a different expense line item. The city had some 200 different line item expenses. Of course no workbook had that many line items,but several had over 65 sheets.

    Collections of data from these workbooks was in a summary workbook in each of the 26 KBUs’ folders. Data was copied from each workbook and loaded in the summary KBU’s workbook and a PivotTable was used to summaries the data for the KBUs. These summary workbooks were all build in VBA and data collect using VBA.

    One last VBA workbook gather the summary data in each KBU’s summary workbook. Thus, the final workbook had some 9500 records that were gathered from each of the 365 individual Excel workbooks.

    Building 365 workbooks and loading the their accounting data using VBA took about 25 minutes Collecting the data in 26 KBUs’ summary workbooks took about 13 minutes. This included the top workbook that collected the summary data from the KBUs’ summary workbooks

    Those were the days!

  • Q: What boring Excel task would you like to automate, or have automated with macros (VBA)?
    A: I have created a roster system in excel (which I am pretty pleased with). But then every six weeks when the new roster comes out there are a whole bunch of tasks needed to be done to the “Master” to create a ‘fixed’ distributable copy. Things like removing all the value pasting all the data drawn from look ups, getting rid of the macros and protections, removing all the no longer necessary sheets and calculation cells…. you get the idea. I just KNOW VBA could automate this into a one click process. But I don’t have the skills. If it could be automated then the staff could do this themselves – one less thing for me to do!!!!!!

  • Q: What boring Excel task do you want to automate, or have automated with a macro (VBA)?

    A: Where to begin? My first VBA project was automating a worksheet that used Solver to calculate a dozen materials properties. Each of these calculations actually required multiple runs through Solver, iterating until two opposing Solver routines converged. Kind of Uber-Solver. Doing it manually took half a day, and the VBA solution took around a minute.

    Then I automated cleaning up of charts, the old Excel 97 formats with dull gray background and mixed neon and pastel data colors.

    I automated exporting of multiple charts into PowerPoint as pictures, and other report-related tasks.

    I automated the ultra-tedious telnet communication with an ancient mainframe data system.

    I automated plotting and analysis of data from forging presses in our metalworking factory.

    I’ve automated creation of myriad custom chart types: waterfall, boxplots, histograms, paretos, marimekkos, and many more.

    I’ve automated interfaces for easily changing and updating chart source data and formatting.

    And much much more. I’ve automated tasks to save myself from boredom, then as I advanced I applied my automation skills to save clients time and effort in their projects, and now I have developed commercial Excel add-ins that make use of VBA. All starting from simple recorded macros and that first ever “Hello World”.

    I see from other comments that people have applied VBA to so many different and so boring tasks. What a powerful thing VBA is.

  • Hi Jon,
    Q: What boring Excel task do you want to automate, or have automated with a macro (VBA)?
    A: i wonder if it can be done…
    A macro to convert formulas into value in the active worksheet (this is easy)
    Another macro to restore all the formula. This is a bit different from undo as the value of precedent cells may have changed

  • Q.What boring Excel task would you like to automate, or have automated with macros (VBA)?
    A. Created a macro to replace cube formulas for our reports created from a data cube in a workbook with hard coded values for external users.

  • Q. What boring Excel task would you like to automate, or have automated with macros (VBA)?
    A. Every month I need to download the accounting data from our Ellucian system. It downloads with lots of extra spaces (16 to be precise) and characters. I did a very basic macro that cleans some of it up, but I don’t have enough VBA knowledge to modify the macro to make it operate on data that changes size every month.

  • I have a boring Excel task do I’d like to automate, or have automated with a macro (VBA) (if possible!).

    As a Compensation Analyst, I review market surveys against employee dashboards almost daily for ad hoc analysis. There are several sources I sense check to make sure my roles and markets are aligning appropriately, as data in the dashboard may have red flags for errors, or data robustness and integrity will be questioned often. I’d like to be able to drop in several reports from different sources and have analysis initiated where I do manual review and entry, but keep sensitivity to the qualitative nature of people metrics and survey interpretation.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Free 24 Excel Tips Guide

    Discover the secrets to Excel efficiency with our free guide: '24 Proven Microsoft Excel Tips to Help You Save Time'

    A humble gift from us to you, to make your Excel journey smoother and more productive. Grab your copy now and start mastering Excel like never before!

    >