Double-spaced VBA code

0

Hi Don,

The code below appears with double spacing in my posting window. It's the last procedure in Module1 of the attached workbook.

Private Function GetSheet(ByVal SheetName As String) As Worksheet

    ' 02 Jun 2019

    Dim Ws As Worksheet

    Set Ws = ActiveSheet                    ' remember the ActiveSheet

    Application.ScreenUpdating = False

    On Error Resume Next

    Set GetSheet = Worksheets(SheetName)    ' error if sheet doesn't exist

    If Err Then

        ' create the sheet

        Set GetSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))

        ActiveSheet.Name = SheetName

        Ws.Activate                         ' re-activate the original ActiveSheet

    End If

    Application.ScreenUpdating = True

End Function

Below is the same code between code tags. The tags were entered first, then the tag's text selected and the code pasted to repalce it. Prior to uploading the code was double-spaced but I believe the extra returns will disappear when uploading the post.

Private Function GetSheet(ByVal SheetName As String) As Worksheet
    ' 02 Jun 2019

    Dim Ws As Worksheet

    Set Ws = ActiveSheet                    ' remember the ActiveSheet
    Application.ScreenUpdating = False
    On Error Resume Next
    Set GetSheet = Worksheets(SheetName)    ' error if sheet doesn't exist
    If Err Then
        ' create the sheet
        Set GetSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        ActiveSheet.Name = SheetName
        Ws.Activate                         ' re-activate the original ActiveSheet
    End If
    Application.ScreenUpdating = True
End Function

Below is a third version of the same code (I didn't refresh the clipboard between making copies of it here). This time the code was pasted first, with the same result as in the first version above, then selected and the Code tag button pressed to insert the tags around the selection. Up until the moment of posting this behaved in the same manner as the previous example. I expect the double spacing to disappear upon uploading.

Private Function GetSheet(ByVal SheetName As String) As Worksheet

    ' 02 Jun 2019



    Dim Ws As Worksheet



    Set Ws = ActiveSheet                    ' remember the ActiveSheet

    Application.ScreenUpdating = False

    On Error Resume Next

    Set GetSheet = Worksheets(SheetName)    ' error if sheet doesn't exist

    If Err Then

        ' create the sheet

        Set GetSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))

        ActiveSheet.Name = SheetName

        Ws.Activate                         ' re-activate the original ActiveSheet

    End If

    Application.ScreenUpdating = True

End Function

This file was created using Excel 2010 and Windows 8.1. I get the same result when using Excel 2013 on a Windows 8.1 (either English or Chinese versions).

Answer
Discuss

Discussion

Interesting. The double spacing didn't disappear in the final test where the code tags where added after the code was pasted. This post was created on Win 8.1 using Excel 2010
Variatus (rep: 3368) Jun 25, '19 at 8:56 pm
ok, I'll play around with it and see what I see. Thanks for the post!
don (rep: 1745) Jun 25, '19 at 10:14 pm
I checked it out and made lots of test posts and edits following your steps and, basically, this is exactly how it is supposed to work. There are no extra lines in the code without CODE tags, those are html <p> paragraph tags, which add extra spacing on their own. The CODE tags include a <pre></pre> tag which makes displaying the vba code easier and more accurate, which is why it doesn't add <p> tags around the lines in the code. The last example that you gave looks like the editor added the <p> tags and then the processing in the backend took them out and replaced them with empty lines, which would account for the extra spacing. I was not able to reproduce your third example completely, but I am quite sure that my explanation of why it happens is accurate. That said, changing this is quite finicky and I don't think it causes enough issues to warrant playing around with the html parser in the backend - also I'm using Chrome and that might have been why the third example couldn't be reproduced for me - different browsers might handle pasting extra lines differently.
don (rep: 1745) Jun 26, '19 at 1:03 pm
Sorry Don, I disagree. When code is pasted it should appear unaltered. That's how it should work. Maybe, if you have spare time in the future ....
I'm using Chrome as well. However it seems conceivable to me that the stability of the Internet connection might play into the last example.
Anyway, thanks for your feedback.
Variatus (rep: 3368) Jun 26, '19 at 8:48 pm
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login