Archive for the 'Excel' Category

Add Multiple Blank Rows In Microsoft Excel

Wow!  Long time without a post.

Anyway, I can’t believe that I’ve never realised how hard it is in the older version of Microsoft Excel to insert multiple blank rows.

I searched Google and found a few sites saying press F4 or buy our tools so I wrote  quick macro.

The macro asks how many rows you want to insert and then inserts that many rows ABOVE the current cursor position. (If you don’t specify a number of rows it defaults to 2).

I’ve tried it a few times and it seems to work okay.

If this may be useful to you perhaps give it a go!

Sub Macro1()
'
    Application.CutCopyMode = False
    
    Dim strHowManyRows As String
    Dim NumberOfRowsToInsert As Integer
    Dim c As Integer ' Just a counter


    strHowManyRows = InputBox(Prompt:="How many rows should I insert? (These rows will be inserted ABOVE the currently selected row)", _
          Title:="HOW MANY ROWS?", Default:="2")

          

        If strHowManyRows = "" Or _
           strHowManyRows = vbNullString Then
            
            
           NumberOfRowsToInsert = 2
        Else
    
           NumberOfRowsToInsert = Val(strHowManyRows)
    
        End If
        
        
    For c = 1 To NumberOfRowsToInsert
        Selection.Insert Shift:=xlDown
    Next c

End Sub

Excel VBA – PERSONAL.XLS Locked For Editing – Resolution

I’ve just been working on an Excel problem that requires one worksheet to be opened at a given time (using the Windows Task Scheduler) and a macro run.

However, when testing this I came across the “Personal.XLS locked for editing” message.

It seems that this is due to having more than one instance of Excel running at one time.

Dave Peterson at http://www.pcreview.co.uk/forums/thread-2831009.php has suggested a solution to this.

This involves marking the personal.xls file as read-only and this, indeed, seems to stop the error.

If you haven’t come across the personal.xls file before it’s a hidden workbook that opens when you start Excel. Any code in this workbook is available in all workbooks you have open in Excel.

On a Windows XP machine the file is usually located in the Excel startup folder at:

C:\Documents and Settings\[UserName]\Application Data\Microsoft\Excel\XLSTART

If you’re using Vista it’s probably in the folder at:

C:\Users\[UserName]\AppData\Roaming\Microsoft\Excel\XLSTART

where [UserName] is the user name you’re currently logged onto the machine as.

However, the file could be located elsewhere so use Search to find it if it’s not in the folders mentioned above.

Although making the file read-only solves this problem it does raise other issues if you often add code to personal.xls but is a useful workaround in my situation.

Excel VBA – Save As (SaveAs) Without Confirmation

When saving a file in Excel from VBA code it is sometime useful to avoid the display of a Save As dialog if the file to be saved already exists (i.e. to overwrite any exisiting files).

This is pretty easy but one of those things I always forget how to do!

Here’s the code:

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs (etc.)
Application.DisplayAlerts = True

Thanks to the users at http://www.vbforums.com/showthread.php?t=528826 who reminded me of this for about the hundredth time!

vbNullString – Empty Cells in Excel VBA

My post on IsNull in Excel VBA has generated some hard thought!

The site Daily Dose of Excel kindly mentioned my post at http://www.dailydoseofexcel.com/archives/2008/06/26/identify-empty-cells-in-vba.

This has lead to some excellent discussion of the use of “empty” cells in Excel within either formula or VBA code.

I’ll repeat here what I posted on that site as a reference for myself and others:

[I was reminded of] some Win32 API programming in VB that I’d done years ago and I came across the following definition of vbNullString (now, I also note, repeated in the Office 2007 help file):

vbNullString – String having value 0 – Not the same as a zero-length string (“”); used for calling external procedures

As a VBA (or VB) string is only a pointer to a character array. The address in memory pointed to by this variable points to the start of the character array.

The array terminates in a 2-byte null terminator and is preceded by 4 bytes showing the length of the string in bytes.

Using “” to declare an empty string results in a character array of size 0. However, this will still contain the 4-byte length data, and the 2-byte terminator.

vbNullString still returns a pointer but one that does not point to any character array resulting in no 4-byte length field or 2-byte terminator.

An article by Microsoft (of which I have a printed copy but now can’t find the URL) on VBA optimization suggests that vbNullString would execute 50% faster than “”.

Basically, using vbNullString seems to be suitable in some case but not in others (e.g. in charts where it returns a value of 0).

I’m sure I’ll be adding more about this soon after I’ve had time to play with it some more.

IsNull in Excel VBA

I’ve come from a background of microsoft access, SQL Server and ASP programming so am pretty familiar with the concept of NULL.

I’ve recently been working on some Excel VBA code and was puzzled when an empty cell would be reported as not being NULL or empty string.

For anyone else coming across this problem have a look at the vbNullString constant which seems to replace NULL in some cases.


Blog Stats

  • 221,277 hits

Counter

ClustrMap