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

spreadsheet with rules
This article addresses the need, sometimes extreme, for a legend that may not be easy to create, depending on the features of conditional formatting you have used. Almost all spreadsheets need some sort of legend.
0
Fundamentals of JavaScript
LVL 13
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Using a Vlookup with one of Excel's random number functions to create randomized columns of data from pre-defined lists.
1
ArticlesVideosEE
This is my 100th publication at EE — 56 articles and 44 videos. To mark the occasion, I wrote a program to download the Title, Views, Endorsements, and Points for the specified URLs of articles and videos. Based on feedback, I enhanced the program to include number of Comments and Date Published.
3
LVL 29

Expert Comment

by:Andrew Leniart
Ran this a few times and works exceptionally well. Great stuff Joe - very helpful indeed.

One feature suggestion for a possible future revision - include a column for "Date Published" - Possible? :)

Thanks for your work Joe - fantastic stuff.

Endorsed!

Edit: Oh and while I have your ear, how about a column for "Number of comments" too?  Sorry, you may have opened up a huge can of worms here. You know how I can get when I like one of your creations lol ;)
0
LVL 67

Author Comment

by:Joe Winograd
Hi Andrew,

> works exceptionally well

Glad to hear it!

> Great stuff Joe - very helpful indeed.

Thanks for the positive feedback...I appreciate it.

> include a column for "Date Published"
> a column for "Number of comments"

Great ideas! Both already implemented. Will upload the enhanced version (and some updates to the article) as soon as my high-speed Internet service returns (connected via my mobile phone now...anything beyond these text comments would be painful).

> Endorsed!

Thanks for that! Regards, Joe
0
Power Tools in Excel
Learn about Power Excel add-ons and how to use them. Power Pivot. Get and transform (Power Query).Power View.3D Map (Power Map).Extra Power BI.Power summary video (Spanish)
0
LVL 2

Author Comment

by:►Yolanda Cuesta Excel Contabilidad TIC
Hi Andrew, thanks a lot for your help, I think that the article is much better now.
Best,
Yolanda Cuesta
0
Rename the worksheets
Excel is a great tool for business and we use it very often in our daily jobs. Sometimes we have been inherited a spreadsheet with a lot of data in which it makes us difficult to search such info. In this case, we need to think of a way to find or tag our data.
0
Summary of Bill Paying Spreadsheet
How to use a bill paying spreadsheet created by yours truly. Also a little background. The download is available towards the bottom of the article and is named Pay-Bills.xltm. Feel free to use, or, as you wish. Provided as is.
2
Bookkeeping with Excel
This is for people with spreadsheet skill, but not necessarily yet book-keeping skills, which with this “starter template” will give you the lists headings and a few fairly simple calculations you need to get going. 
0
Dropdown lists in Excel
Do you want to increase the data integrity of your Excel worksheets? Do you input something repeatedly? Would it be handy to pick the item from a predetermined (by you) list? READ ON!
2
LVL 61

Expert Comment

by:Martin Liss
I don't mean to be argumentative but can you explain your reasoning?
0
LVL 33

Author Comment

by:Thomas Zucker-Scharff
Sure. The purpose of a database is to enter data once and have it displayed in many different ways and also relate that data to other areas of the relationship db (using tables and queries).  When you use a dropdown list in excel, you are basically using it like a simple database.   Di why not use a db like access? It is as easy to use as excel.
1
Enroll in these five web development courses: RESTful Services: Data, JAX-RS and Jersey, RESTful Services: Fundamentals, Selenium Fundamentals, Bootstrap 3.1: Fundamentals, and Perl Fundamentals. Then learn to perform data analysis in Microsoft Excel to measure your success.
1
LVL 7

Administrative Comment

by:Stacy Richard
Published for Nicole
1
Google Maps Distance
Many applications need to compute the road distance between two addresses. Numerous websites perform this function, but to build this feature into your application, you must use an API to call these features via the internet. This article discusses how I did so by using Google Maps API and VBA.
1
Become a Certified Penetration Testing Engineer
LVL 13
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Charts are easy to insert manually but sometimes we need to automate the chart insertion and most of the people find it difficult to control the chart creation and its properties via VBA In this article, we will learn about how we can use VBA to insert a Chart and manage its appearance.
10
LVL 1

Expert Comment

by:agwalsh
I could see how this could be really useful if you were regularly entering charts - would save a lot of time :-)
1
LVL 4

Expert Comment

by:Skylar-Barlow
Great work!
1
Convert Excel Contacts To Outlook Easily
Want to troubleshoot the issue of importing contacts from Excel to Outlook error No Named Ranges?? Resolve this problem and get the error-free solution to resolve the “No Named Ranges” error in a hassle-free way.
0
Are you encountering the error message “The workbook cannot be opened or repaired by Microsoft Excel because it is corrupted” when trying to open the MS Excel workbook, and you are not able to open the workbook? If your answer to this question is a Yes, know all about this error & its solutions.
1
Export CSV to VCF or vCard
Do you know how to convert CSV to multiple VCF formats? Are you searching for a hassle-free solution to import .csv file contacts into .vcf files? Get an optimum solution here to export Excel or CSV files to vCard or VCF formats! Read more to know the solution in detail.
1
It is not that simple from VBA to display a compressed help file as it should be. API calls and a few tricks are needed. This demo and full code will show you how.
0
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
0
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
0
Debits & Credits. Why oh why do they exist?
Here is why.
0
Multi-Purpose Knife
This article presents several of my favorite code snippets.
6
Exploring ASP.NET Core: Fundamentals
LVL 13
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Bookkeeping & Excel: Debits & Credits and the 5 Column Cashbook -  also for Pivot Table reporting
Debits & Credits have been the foundation of financial record keeping since 1494 - over 500 years. Excel is a brilliant tool for leveraging this ancient power - not least with Pivot Tables, sorting and filtering. This article seeks by illustration to offer a practical demonstration of the theory.
0
Excel allows various different methods to link Excel files to each other. This includes relative paths, mapped drives (or the local drive) and UNC paths. UNC paths are the least robust of the three.
2

Expert Comment

by:Fanie Prinsloo
Confirmed.  The process works like a dream and saved us days of work trying to update the links manually within the files
1
LVL 55

Author Comment

by:Shaun Vermaak
Thank you Fanie
0
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
0
My image

As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to do that.


An example

Let’s say I wanted to determine if a variable (in this case a String) contains certain values, so I might write something like this.

If MyVar = "This" Or MyVar = "is" Or MyVar = "a" Or MyVar = "test" Then
                                            MsgBox "I'm interested in those words"
                                        End If

At one time in my programming career when I would write a line like the first one, I’d say to myself “I wish that I didn’t need to repeat the “MyVar =” and could write the following instead.

If MyVar = "This" Or "is" Or "a" Or "test" Then

Experience, however, taught me that I’d get a syntax error. Fortunately I eventually discovered the Select Case statement and I was able to write this instead which is similar.

Select Case MyVar
                                            Case "This", "is", "a", "test"
                                                MsgBox "I'm interested in those words"
                                        End Select

The rest of this article explains how to use the powerful Select Case statement.


The basic Select Case syntax

Select Case < The Expression >
                                            Case SomeCondition
                                                ‘ Do something
                                            Case Else
                                                ‘ Do the default action
                                        End Select

Notes:

  • For the purposes of this article, “The Expression” is any valid expression that resolves to a string, a numeric value, or a Boolean value. In the case of Excel that includes cell values.  
  • When Visual Basic encounters a Select Case statement, it evaluates the Cases top to bottom, and once it finds that the Case is True it executes the code in that Case and ignores the rest.
  • There can be multiple Case statements
  • The Else case is optional


An expanded version of our Select Case above might look like this.

Select Case MyVar
                                            Case "This", "is"
                                                MsgBox "It’s one of the first two words"
                                            Case "a", "test"
                                                MsgBox "It’s one of the last two words" 
                                            Case Else
                                                MsgBox “Word not found”
                                        End Select


In Case you want to know more

Here are some other Case statements that illustrate the power of Select Case.

Case 1 to 7, 19, > 25

Will be true if the expression resolves to a number between 1 to 7 inclusive, 19, or any number greater than 25

Case Is = "blah"

Same as Case "Blah"

Case "alpha" To "omega"

Visual Basic can spell and any value that is alphabetically between the two (like “great”)  will be true. Be warned however that “Great” is not the same as “great” and the former will not be true. One way to avoid that is to put Option Compare Text at the top of your code module. When you do that all text comparisons will non-case sensitive. Another way is this which compares the upper case versions of the text.

Select Case UCase(Range("B2"))
                                            Case "ALPHA" To "OMEGA"

You can also nest Select Case statements like this example.

Select Case Range("B2")
                                            Case "alpha" To "omega"
                                                Select Case Range("B2")
                                                    Case "coding", "is", "fun"
                                                        MsgBox "I agree"
                                                End Select
                                        End Select

The MsgBox will be displayed if it contains one of those three words.


And what is perhaps my favorite

Remember where I said that “The Expression” could be anything that resolves to a string, a numeric value, or a Boolean value? Well, True (or False) is a Boolean value and so you can do this:

Select Case True
                                            Case Range("B1") = 5
                                                ' Do something
                                            Case Range("C4") = "Done"
                                                ' Do something else
                                            Case Else
                                                ' Do some default action
                                        End Select
                                        
                                        

And the first one of those things that is true will be executed. I find many uses for that.


Finally

For more suggestion about how to write understandable and maintainable Visual Basic code see my article on the subject.


If you find that this article has been helpful, please click the “thumb’s up” button below. Doing so lets me know what is valuable for EE members and provides direction for future articles. It also provides me with positive feedback in the form of a few points. 


If you have any suggestions for improvement or if you encounter any bugs, please send me a message. Thanks!


2
Unknown Error when importing an Excel spreadsheet to SharePoint Online 2016
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
1

Expert Comment

by:Andreas Wennborg
Solved my problem. Thanks!
1
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
0

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.