Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

September 2010 - Daily Dose of Excel

Sort by: Most Recent | Most Viewed | Most Commented

  • Formula Editing Made Easier

    I’ve been working a bit more with editing formulas in Notepad++. I changed my xml file names to be different than Bob’s. I’m unable to Remove a user defined language without causing errors and having to reinstall Notepad++. The two files I have are XLFF.xml - goes in ..\Program Files\Notepad++\plugins\APIs\ xlf_udl.xml - import from [...] Read More...
    Filed under:

  • Change ContextMenu in Excel with VBA or RibbonX(only 2010)

    Hi all I created this page this week : http://www.rondebruin.nl/contextmenu.htm I am curious about what developers think about the option in Excel 2010 to change contextmenus with RibbonX. Creating a dynamic menu in the Cell menu in Excel 2010 for example is easy to do because every time you right click on a cell it will run [...] Read More...
    Filed under:

  • Formula Editor in Notepad++

    Bob wrote a blog post last week, Formulas Made Easy, that I read with great interest. He uses a custom language in Notepad++ to allow for indenting formulas, editing formulas, and all the other stuff you can do in a text editor that you can’t do in Excel’s formula bar. I downloaded Notepad++ [...] Read More...
    Filed under:

  • MatLab vs. Excel

    The engineer who attempted teaching me MatLab used a short script to demonstrate its matrix manipulation prowess. MatLab (shortened from Matrix Laboratory) is built from the ground up to do matrix mathematics. I wanted to translate his script to VBA, to see, 1. If it’s possible, and 2. How Excel compares. [...] Read More...
    Filed under:

  • Make a Simple HTML Table from a Range

    Tushar comments 1) Please consider posting the data in a table or attach a file. It’s a PITA to recreate the data set! Very good point. I made this function. Public Function MakeHTMLTable(rInput As Range, Optional bHeaders As Boolean = True) As String         Dim rRow As Range     Dim rCell As Range     Dim [...] Read More...
    Filed under:

  • Case Sensitive Lookup

    The VLOOKUP function (and its brothers MATCH, LOOKUP, and HLOOKUP) do not care about the case of the lookup value. Take this data, for example: A B 1 Name Number 2 Bob one 3 Jim two 4 bob three 5 jim four The formula =VLOOKUP("bob",A2:B5,2,FALSE) will return “one”. It finds “Bob” and quits looking because it considers that a match. If you need to find the true “bob”, you can use [...] Read More...
    Filed under: ,

  • Output from Class to Range

    Finding Comparables Setup Filling Classes and Finding Specific Instances Filtering and Sorting Classes In this final installment, I look at the code to write a class module to a range. That is represented by the last two lines of this controlling procedure: Sub GetComperablesByState()         Dim clsCompanies As CCompanies     Dim clsWisky As CCompanies     Dim [...] Read More...
    Filed under:

  • Filter and Sort Classes

    Following up on Finding Comparable Objects and Filling Classes and Finding Specific Instances, today we look at filtering collection classes and putting the results into a new instance and sorting collection classes. For reference, here’s the controlling sub: Sub GetComperablesByState()         Dim clsCompanies As CCompanies     Dim clsWisky As CCompanies     Dim vaOutput [...] Read More...
    Filed under:

  • Filling Classes and Finding Specific Instances

    In yesterday’s post, I discussed the setup of data and classes to find comparables. Today, I’ll discuss filling the classes with data and finding a specific instance of a class by a property that’s not the key to the collection. Here’s my main procedure again Sub GetComperablesByState()         Dim clsCompanies As CCompanies     Dim [...] Read More...
    Filed under:

  • Finding Comparable Objects in Class Modules

    Here’s the made up scenario (that resembles a real one, kind of): I have 200 companies with their city, state, and a sales figure. I want to find the companies whose sales are near my focus company, but only if they’re in the same state. In Excel’s UI, the steps would be 1) Sort the [...] Read More...
    Filed under:

  • Placing Shapes with VBA

    I always enjoy Daniel Ferry’s (and ’s) Excel illusions. I tried the Curry Paradox one and it didn’t work for me. I’m not sure why. I think it’s either because my screen resolution is different or because I ran it on a secondary monitor, but the shapes weren’t moving to their rightful [...] Read More...
    Filed under:

  • Left Looking Lookups

    OR: The problem with VLOOKUP. Wikipedia gives us a table of birthstones, and I think just because it’s Wikipedia, there’s a trailing space after every entry. Pasted into a spreadsheet, the table looks like this:   A B C D E 1 Month  Traditional Birthstone(s)  Modern Birthstone(s)  Mystical Birthstone  Ayurvedic Birthstone  2 January  Garnet  Garnet  Emerald  Garnet  3 February  Amethyst  Amethyst  Bloodstone  Amethyst ...
    Filed under:

  • Building a self-sorting list (Part 2)

    In Part 1 we ended up with Column D, a sorted list. One criticism was the many times we were counting the numbers in a column. We should improve it and only count once. Via Insert/Name/Define define Count_BB to =COUNT(Sheet1!$B:$B), and then select Columns C:D, and “Replace All” COUNT(B:B) with Count_BB. [...] Read More...
    Filed under:

  • Building a self-sorting list

    I haven’t posted in a good while. I’ve done over half the Euler problems, but haven’t had the time to research the high-numbered ones considering I might even understand them. Now that I’ve retired from my second career, and my third is only part-time, maybe I’ll get back to it. Today is [...] Read More...
    Filed under:

  • Setting a Base Directory

    If you want your Excel app to default to a specific directory when opening or saving files, see Changing the Current Directory. Be sure to read the comments. I have a slightly different situation. I want my app to open to a specific directory that will give me easy access to sub directories. [...] Read More...
    Filed under:
1 2 Next >
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.