Microsoft Excel

134K

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

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
Cloud Class® Course: Microsoft Exchange Server
LVL 12
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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.
0
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
Here is why.
0
Multi-Purpose Knife
This article presents several of my favorite code snippets.
3
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
Comment Utility
Confirmed.  The process works like a dream and saved us days of work trying to update the links manually within the files
1
 
LVL 42

Author Comment

by:Shaun Vermaak
Comment Utility
Thank you Fanie
0
Cloud Class® Course: Ruby Fundamentals
LVL 12
Cloud Class® Course: 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.

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!


1
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
Comment Utility
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's Excel has many features that most people will never need nor take advantage of. Conditional formatting is one feature that you may find a necessity once you start using it.
2
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
0
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
0
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
1
 
LVL 4

Author Comment

by:Bob Flisser
Comment Utility
Thanks, Andrew!
0
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
0
 
LVL 53

Expert Comment

by:Martin Liss
Comment Utility
Thanks, that helps and I suggest you mention that the lists are related and also include the description of what you mean by "true database form". And rather than
Each option also then has an identifying code allocation. The codes are calculated in columns H to M...
I'd suggest something like
Each option also has an identifier which is generated via the formulas [that's more familiar to American audiences than formulae] found in columns H to M...
You should also change the reference to "code allocation" in you next to last paragraph.

I find
However, if these three lists are used for DV they would not be dynamic and change based on the selection from the previous category.
a little confusing. Do you mean
However, if these three were used for DV as is, they would not be dynamic because [explain why]

I'm going to publish this no matter what but consider my suggestions and let me know when you're done.
0
Free Tool: Port Scanner
LVL 12
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
2
 
LVL 30

Expert Comment

by:Shums
Comment Utility
Great Expert with great mind
0
 
LVL 53

Author Comment

by:Martin Liss
Comment Utility
Thanks.
0
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
3
Warning
This article describes a serious pitfall that can happen when deleting shapes using VBA.
3
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
1
 
LVL 24
Comment Utility
very nice, Dale.  If there is only need create a file, is there any performance advantage of this method over GetObject? thanks ~ crystal
0
 
LVL 50

Author Comment

by:Dale Fye
Comment Utility
Crystal,

GetObject will only get the first open Excel application.  If you have multiple Excel sessions running you cannot use GetObject to identify each of them.

Dale
0
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will discuss how to use VBA to automate Excel.
2
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are given for Office versions from 2003 through 2016.
0
 
LVL 31

Author Comment

by:Helen Feddema
Comment Utility
I will see if I can get that information.
1
 
LVL 31

Author Comment

by:Helen Feddema
Comment Utility
David -- this is most curious.  For Access 2010, the location for database templates is C:\Program Files\Microsoft Office\Templates\1033\Access.  But for Access 2016, it is C:\Program Files (x86)\Microsoft Office\root\Templates\1033\Access\Part.  I have 64-bit Office and Windows, in case that makes a difference (it probably does).
1

Microsoft Excel

134K

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.