WorksheetFunction.Index Limitations

Posted on October 11, 2013March 17, 2018 by Dick Kusleika

Did you know that there is a limit to the array that can be passed into WorksheetFunction.Index? I read a really big range into an array and I wanted to split out a row. I used the INDEX(array, rownum, 0) feature, but I kept getting a Type Mismatch error. I couldn’t figure out what the heck was going on. I ended up testing my code on a smaller range and it worked as expected.

Sub IndexTest()
    
    Dim vaValues As Variant
    Dim i As Long
    Dim vaNew As Variant

    vaValues = Sheet1.UsedRange.Resize(2 ^ 16 + 1).Value
    
    For i = LBound(vaValues, 1) To UBound(vaValues, 1)
        
        vaNew = Application.WorksheetFunction.Index(vaValues, i, 0)
        
        Stop
    Next i
    
End Sub

Sub IndexTest()

Dim vaValues As Variant

Dim i As Long

Dim vaNew As Variant

vaValues = Sheet1.UsedRange.Resize(2 ^ 16 + 1).Value

For i = LBound(vaValues, 1) To UBound(vaValues, 1)

vaNew = Application.WorksheetFunction.Index(vaValues, i, 0)

Stop

Next i

End Sub

A little experimenting and I discovered that if my range is 65,537 rows long, I can’t pass it into Index. By the way, there does not appear to be any such limitation when the function is called from a worksheet cell, e.g. =COUNTA(INDEX(A2:G1007425,1,0)).

You may have noticed that the limit is the same number of rows in a 97-2003 file format. I wonder if that’s related. Am I missing something? Am I doing something wrong?

Excel 2010 64-bit Problems

Posted on September 28, 2013September 20, 2014 by Dick Kusleika

I upgraded to Office 2010 64-bit this morning and it’s not working out for me. I was offered the upgrade at work and of course I took it. Why wouldn’t I? Here are the problems I’ve encountered so far.

I can’t add menu items to the VBE. I have this line of code

Set MenuEvent.EvtHandler = Application.VBE.Events.CommandBarEvents(CmdBarItem)

that I got from Chip Pearson. Someone asked the question on the MSDN forum, but did not get an answer. That didn’t stop some Microsoft douche-nozzle from marking a response as “the answer”. Those forums suck for that reason.

Next, MZ Tools doesn’t work on 64-bit.

Next, CodeCleaner doesn’t work on 64-bit.

Nothing important, just three things I use every single day. Dang.

My plan is to search for updates to Office 2010 and hope that it was fixed. It was a recent clean install, so I expect that it’s up-to-date, but we’ll see. If that doesn’t work, then I don’t know. I guess I’ll have to put the menu on a userform and futz around with the window handles to make it a child of the VBE. Ugh.

Part two of my plan is to rewrite CodeCleaner for 64-bit. How hard could that be? It was only written by two of the best VBA programmers in history. Part three is replicating my favorite parts of MZ Tools in 64 bit.

I’ll be shoe-horning this three part plan into year-end, a technical review of a book, development of an add-in for sale, a consulting gig, and college football season. End rant.

Pegged

Posted on August 9, 2013 by Dick Kusleika

Can’t a guy sort a couple hundred rows?

File Path and Name for Links

Posted on July 16, 2013March 17, 2018 by Dick Kusleika

Unlike many people with whom I work, I almost never email a workbook. Instead, I save it to an appropriate place on the server and email the link. This seems to confuse the hell out of some people, but if I’m going to change the way things are done, it has to start with me and I have to take a hard line. But that’s not the point of this post. The point is that I need to get the path to these workbooks I want to email. I’m sure many of you remember this gem of a post from 2006 about getting the UNC path from the Web toolbar. That technique found its way into this utility:

Sub GetUNCAddress()
    
    Dim doClip As DataObject
    Dim Wb As Workbook
    Dim sText As String
    
    Set doClip = New DataObject
    
    'If there is no activeworkbook, you'll get the last open workbook.
    'this makes sure that the last open workbook doesn't have a real
    'path so you'll realize the error
    Application.ScreenUpdating = False
        Set Wb = Workbooks.Add
        Wb.Close False
    Application.ScreenUpdating = True
    
    sText = Application.CommandBars("Web").Controls("&Address:").Text
    
    doClip.SetText sText
    doClip.PutInClipboard
    
    Set doClip = Nothing
    
End Sub

Sub GetUNCAddress()

Dim doClip As DataObject

Dim Wb As Workbook

Dim sText As String

Set doClip = New DataObject

'If there is no activeworkbook, you'll get the last open workbook.

'this makes sure that the last open workbook doesn't have a real

'path so you'll realize the error

Application.ScreenUpdating = False

Set Wb = Workbooks.Add

Wb.Close False

Application.ScreenUpdating = True

sText = Application.CommandBars("Web").Controls("&Address:").Text

doClip.SetText sText

doClip.PutInClipboard

Set doClip = Nothing

End Sub

That worked like a peach at my old job where mapped drives were the wild west. At my new job, it is guaranteed that everyone has certain mapped drives. In those cases, I don’t want to confuse them further by giving them a UNC path. So I send them the mapped drive path.

Sub GetMappedAddress()
    
    Dim doClip As MSForms.DataObject
    
    Set doClip = New MSForms.DataObject
        
    doClip.SetText ActiveWorkbook.FullName
    doClip.PutInClipboard
    
    Set doClip = Nothing
    
End Sub

Sub GetMappedAddress()

Dim doClip As MSForms.DataObject

Set doClip = New MSForms.DataObject

doClip.SetText ActiveWorkbook.FullName

doClip.PutInClipboard

Set doClip = Nothing

End Sub

Works great most of the time. One of our mapped drive is considered an “internet location” by Excel. I really don’t know how are servers work around here, but I do know that when I open something from that particular mapped drive, I have to click the Enable Content banner at the top.

This is called Protected View and it’s a pain in the ass. When a workbook is in Protected View and is active, the ActiveWorkbook object is Nothing. The above code fails. The good news is that Application.ActiveProtectedViewWindow is Nothing if that’s not the case.

Sub GetMappedAddress()
    
    Dim doClip As MSForms.DataObject
    
    Set doClip = New MSForms.DataObject
        
    If Not Application.ActiveProtectedViewWindow Is Nothing Then
        doClip.SetText Application.ActiveProtectedViewWindow.Workbook.FullName
        doClip.PutInClipboard
    Else
        doClip.SetText ActiveWorkbook.FullName
        doClip.PutInClipboard
    End If
    
    Set doClip = Nothing
    
End Sub

Sub GetMappedAddress()

Dim doClip As MSForms.DataObject

Set doClip = New MSForms.DataObject

If Not Application.ActiveProtectedViewWindow Is Nothing Then

doClip.SetText Application.ActiveProtectedViewWindow.Workbook.FullName

doClip.PutInClipboard

Else

doClip.SetText ActiveWorkbook.FullName

doClip.PutInClipboard

End If

Set doClip = Nothing

End Sub

Do you want to know what else doesn’t work when Application.ActiveProtectedView is not Nothing? Application.Wait. That’s right, the Wait method fails with Method 'Wait' of object'_Application' failed. That’s just super. Anybody know what else fails in Protected View?

Workbook Window States: Maximized, Restored, Minimized, Limbo

Posted on June 18, 2013June 12, 2013 by Dick Kusleika

I have been seeing some strange behavior in Excel 2010. I run 2010 on at least three machines and this only happens on one. It just so happens that the one machine is the one I have the least control over. To my knowledge, windows inside of Excel have three states:

Minimized: The windows is reduced to a small rectangle in the lower left area of the application window.
Restored: I don’t know if that’s really what it’s called, but it’s the state between Min and Max. It’s floating inside the application window. The workbook name is on the window title bar, not the application title bar.
Maximized: The window takes up the whole of the application window and the workbook name shares the application window’s title bar with “Microsoft Excel”

When I add a new workbook to Excel using Control+N, a workbook is added in what appears to be a Restored state. The workbook window has it’s own title bar, distinct from the application’s title bar.

There’s just one little problem. It’s not Restored. For one thing, the workbook name is in both the window’s title bar (as if Restored) and the application’s title bar (as if Maximized). No matter, I know Ctrl+F10 toggles between Maximized and Restored, so I use that shortcut key.

That seemed to move the window to a Restored state as evidenced by the lack of the workbook name in the application title bar. Notice, however, that the Restore icon (between the Minimize icon and the Close icon on the window’s title bar) shows that it’s in a Maximized state. It appears that clicking it will move it to a Restored state. In fact, clicking it does nothing. And I mean nothing. If, however, I grab the window and move it one pixel in any direction…

It goes into a Restored state. Now the Restore icon turns into a Maximize icon. Clicking that icon maximized the workbook and no matter what I do from that point, it works as expected.

It must be a graphics card problem, right? That whole moving-the-window-one-pixel thing makes me think graphics card. But there are other oddities like when the workbook name is in two places. The answer, for me anyway, is to open a window with Ctrl+N, Ctrl+F10, Ctrl+F10. That gives me a maximized window, which is almost always what I want.

Excel corrupts certain workbooks in migrating from 2003 to 2007

Posted on April 14, 2013March 18, 2018 by Tushar Mehta

I got a email from a client asking for help because Excel was “destroying,” to use his terminology, his 2003 workbook after conversion to the 2007 format. And, after analyzing the kind of change Excel made, I had to agree.

The following in 2003

badnames 1
Figure 1

becomes, in 2013 (and in 2010),

badnames 5
Figure 2

The basic problem is that names that are legitimate names in Excel 2003 may become unacceptable in 2007 (or later). But, a more devastating problem is with a formula using a name with a dot in it. Even though it is completely legitimate, Excel changes the dot to a colon. This causes the formula =SW1.SW2 to become =SW1:SW2. Don’t ask me why. It just does. The result is the formula is all wrong and destroys the integrity of the workbook.

It appears that the cause may be Excel trying to help manage the transition of a XLS workbook into the newer format. In 2007, Microsoft increased the number of columns from 256 to 16,384. Consequently, the reference to last column went from IV to XFD. So, a name such as SW1, completely OK in 2003, became unacceptable in 2007. On converting a XLS file to a XLSX file, Excel will convert such names by adding an underscore at the start of the name. But, it seems to go beyond that, converting formula references to certain names with dots in them to a colon. This happens if both the tokens to the left and to the right of the dot could be legitimate cell references. So, Excel converts the formula =XFD1.XFD2 to =XFD1:XFD2 but it will leave =XFD1.XFE2 alone.

To replicate the problem:

Start with Excel 2003. Create a workbook and add the names shown in the Figure 1. Save and close the workbook.
Open the workbook in Excel 2013. Save it as a XLSX file. Acknowledge the warning message (see Figure 3),

Figure 3
Close and reopen the new XLSX workbook. The formulas will have the errors shown in Figure 2.

The safest way to work around this problem is to add an underscore before every name in the workbook before making the transition to the 2007 format. Obviously, the quickest way to do this would be with a very simple VBA procedure. But, through trial and error I discovered the code will not work in 2003. It runs without any problems but it doesn’t do anything!

So, the correct way to use the code is the following sequence.

Open the XLS file in 2013 (or 2010).

Run the macro below.

Option Explicit

Sub fixNames()
    Dim aName As Name
    For Each aName In ActiveWorkbook.Names
        With aName
        If Left(.Name, 1) <> "_" Then _
            .Name = "_" & .Name
        End With
        Next aName
    End Sub

Option Explicit

Sub fixNames()

Dim aName As Name

For Each aName In ActiveWorkbook.Names

With aName

If Left(.Name, 1) <> "_" Then _

.Name = "_" & .Name

End With

Next aName

End Sub

Now, save the file in the newer format. If your original workbook had no code in it, save the file as a XLSX file and acknowledge the warning that the VB project will be lost.
Close and reopen the file. You should see the correct data with all the names now starting with an underscore.

Figure 4

Tushar Mehta

My Excel 2010 Journey

Posted on February 8, 2012 by Dick Kusleika

I almost finally installed 2010. I say ‘almost’, because I’ve only installed it on a machine that previously had 2003 on it. I have yet to install it on my 2003/2007 machine. I guess I’ll be uninstalling 2007 and then installing 2010. Seems like a hassle, but if I tell it to upgrade, it will certainly screw something up. Anyway, here’s my first 2010 problem.

Sub OpenCSV()


    Dim sFldr As String

    Dim fso As Scripting.FileSystemObject

    Dim fsoFile As Scripting.File

    Dim fsoFldr As Scripting.Folder

    Dim dtNew As Date, sNew As String
    'Const sCSVTYPE As String = "Microsoft Office Excel Comma Separated Values File"

    Const sCSVTYPE As String = "Microsoft Excel Comma Separated Values File"
    Set fso = New Scripting.FileSystemObject
    sFldr = "C:\Documents and Settings\dick\My Documents\QBExport\"
    Set fsoFldr = fso.GetFolder(sFldr)
    For Each fsoFile In fsoFldr.Files

        If fsoFile.DateLastModified > dtNew And fsoFile.Type = sCSVTYPE Then

            sNew = fsoFile.Path

            dtNew = fsoFile.DateLastModified

        End If

    Next fsoFile
    Workbooks.Open sNew

End Sub

Once I installed 2010, Windows 7 thinks CSV files are “Microsoft Excel…” rather than “Microsoft Office Excel…”. What happened to the ‘Office’ part of the name? They were really pushing that at one point. I guess it’s time I get back to Redmond.

Infinite Loop of Errors

Posted on February 7, 2012 by Dick Kusleika

This morning one of my add-ins kept giving me Run-time error ‘438’; Object doesn’t support his property or method. I would click OK, and the error would immediately pop up again. The cause of the error was pretty simple – in fact I’m surprised it compiled before I shut down last night – but a variety of other circumstances made me have to Control-Break. Here’s the thrilling story.

I created a new toolbar button in code and screwed up one of the lines. Instead of

.OnAction = "PostToLog"

I put

.OnAction "PostToLog"

I forgot the equal sign. This was the cause of the error. Normally, this will cause a compile error, except that I had it deeply nested in With statements referencing the Controls property. Something like this

Sub test()


    Dim cb As CommandBarPopup
    With cb

        With .Controls.Add(msoControlPopup)

            With .Controls.Add(msoControlButton)

                .OnAction "wrong"

            End With

        End With

    End With

End Sub

Under these circumstances, .Controls is so loosely typed that it compiles fine. But that’s not the whole story.

I use the error handling system from Professional Excel Development. If you’re unfamiliar, here’s a short explanation.. One of the aspects of this error handling system is a global variable called gbDebug_Mode. It looks for a file on my computer and behaves differently if it’s me or you (because you don’t have the file). For you, it gives an error then stops. For me, it kicks me into the code so I can see what the problem is. That’s not really a problem, except…

The project is locked for viewing and password protected, so it doesn’t throw me in the code when there’s an error. I’m not sure what it was doing in this case that caused the error to keep repeating as apposed to just stopping. So to summarize:

I coded an error
In such a way that the compiler couldn’t detect it
In a procedure with error handling
In a project that was locked
And I ran it on a machine where the error handling throws me into the code

I was able to Control+Break at the error message and End the code. Then I could unlock the project and rerun it to find the problem.

Daily Dose of Excel

Haphazardly Posted Excel Information and Other Stuff

Category: Bugs

WorksheetFunction.Index Limitations

Excel 2010 64-bit Problems

Pegged

File Path and Name for Links

Workbook Window States: Maximized, Restored, Minimized, Limbo

Excel corrupts certain workbooks in migrating from 2003 to 2007

My Excel 2010 Journey

Infinite Loop of Errors