Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.
Daily Dose of Excel » All Tags » Arrays (RSS)

Browse by Tags

  • Sparkline Gauge

    I have a list of labels and a list of values. I also have a value from somewhere in the range of my values list. I want to show where the value falls in the list of values with a red mark. The labels need to be proportional to their values. An XY chart seemed [...] Read More...

  • Retrieving Lost Comments

    I’ve restored a few posts in the last few months that were lost. I didn’t restore any of the comments. Honestly, I should have but I didn’t even think about it. But when I went to restore the In Cell Charting post, I noticed there were 85 comments. That seemed worth my while. First I [...] Read More...
    Filed under: ,

  • String Diffing

    I’ve wanted to have some wiki-like diffing in my userform textboxes for a while now. Since I’ve been using wikis almost daily, I want the revisioning feature in everything I do. I’m not there yet, but I decided to see what kind of algorithm I would need to do it. I read the Wikipedia article [...] Read More...
    Filed under: ,

  • Find Matching Data in Array Speed Test

    JP has a good post about finding exact matches in arrays. I use a similar method. I Join the array with delimiters around all the values, then use Instr to see if it’s there. Here’s my code: Function IsInArrayDK(vArr As Variant, sValueToCheck As String, _     Optional bMatch As Boolean = True) As Boolean [...] Read More...
    Filed under: ,

  • How Microsoft Names Products

    I was recently granted unprecedented access to Microsoft’s internal code base. I found this function to generate brand names for print and web. It really explains a lot. Public Function MicrosoftifyBrandName(ByVal sBrand As String) As String         Dim vaSpace As Variant     Dim i As Long         vaSpace [...] Read More...
    Filed under:

  • Flipping Coins

    Sometimes people post their homework problems on stackoverflow.com I don’t answer homework problems, but I do like to try to figure them out. Problem description: Take a stack of coins all heads up. Upturn the topmost coin, place back on the stack and then proceed: take the top 2 coins and upturn as a single [...] Read More...
    Filed under:

  • TRANSPOSE Changes Array Dimension

    Here’s an odd one, I think. I have code that creates a two-dimensional array and I want to use Application.WorksheetFunction.Transpose to switch the rows and columns. Sub TestTrans()         Dim aTest(0 To 3, 0 To 1) As Long     Dim vaTrans As Variant     Dim i As Long, j As Long       [...] Read More...
    Filed under:

  • Parsing Version Numbers

    Here are two ways to parse out a string representation of a version number. Start with a version number like “v7.10″. End up with the version number (7) and the revision number (10). Sub GetVersion()         Dim lVer As Long     Dim lRev As Long     Dim dTemp As Double       [...] Read More...
    Filed under:

  • The Join Function

    The Join function concatenates a bunch of strings in an array into one long string. I rarely use this function, but maybe I should start, as JP suggests. I wondered if it was really that much faster. Declare Function timeGetTime Lib "winmm.dll" () As Long Sub TestJoin()         Dim i As Long     [...] Read More...
    Filed under:

  • Writing to Noncontiguous Ranges with VBA

    Mike comments: I’m wondering if this same Array -> Range approach will work with a non-contiguous range? For example we have an application at work that needs to write around 400 values into an Excel sheet (it does this via ActiveXObjects from the browser. Yuck, I know). At present it does this in a loop calling .cell(row, [...] Read More...
    Filed under: ,

  • Pages to Print

    In Microsoft Word’s print dialog, there’s a textbox called Pages. In it, you can specify which pages you want to print by separating individual pages and page ranges by a comma. I have a small app wherein I enter a starting serial number and an ending serial number. The output is [...] Read More...
    Filed under: ,

  • Getting Array Data from a Filtered List in VBA

    Getting data from an Excel sheet into an array is usually best accomplished with a statement that looks something like this: vMyArray = Sheet1.UsedRange.Value I thought it would be keen to fill an array from a filtered list, so I coded Sub ArrFilteredList()         Dim vArr As Variant         vArr = Sheet1.UsedRange.SpecialCells(xlCellTypeVisible).Value       [...] Read More...
    Filed under:

  • Multiple Substitute VBA

    In regard to Multiple Substitute Formula, here’s one way to do it in VBA. Sub RemoveStates()         Dim rInput As Range     Dim rStates As Range     Dim vaInput As Variant     Dim vaStates As Variant     Dim i As Long, j As Long     Dim sTemp As String         Set rInput [...] Read More...
    Filed under:

  • A quick tip - Working with arrays with unknown bounds

    Two recent instances required transferring data from one array to another. One was a solution to speed up the use of COUNTIF for each element in a large range testing against another large range. In the process I had to build several building blocks including a replacement for the native Excel TRANSPOSE function, [...] Read More...
    Filed under: , ,

  • VBA Random Integers

    Every time I post code that includes picking a random integer, Tushar reminds me that my methods produce biased results. The last time, I swore to myself I would do it right. Here's how the old me would have done it: Sub RandTest1()         Dim lRand As Long     Dim i As [...] 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.