Microsoft Excel

130K

Solutions

195

Articles & Videos

37K

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

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

Expert Comment

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

Author Comment

by:Martin Liss
Comment Utility
Thanks.
0
On Demand Webinar: Networking for the Cloud Era
LVL 9
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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.
2
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.
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).
0
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
2
Using the Hyperlink formula in Excel
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
3
Decision Making
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
25
 

Expert Comment

by:abbey Law
Comment Utility
Wow! This is impressive and so clear to understand. Thank you for sharing your invaluable knowledge :)
1
 

Expert Comment

by:Aamir Hussain
Comment Utility
A simple and excellent solution of the complicated question. Well done Mr.Shums :-)
1
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to do.
25
 

Expert Comment

by:abbey Law
Comment Utility
Your knowledge of Excel is impeccable. Being new to Excel myself and only just learning the basics, this article is extremely useful. I look forward to your future articles, thank you once again for sharing your knowledge with is!
1
 

Expert Comment

by:Aamir Hussain
Comment Utility
A very good cooperative and professional expert.Always give new idea with excellent logic .
1
Enroll in June's Course of the Month
LVL 9
Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Security
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
2
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
2
 
LVL 33

Author Comment

by:Rob Henson
Comment Utility
Hi Thomas,

Thanks for the pointers. I have now edited the article as suggested and am happy for it to proceed.

Thanks
Rob H
0
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
1
Screenshot of Automated Link Checker
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlighted yellow and are converted into a hyperlink.
1
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
0
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for you to try out.
2
In this article we have discussed about the OS X EI Capitan and how to fix Wi-Fi issue in OS X El Capitan. We have explained how to delete system level preferences and create a new Wi-Fi location to resolve Wi-Fi issue.
1
Eggs in one array
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes a way to elegantly solve the problem.
6
 
LVL 1

Expert Comment

by:sdeller
Comment Utility
The suggestion using UBound is incorrect.  With the code like this:
  CustData(UBound(CustData)).strCustName = ...
  CustData(UBound(CustData)).dblSales = ...
the code will create a new entry with the first assignment, causing UBound to increment. So the second line will refer to yet another new entry.  You need to assign both values with one assignment.
0
 
LVL 48

Author Comment

by:Martin Liss
Comment Utility
I'm sorry but that is not correct. In VB6, once an array is created the only ways to increase the upper bound of the array is through Dim, Redim and Redim Preserve. If you place this bit of code in a new project you'll see that I'm correct.

Option Explicit

Private Type CustomerData
    strCustName As String
    dblSales As Double
End Type
Private Sub Form_Load()
Dim CustData() As CustomerData
Dim lngIndex As Long

ReDim CustData(2)

CustData(0).dblSales = 0
CustData(0).strCustName = "Customer 0"

CustData(1).dblSales = 100
CustData(1).strCustName = "Customer 1"

MsgBox "The upper bound of CustData is " & UBound(CustData)

CustData(UBound(CustData)).dblSales = 200
CustData(UBound(CustData)).strCustName = "Customer 2"

MsgBox "The upper bound of CustData is still " & UBound(CustData)

For lngIndex = 0 To UBound(CustData)
    Debug.Print CustData(lngIndex).strCustName & " has sales of " & CustData(lngIndex).dblSales
Next
End Sub

Open in new window

0
Oh no
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
68
 

Expert Comment

by:M Melson
Comment Utility
Very nice article. Thank you for taking the time writing and sharing it.  I can use part with variable names to indicate the type of variable. Good advice.
0
 
LVL 48

Author Comment

by:Martin Liss
Comment Utility
Thanks.
0
Free Tool: SSL Checker
LVL 9
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Example PowerPoint Add-In
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
5
 
LVL 16

Expert Comment

by:Kyle Santos
Comment Utility
Great job!
0
 
LVL 12

Author Comment

by:Jamie Garroch
Comment Utility
Thanks Kyle Santos :-)
0
Where used to see Gantt charts for illustrating project timelines, but what if I wanted to visualize passed timed events? Here's how.
5
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
Nice job illustrating.  Voted Yes.
0
 

Expert Comment

by:kev_hinds
Comment Utility
Pretty decent.  I am sure it could come in handy some day.  Thanks!
0
Create your own formula
This article describes how to create your own Excel formula when there isn't a built-in formula that meets your needs.
19
 

Expert Comment

by:J. Andrew Smith
Comment Utility
This is for Sach44: a co-worker of mine came up with a very nice way to suppress automatic recalculation and then restore it to its previous state -- after all you might have two procedures, A calling B, and if you blindly set it to manual at the top of each, and automatic at the bottom of each, then it will be automatic right after A has called B.  But, if you set up a local variable in each function to hold its state, you have to remember to include a statement at the bottom to restore it, so now you can't use Exit Sub/Function, and you even have to write up an error-handler to resume at the statement that restores it -- what a pain!

Here's the nice alternative: a class that suppresses auto-calculation when instantiated and restores the state when destroyed:

Class clsCalcSuppressor
Option Explicit

Private FormerStatus As XlCalculation

Private Sub Class_Initialize()

    FormerStatus = xlCalculationAutomatic       ' Establish default

    If Workbooks.Count > 0 Then                 ' With no workbooks open, just mentioning "Application.Calculation" causes type mismatch!
        FormerStatus = Application.Calculation
        Application.Calculation = xlCalculationManual
    End If

End Sub

Private Sub Class_Terminate()
    If Workbooks.Count > 0 Then Application.Calculation = FormerStatus
End Sub

Open in new window

Now all you have to do is include two statements just at the top of any procedure:
Dim CS As clsCalculationSuppressor
Set CS = New clsCalculationSuppressor

Open in new window

...and when the procedure ends by any means, the state is restored, because CS is local and therefore gets destroyed.
(But what I wonder is: why doesn't the one statement Dim CS As New clsCalculationSuppressor work?)

Of course this technique can be used for many other application properties, e.g., the status bar, displaying alerts, enabling events, the mouse cursor, screen updating...
0
 
LVL 1

Expert Comment

by:Sach44
Comment Utility
Hi Andrew.

On my part, I have carefully stored the many original Excel user settings using registry entries upon activation (commandar("ply"), decimal seperator, thousand seperator, xlcalculation, ...), modified them for my needs, and then set them back to the original state on the workbook deactivate_event (that is launched on close and opening a workbook in the same excel instance). Curiously, I have noticed that the orginal usersettings remain if if a new Excel instance is created with my workbook previously openned in its own instance. This appears to work alright for my needs at the moment. Tested using Excel 2013 64 bit and Excel 2010 32bit.

As I am a beginner, I suppose the solution you suggested is quicker; I will give it a try once I get a chance. Thanks!
0
This collection of functions covers all the normal rounding methods of just about any numeric value.
2
 
LVL 50

Author Comment

by:Gustav Brock
Comment Utility
Finally I have these assembled. Through the years bits and pieces were collected, but now I had the option for a rewrite.

/gustav

PS: There are minor flaws in the layout but the editor is somewhat strange so I couldn't get it completely straight.
0
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
Nicely laid out.  Voted Yes.
0

Preface:

When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and removed the user interface for creating commandbars. This resulted in a common misconception that all CommandBars have been deprecated (eliminated). The truth is that you can no longer create CommandBar MENUS starting with Office 2007. However, you can still create and use pop-up menus, which are sometimes referred to as "right-click" or "shortcut" menus, using VBA. This series is all about these types of menus; I will use these two terms interchangeably in the remainder of this article.
 

Introduction:

Part 1 of this series (Understanding and Using Commandbars) describes techniques for exploring and using the CommandBar object. Part 2 of the series (Creating your Own) provides a more detailed discussion of the CommandBar and CommandBar Control object models, and demonstrates how to create your own shortcut menus.

While working on a new application, I found a need to not only develop my own right-click menus, but to augment and replace several of the …
4
 
LVL 48

Author Comment

by:Dale Fye (Access MVP)
Comment Utility
Thanks.  This is the first article I've written using the new editor, and I like the flexibility.  I was a little disappointed in the ability to size text other than using the couple of "styles".
0
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
Excellent article.  Voted Yes.
0
Hi all,

This is a brief aid on how to create a simple add-in into all new Workbook Excel files.

It may happen that you use a Module with a specific VBA macro in it that you wish you could include it as a default macro into all new Excel files you create. This article will guide you through the steps you will need to take to make it happen. Then you will be able to apply the same steps to your own modules or macros.

Step 1:
Open a new excel file. Press Alt+F11 to open the Visual Basic Editor, and then click Insert and select Module  as shown below. Then copy and paste this code:
Option Explicit
Function GetFormula(x As Range) As String
GetFormula = x.Formula
End Function

Open in new window

Insert-function-2.jpgStep 2:
You need to save this workbook as: Excel Add-In. Save the file with name as:  GetFormula. Make certain to save the file under your Documents in a place you will remember.
Save-as-excel-add-in.jpgStep 3:
Now it's time to add the new add-in GetFormula to all new Excel files where you will Include it as a default add-in.

Open a new Excel file, and go into File / Option / Add-Ins.  In the Manage field  select Excel Add-ins and then, click Go...
Excel-add-ins-selection.jpgStep 4:
Search for you  GetFormula Excel add ins file you created at Step 2. Follow the steps 1 through 3 shown in the picture below, and then click OK. Make sure to check the GetFormula  before you click OK.
find-add-in-file.jpg
Step 5:
Close Excel without saving the file and then re-open the program. Press Alt+F11 to open the Visual Basic Editor, and see if your module is showing your version of  GetFormula .
Excel-file-with-default-add-in.jpg
You have now made  GetFormula
1
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
I have never tested it. But i think that you can build an Add-in that applies multiple settings and then, you would just need to call that function manually if you create a new sheet.

I don't think we can add this to automatically applies to all new excel files without call the add-in macro.
0
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
Another option could be to create a master VBA file with all your default setting, and when you open your file, it will automatically prompt you to save the file as a new VBA file with all the macros inside.
0

Microsoft Excel

130K

Solutions

195

Articles & Videos

37K

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.