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
Microsoft Azure 2017
LVL 12
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.

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.
4
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 46

Author Comment

by:Shaun Vermaak
Comment Utility
Thank you Fanie
0
PMI ACP® Project Management
LVL 12
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

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

Expert Comment

by:Marc Zosiac
Comment Utility
What about zipping two or more files in the same destination?
0
LVL 55

Author Comment

by:Gustav Brock
Comment Utility
You mean into the same destination?
That it cannot do. That would require an array or collection to be passed as parameter and/or acceptance of wildcards.

But you could copy the files to a temp folder, then zip that folder to the location where you wish the zip to be finally located - though that zip would contain the temp folder and its files.

I'll add it to my (long) to-do list.
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 54

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
Amazon Web Services
LVL 12
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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

Expert Comment

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

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 51

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

Expert Comment

by:Roy Luce
Comment Utility
Suggest adding a notation regarding hidden files.

Spent over an hour this morning trying to locate Normal.dotm in Word 2016.  
It took an hour because the AppData folder in C:\Users\[User Name]\AppData\Roaming\Microsoft\Templates is a hidden folder.  
Only after finding and changing the display option for hidden folders was I able to locate and update Normal.dotm
0

Expert Comment

by:Kali
Comment Utility
I am still having problems accessing the created templates using Word 2013.  I understand we need to direct the File Locations to the proper directory, which I have done.  Now what?  I can't see any way to access these files AS templates.  We could do this in Word 2007 and Word 2010, but NOT in Word 2013.  I do not know how to properly access them.

Thank you for your help.
0

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.