Microsoft Excel

137K

Solutions

38K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Share tech news, updates, or what's on your mind.

Sign up to Post

ArticlesVideosEE: Download statistics on Experts Exchange Articles and Videos - Demo of Enhancements
My 100th publication here at Experts Exchange is an article that presents a program called ArticlesVideosEE, which is able to download the Title, number of Views, number of Endorsements, number of Points, number of Comments, and Date Published for each article and video whose link is in a list. You may read the article here:

How to download number of Views, Endorsements, Points for Experts Exchange Articles and Videos

I also published a five-minute EE video Micro Tutorial with a demonstration of the program:

How to download number of Views, Endorsements, Points for Experts Exchange Articles and Videos--Demo

As you can see from the Title of the article and video, I enhanced the program after publishing the initial version to include columns for the number of Comments and the Date Published. There have also been three other enhancements:

• Ability to specify all the run-time parameters in a configuration file (config.ini) so that (i) the source code does not have to be modified to specify new options/settings and (ii) the Browse dialogs shown in the first video may be avoided.

• Support for a Command Line Interface (CLI) such that a different configuration file (not just config.ini) may be specified on the command line, thereby allowing great flexibility to run ArticlesVideosEE in a command/DOS prompt, a batch file (.bat), the Task Scheduler, programs/scripts, etc. — anywhere that a command line call can be made.

• Addition of a debugging parameter (
1
LVL 29

Administrative Comment

by:Andrew Leniart
Excellent video Joe and thank you or incorporating the enhancements I asked for in your program!

Endorsed!

Regards,

Andrew Leniart
Experts Exchange Senior Editorial Editor
Private Message Me
0
LVL 67

Author Comment

by:Joe Winograd
You're welcome, Andrew, and thanks back at you for your enhancement suggestions...definitely made the program better! Thanks, too, for the Endorsement and Approved accolade...both very much appreciated! Regards, Joe
0
Learn Ruby Fundamentals
LVL 13
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

How to download number of Views, Endorsements, Points for Experts Exchange Articles and Videos--Demo
My 100th publication here at Experts Exchange is an article that presents a program called ArticlesVideosEE, which is able to download the Title, Views, Endorsements, and Points for each article and video whose link is in a list. You may read the article here:

How to download number of Views, Endorsements, Points for Experts Exchange Articles and Videos

This video Micro Tutorial is a supplement to that article, providing a demonstration of the program.

1. Download and install AutoHotkey


If you do not already have AutoHotkey installed, read this EE article:

AutoHotkey - Getting Started

Follow the instructions in that article to download and install AutoHotkey.

2. Download the ArticlesVideosEE program


If you have not yet downloaded the ArticlesVideosEE program, read my EE article:

How to download number of Views, Endorsements, Points for Experts Exchange Articles and Videos

Download the ArticlesVideosEE program attached at the bottom of that article.

3. Create a plain text file with the URLs of your EE articles and videos


Following the instructions in my article, use Notepad (or whatever text editor you prefer) to create a file with the URL list (each article/video URL on a separate line).

4. Run the ArticlesVideosEE program


In Windows/File Explorer (or whatever file manager you use), double-click the ArticlesVideosEE.ahk file to run it.

The above works because a standard installation of AutoHotkey (done in Step #1) associates the .AHK file extension with the AutoHotkey program.

Respond to the Browse For Folder prompt to navigate to the folder where you want the Results file stored.

Respond to the Browse For File
2
LVL 29

Administrative Comment

by:Andrew Leniart
I find this so useful to EE members that I've decided this is worthy of an Approved accolade Joe.

Congratulations and keep up the great work.

Regards,

Andrew Leniart
Experts Exchange Senior Editorial Editor
Private Message Me
0
LVL 67

Author Comment

by:Joe Winograd
Hi Andrew,
Thanks for the kind words, Endorsement, and Approved accolade...all very much appreciated! Regards, Joe
0
Shuffle - 1 - Examine Data in Access that was imported from Excel
Examine data that was imported from Excel. Filter data in Access just like you do in Excel, for just what you want to see.
Turn on a totals row to sum columns. Best-fit columns and change column widths. See how a new AutoNumber value  is automatically assigned, and totals are changed, when a new record is created. Look up a customer code using a quick query.

Here are the other videos in this series:

Shuffle - 2 - Totals Query
https://www.experts-exchange.com/videos/61035/Shuffle-2-Totals-Query.html

Shuffle - 3 - Table Design
https://www.experts-exchange.com/videos/61036/Shuffle-3-Table-Design.html

Shuffle - 4 - Append Query
https://www.experts-exchange.com/videos/61037/Shuffle-4-Append-Query.html

Here is the video that shows how to do the import:

import an Excel spreadsheet into an Access database
https://www.experts-exchange.com/videos/60914/import-an-Excel-spreadsheet-into-an-Access-database.html

1. Open a table in Datasheet view to look at information

2. Click the Filter dropdown at the top of a column to specify criteria for the rows displayed

3. Resize columns to be wider or narrower, double-click to best-fit

4. Add new records at the bottom

5. Look at each column and determine what type of object it is.


      There needs to be a table to describe each type of object.
1
Shuffle - 2 - Totals Query
Data was imported into Access from Excel. In part 1, we examined it, and learned how to do a few things in datasheet view like filtering, sizing columns, and showing totals. To get unique values, we'll make a query and then change it to a Totals query, and use an aggregate function to calculate something. We'll use Group By, Max, and Expression.

Here are the other videos in this series:

Shuffle - 1 - Examine Data (imported from Excel)
https://www.experts-exchange.com/videos/61034/Shuffle-1-Examine-Data-imported-from-Excel.html

Shuffle - 3 - Table Design
https://www.experts-exchange.com/videos/61036/Shuffle-3-Table-Design.html

Shuffle - 4 - Append Query
https://www.experts-exchange.com/videos/61037/Shuffle-4-Append-Query.html

1. To change a query to an aggregate query, click 'Totals'


in the Query Setup group of the Design ribbon tab

2. Group By the key field in the Total row

3. Change Group By to Max for other fields

4. Create an Expression with Count(ID) for the number of records

1
Shuffle - 3 - Table Design
We are going on a deep dive to the foundation of data structure -- what is in the  design view of a table? What else you can do? Use a Unique Index to prevent duplicate values from being added. We discuss Ignore Nulls, the Primary Key, AutoNumber and its properties, create a Calculated field with an Expression to combine data from other fields, tracking fields for when records were added and edited, Field Names, reserved words, and tips for naming fields, and field properties like Data Type, and Size.

Here are the other videos in this series:

Shuffle - 1 - Examine Data (imported from Excel)
https://www.experts-exchange.com/videos/61034/Shuffle-1-Examine-Data-imported-from-Excel.html

Shuffle - 2 - Totals Query
https://www.experts-exchange.com/videos/61035/Shuffle-2-Totals-Query.html

Shuffle - 4 - Append Query
https://www.experts-exchange.com/videos/61037/Shuffle-4-Append-Query.html

Look up Problem names and reserved words in Access, by Allen Browne:
http://allenbrowne.com/AppIssueBadWord.html

1. In table design, you can use an AutoNumber for the primary key


in the Query Setup group of the Design ribbon tab

2. Don't use reserved words in field names


      or spaces or special characters
      except underscore (_) is okay.

3. Start field names with a letter

4. Ensure Data Type and Size are good.

5. Put key fields at the top


      and tracking fields at the bottom.

6. Define an Expression for calculated fields.


      Make text bigger in the Zoom Box (Shift-F2) by clicking the Font button.

7. Use Now() for the Default Value of tracking fields


      for when a record was added and edited.

8. Set a Unique Index to prevent duplicate values.

1
Shuffle - 4 - Append Query
We have a Totals query, and change it to an Append Query, so we can use it to add records. Create an an expression for a calculated field. Get errors (oops!) when we run the append query, but see how to fix them :)

To Summarize, in part 1, we examined data and made a query. In part 2, we changed the query to a Totals query to get unique values and used an aggregate function in an expression. In part 3, we took a deep dive into table design. Now, we have created and run an append query. Records are now in the customers table, and we have two more tables
to put records in before the import from Excel is done. Look for those lessons in the future.

Here are other videos in this series:

Shuffle - 1 - Examine Data (imported from Excel)
https://www.experts-exchange.com/videos/61034/Shuffle-1-Examine-Data-imported-from-Excel.html

Shuffle - 2 - Totals Query
https://www.experts-exchange.com/videos/61035/Shuffle-2-Totals-Query.html

Shuffle - 3 - Table Design
https://www.experts-exchange.com/videos/61036/Shuffle-3-Table-Design.html

1. Open a table in Datasheet view to look at information

2. Click the Filter dropdown at the top of a column to specify criteria for the rows displayed

3. Resize columns to be wider or narrower, double-click to best-fit

4. Add new records at the bottom

5. Look at each column and determine what type of object it is.


      There needs to be a table to describe each type of object.
1
import an Excel spreadsheet into an Access database
Learn how to Import data from Excel into a Microsoft Access database using the wizard. Change data types and modify table design. Add another field and create an update query to fill it out. Also see a neat trick to copy the full path and filename of the active workbook in Excel.

1. To import data from Excel using the wizard, click the Excel icon in the Import & Link group of the EXTERNAL DATA ribbon tab


      (You might have to click the drop-down arrow for more choices to see the Excel icon.)
 

2. Choose the Excel file you want to import from


 

3. Choose the worksheet or range


 

4. Verify that the first row contains the column headings


 

5. change data types


 

6. let Access add a primary key


this will be an AutoNumber field, which is a good idea to have in every table.
 

7. change the table name to be logical, and preface with 'import_'

     the reason to preface the table name is so that when they are listed alphabetically, the import tables will be next to each oher. Once data is in Access, it needs to be normalized.

 

8. modify the table design (to add a field for date)


      In this example, we just added another field. In reality, you would also change Data Type and Size, remove tag-along properties like Format, set text to have Unicode Compression, and more
 

9. make update query to create values in the new field

     construct date using the DateSerial function, with parameters for the numeric year, month, and day

 

10. save the query


      (so you can modify it for another worksheet of data)
1
LVL 50

Expert Comment

by:Subodh Tiwari (Neeraj)
Very helpful tutorial Crystal! 👍
1
LVL 25
thank you, Subodh!
1
How to move data between Excel and Airtable
Airtable is an online database application that's great for small and medium-sized companies. This video shows you how to move data between Airtable and Excel.
0
How to replace or substitute data in Excel
Functions for replacing and substituting data in Microsoft Excel.
0
Create Dropdown menus in Excel 2016
This video takes you through the steps to create dropdown validation menus in an excel spreadsheet.  For the purposes of the video, Microsoft Excel 2016 was used.
2
Learn SQL Server Core 2016
LVL 13
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Wrapper-2, VBA.  Show result of Excel Worksheet Function in Access Query
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access.

The declaration statement defines the function name and its return data type. The values it needs to calculate are specified as parameters, or arguments.

Create an error handler that will exit without the user realizing there might have been a problem, and define a default value of zero for the function return value.

See how to open Excel and refer to it so code in Access can use it. Reuse the Excel application object to make susbsequent processing faster ... so, how do we clean that up?

Use the function in a query! Part 1 shows a query with the calculated column that calls this code:
Wrapper-1-Query-Show-result-of-Excel-Worksheet-Function-in-Access-Query.html

Even though topics may be technical, I do my best to consider that a beginner may be watching, so extra detail is added. Hopefully not so much that more advanced users turn away ... maybe you will comment and let me know your thoughts about this? Thanks!

have an awesome day,
crystal

1. Dimension oExcel as object at top of module

2. Name of function follows the "Function" keyword

3. Enclose function parameters in parentheses

4. Declare data type of return value

5. Set up error handler


For more information on the error handling, here is a short video: Basic-Error-Handling-code-for-VBA-and-Microsoft-Office.html

6. Define default return value

7. Validate parameters -- not done in this example

8. See if Excel is loaded and ready to use


if not, then open Excel in the background and use oExcel to represent it

9. Call the Excel function you want and pass parameters

10. Assign the result to the function return value

0
How To Insert Foreign and Special Characters when in Windows Applications
Enter Foreign and Special Characters

Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~

Use these codes in any Windows application! ... whether it is a Microsoft Office product like Excel, Word, PowerPoint, Access,... or a simple program like NotePad.

Along these lines, I've written a tool to make it easier to input and lookup Unicode when you are in Access, and am making another video on Unicode - so be on the lookout for it.

Please Like, Comment, and Share with your friends ~

have an awesome day,
crystal

1. To insert a special character, make sure Num Lock is on


2. Hold down the ALT key


3. Type the ASCII code on the numeric keypad


4. Release the ALT key

0
How to Add a "Save As" icon to the Quick Access Toolbar
How can you see what you are working on when you want to see it while you to save a copy?

Add a "Save As" icon to the Quick Access Toolbar, or QAT.

That way, when you save a copy of a query, form, report, or other object you are modifying, you can still see what you are doing and can give your object a better name.

While Access is used to demonstrate,  you can do this in other Office products like Word, Excel, and PowerPoint too.

have an awesome day,
crystal

1. click on down arrow at end of QAT to Customize it


2. Choose "More Commands..." from the drop-down menu


3. choose to show 'All Commands'


4. click on the last command in the list of QAT commands on the right


   or whatever command you want to add the command below

5. double-click <Separator> in the left list to add it to the right list below what is selected


6. in the left list, doube-clicm Save As to addit below what is selected (now the separator)


7. click OK to close the Customize the Quick Access Toolbar dialog box


8. Enjoy faster saving!

1
Enumerating Prime Numbers in Excel / VBA
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11).
The larger templates are marginally faster (4 - 6%) but at great expense: 100's or 1000's of lines of code.  

The macro assigned to my "Find Primes" button is listed below:

Option Explicit

Public i As Long
Public j As Long
Public estr As Double    
Public esti As Long              ' used with estr to estimate the size of the primes array
Public iend As Long            ' user input - program will enumerate all prime <= iend
Public sr As Long                 ' square root of iend
Public PrX() As Boolean      ' Templated Possible Primes
Public Primes() As Long      ' # Primes <= iend

Sub DefinePrimes()

With ThisWorkbook.Worksheets("Sheet1")   ' rename the sheet if needed
  iend = .Cells(1, 5)
  If iend < 40 Then
    iend = 40
  End If
 
  estr = 1 / (WorksheetFunction.Ln(iend) - 1.15)
  esti = Int(estr * iend)
ReDim PrX(iend + 30)           ' we always want to have a little extra space
ReDim Primes(esti)               ' we always want to have a little extra space
  sr = Sqr(iend)
  .Cells(1, 4) = "Find Primes <= "
  .Cells(1, 5) = iend
  .Cells(2, 4) = "estr ="
  .Cells(2, 5) = estr
  .Cells(3, 4) = "esti ="
  .Cells(3, 5) = esti
  .Cells(4, 4) = "sr ="
  .Cells(4, 5) = sr
  PrX(2) = True
  PrX(3) = True
 …
0
Binomial Distribution
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac Newton by several years in publishing his work. Actually, it was Jacob's brother Johann Bernoulli (1667-1748), an academic who made sense of Leibniz's notes and was primarily responsible for getting Leibniz's "Calculus" published. In fact, it was Johann that coined the term "Integral" (Leibniz called it sums), and may have even been responsible for the calculus symbology. If the name Bernoulli sounds familiar and you're an engineer, it's probably Johann's son Daniel Bernoulli you recall.

For over 300 years, mathematicians and statisticians have used the Binomial Distribution to group and model real world observations and is the basis of many other statistical distributions.

Here are my spreadsheets if you want to take a look at them:
https://filedb.experts-exchange.com/incoming/2017/03_w13/1153243/N-16-BBD.xlsm
https://filedb.experts-exchange.com/incoming/2017/03_w13/1153244/N-35-BBD.xlsm
https://filedb.experts-exchange.com/incoming/2017/03_w13/1153245/N-40-BBD.xlsm
https://filedb.experts-exchange.com/incoming/2017/03_w13/1153246/N-64-BBD.xlsm
1
How to use the IF function in Excel
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to use Excel's many other decision-making functions.
When to use the IF function

Use the IF function when you want the value of a cell to depend on a condition. The condition is typically the value of another cell or of a value you input. If the condition you choose is a cell value, the cell can be on the same worksheet, a different worksheet, or even a different workbook.

Here's a simple example:
Let's say cell B3 displays a total sale amount. If that amount is greater than $100, you want cell B4 to show a discount rate of 10%. But if B3 is not over $100, you want the discount to be zero.

Here's the syntax of the IF function:
=IF (condition, value if true, value if false)

Note that in the function, there are three arguments. In Excel and many programming languages, the arguments a function gets are separated with commas.

So we write out the function as follows:
=IF(B3>100,10%,0)

Translating Excel into English, this means:
Evaluate the statement that B3>100. Is this true or false?
If it's true, the cell gets a value of 10%.
If it's false, the cell gets a value of 0.

This formula goes inside cell B4. So depending on whether B3 is under or over 100, B4 will adjust to either 0 or 10%.

Now let's put this…
0
LVL 19

Administrative Comment

by:Kyle Santos
Congratulations.  Your video has been Accepted and is now published on Experts Exchange.  Feel free to share this video by selecting the social sharing icons to your left.
0
Using, Creating and Modifying Styles in Microsoft Excel
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custom styles in multiple workbooks.

To follow along with this tutorial, you can use your own files or download the files I use in the video:
www.flisser.com/classfiles/excel-styles.zip
When you want to format cells in Microsoft Excel, you can do it manually, by selecting fonts, font color and size, background colors and borders, or you can do the formatting quickly and automatically using styles. If you used styles in other programs, you’ll be familiar with the concept: a style is a mixture of formatting that you can apply over and over, like paint.

There are two advantages to using styles:

  • Speed. When you have a lot of cells to format, it’s faster to apply a style than to apply all the formatting features individually. And if you need to change a formatting feature of all the cells – like just the color or the font – changing the style definition will immediately update the cell formatting.
  • Consistency. When formatting a lot of cells, it’s easy to make a mistake and select a slightly different color or font size. But when you apply styles, the exact, same formatting gets applied every time.
  • Excel has built-in styles that you can use, and you can also modify them and create your own.
2
LVL 19

Administrative Comment

by:Kyle Santos
Congratulations, Bob!  Your video is Accepted and now published on Experts Exchange.
0
How to tell Microsoft Office that a word is NOT spelled correctly
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When an Office module, such as MS Word, gives us the red squiggly underline signifying a misspelled word, yet it really is spelled correctly (such as a proper noun), we're all familiar with how to add it to the custom dictionary, that is, simply right-click the word and select Add to Dictionary. But suppose we type in a word that does not get the red squiggly underline, that is, Word thinks it is spelled correctly, yet we do not like that spelling, and we want Word (and other Office apps) to flag it as a misspelling. This video explains a Microsoft supported technique for achieving that.

1. Locate the ExcludeDictionary file


Exit all Office apps.

Using Windows/File Explorer (or whatever file manager you prefer), navigate to this file:

c:\Users\<username>\AppData\Roaming\Microsoft\UProof\ExcludeDictionaryEN0409.lex

Of course, <username> is your user name (in my video, it is Joe). The exact name of the file will vary depending on your language and Office version, but it will begin with ExcludeDictionary and have a LEX file extension.

Step1

2. Open the ExcludeDictionary file


Using Notepad (or whatever plain text editor you prefer), open the ExcludeDictionary file, which will be empty the first time you open it. If you do a File>Open, make sure that All Files is selected, since it is a LEX
14

Expert Comment

by:baffledbill
Thank you for the detail and writing it in an understandable way.

Based on your information, I played around with the various English files and my locale and language settings and saw it work as you suggested. I also found that it is safe to simply delete the files, and let Office recreate the appropriate one when it performs a spell check.
0
LVL 67

Author Comment

by:Joe Winograd
You're welcome, Bill. I'm glad to hear that it works for you. And thanks to you for determining that it is safe to delete the LEX file(s) — that Office will re-create the appropriate one when it performs a spell check. I hadn't tried that — very good to know! Regards, Joe
0
Excel Error Handling Part 3 -- Run and Fix Bugs
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel.

Part 1 of this series discussed basic error handling code using VBA.
http://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html

Part 2 went in depth on how the VBA  to copy values to blank cells works, and how to loop.
http://www.experts-exchange.com/videos/1498/Excel-Error-Handling-Part-2-VBA-to-Copy-Values-Down-to-Blank-Cells-in-an-Excel-Column.html

Although helpful, it is not necessary to watch parts 1 and 2 before this lesson.

This lesson runs code to see what it does and then breaks working code so we can explore errors.  We run and fix, debug, compile, use and not use Option Explicit, step through code while it is running, look at the watch window to see values of variables, set and clear breakpoints, stop, continue running, and learn how debugging and error handling work.

01. For a list of macros, press Alt-F8


   When you are in an Excel Workbook, press Alt-F8 for a list of Macros.

02. To go to VBA, press Alt-F11


   When you are in an Excel Workbook, press Alt-F11 to go to the Visual Basic Editor (VBE) where you can write Visual Basic for Applications (VBA).

03. To watch variable values, press Ctrl-W


   When you are in VBA code, press Ctrl-W to open the Watch window and set expressions to watch the value of.  If a variable name is highlighted when Ctrl-W is pressed, it will be filled in the Expression.

04. Stop


   Add a Stop statement to the code to cause the code to stop on that line when it runs.

05. To single-step, press F8

2

Expert Comment

by:Erika Anderson
Thank you!
1
Microsoft Azure 2017
LVL 13
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Fix Code to fill Missing data in Excel. Loop through spreadsheet in VBA.
This Experts Exchange lesson shows how to use VBA to loop through rows in Excel.  In order to sort, filter, and use database features, there needs to be a value in each column for every row. When data arrives with values missing, code to copy values where it is blank can be run.

This lesson shows the manual process to fill blanks, and the VBA code to implement it.

This is Part 2 in a 3-part series to discuss error handling in Excel.

Part 3 of this series will add errors to the code and then show how to fix them.  Part 1 of this Experts Exchange series suggested basic error handling code.

01. Set up the error handler


   At the top of the code for your procedure, the error handler is set up using     On Error GoTo Proc_Err

02. Dimension Variables


   Declare variable names and data types that will be used in this procedure.

03. Initialize Variables


   Initialize the values of variables that will be used in this procedure.

04. Calculate Variables


   Calculate the values of variables that be determined.

05. Give User a Chance to Back Out


   Issue a message box to the user and allow them to stop the process.

06. Determine the Last Row


   Determine the last row on the worksheet that needs to be written to.

07. Loop through each Row of Data and Save or Write Value


   Loop through each row and either save the value that is there, or write the last value saved if a value is needed.

08. Continue Looping until Done


   Continue looping until all values in the specified column are written.

09. Exit Code


   After the procedure code, a line label for the exit code (Proc_Exit: ) is used to signify what happens at the end of the procedure. This is code to gracefully exit.

10. Error Handling Code

0
LVL 25
This is Part 2 in a 3-part series to discuss error handling in Excel.  It can be watched independently of the other videos.  This presentation focuses on automating a manual process to copy values from one row to the next in a column of an Excel spreadsheet.

You will learn how to loop through rows using VBA.  This example copies values to blank cells, but you could do something else in your loop.

Part 3 of this series will add errors to the code and then show how to fix them.  
http://www.experts-exchange.com/videos/1518/Excel-Error-Handling-Part-3-Run-and-Fix-Bugs.html

Part 1 of this series reviewed basic error handling code.
http://www.experts-exchange.com/videos/1478/Excel-Error-Handling-Part-1-Basic-Concepts.html

Attached is the download file for this lesson, and the next: Books_ErrorHandling_02_Code2_CopyDownBlanks.xlsm
Books_ErrorHandling_02_Code2_CopyDo.xlsm
0
Basic Error Handling code for VBA and Microsoft Office
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code.

This lesson, Part 1, is the basics.  Whether you are writing VBA for Excel, Access, Word, or another Microsoft Office application,  basic error handling is the same.

01. Set up the error handler


   At the top of the code for your procedure, the error handler is set up using     On Error GoTo Proc_Err

02. Exit Code


   After whatever your procedure does, a line label for the exit code (such as Proc_Exit: ) is used to signify what happens at the end of the procedure. This can be code to cleanup object variables, or simply code to gracefully exit.

03. Error Handling Code


   After the exit code, a line label for the error handling code (such as Proc_Err: ) is used to begin what happens if there is an error.
Books_START_ErrorHandling_CopyDownB.xlsm
Books_ErrorHandling_CopyDownBlanks_.xlsm
3

Expert Comment

by:chris pike
For someone who is trying to wrap their brain around VB for the first time, this video is starting to shed light on the subject.
Well done video, very helpful.

Thanks so much.
I will definitely look out for more videos from crystal (strive4peace).
0
LVL 25
thank you, Chris and you're welcome  ~ if you have any questions about basic error handling, please post them here.
0
How to install the Office 2016 desktop applications that come with the free trial of Office 365 Home
In a previous video Micro Tutorial here at Experts Exchange, I explained how to get a free, one-month trial of Office 365, which provides the desktop versions of Office 2016. For Windows, this includes Access 2016, Excel 2016, OneNote 2016, Outlook 2016, PowerPoint 2016, Publisher 2016, and Word 2016, as well as Microsoft OneDrive. The previous tutorial ended at the point of downloading the installer for the Office 2016 desktop modules for Windows. This new tutorial goes through the installation process for those applications.

1. Run the downloaded installer


Using Windows/File Explorer (or whatever file manager you prefer), locate the downloaded installer for the Office 2016 apps that are included as part of the Office 365 Home subscription. The name may vary depending on your operating system, but it will look something like this:

Setup.<lots of other characters here>.exe

Run it (usually, via a double-click, but that depends on your file manager and settings) and then click the "Run" button on the "Security Warning" dialog.

step1

2. Accept the User Account Control dialog


Depending on your User Account Control (UAC) settings, you may or may not get the UAC dialog. If you do, click the "Yes" button.

step2

3. Wait until all Office 2016 apps are installed


Although it says, "We'll be done in just a moment", grab a cup of coffee.

step3

4. Check for the Office Tools shortcuts


Check to make sure that the installer created a "Microsoft Office 2016 Tools" program group, with two shortcuts in it.

step4

5. Check for the Office shortcuts


Check to make sure that the installer created shortcuts for all of the Office 2016 apps. It does not
0
How to get a free trial of Office 365 with the Office 2016 desktop applications
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Personal. However, only one of them offers a free trial — Office 365 Home. This Experts Exchange video Micro Tutorial explains how to go through the process of obtaining the free, one-month trial for Office 365 Home, which includes the desktop versions of Office 2016. For Windows, this includes Access 2016, Excel 2016, OneNote 2016, Outlook 2016, PowerPoint 2016, Publisher 2016, and Word 2016, as well as Microsoft OneDrive. In a subsequent EE video Micro Tutorial, I show how to install the downloaded desktop versions of those Office 2016 modules in a Windows 7 system.

1. Visit the website for Office 365 Home


Visit the site with the only Office 365 edition that currently offers a free trial:
https://products.office.com/en-us/compare-microsoft-office-products

Step1

2. Request free trial


Click the "Try for free" button.

Step2aClick the "Try 1-month free" button.

Step2b

3. Sign into your Microsoft account


Enter your email or phone for your Microsoft account, your password, and click the "Sign in" button.

Step3

4. Go through the payment process


Even though it is a free trial, you must provide a payment method and go through the payment process. So be prepared with a credit/debit card or a bank account or PayPal. If you are unwilling to provide a payment method, you cannot get the free trial.

Step4

5. Go through the install process

0
LVL 8

Expert Comment

by:Yashwant Vishwakarma
Thank You for sharing Joe :)
0
LVL 67

Author Comment

by:Joe Winograd
You're welcome, Yashwant. I'm glad you like it! Regards, Joe
0
VLOOKUP Tips and Tricks for Marketers
This Micro Tutorial will demonstrate how marketers working with data will be able to use VLookups because it is the best technique to handle data sets.
8
Three Marketing Examples of the OFFSET Function in Excel
This Micro Tutorial will demonstrate three uses of OFFSET function for marketing data. This includes dynamic validation, creating named ranges for dynamic charts, and formatting Google Analytic data.
1