none
Run-time error 5 - WITH...END WITH RRS feed

  • Question

  • Any idea why the CASE ELSE here fails with, "Run-time error '5':  Invalid procedure call or argument":

    Dim RowNum As Integer
    Dim ColNum As Integer
    Dim TotalRows As Double
    Dim TotalCols As Double
    
    ' Store the total number of rows and columns to variables.
     TotalRows = Selection.Rows.Count
     TotalCols = Selection.Columns.Count
    
    ' Loop through every cell, from left to right and top to bottom.
    For RowNum = 1 To TotalRows
      For ColNum = 1 To TotalCols
        With Selection.Cells(RowNum, ColNum)
          Dim ColWidth As Integer
          ColWidth = Application.RoundUp(.ColumnWidth, 0)
    
          ' Store the current cells contents to a variable.
          Select Case .HorizontalAlignment
            Case xlRight
              CellText = Space(ColWidth - Len(.Text)) & .Text
            Case xlCenter
              CellText = Space((ColWidth - Len(.Text)) / 2) & .Text & _
              Space((ColWidth - Len(.Text)) / 2)
            Case Else
              CellText = .Text & Space(ColWidth - Len(.Text))
          End Select
        End With
       
        'Other misc stuff BEGIN
        '...
        'Other misc stuff END
     
      ' Loop to the next column.
      Next ColNum
    
    ' Loop to the next row.
    Next RowNum


    But change the CASE ELSE to this, it runs fine?

    Dim RowNum As Integer
    Dim ColNum As Integer
    Dim TotalRows As Double
    Dim TotalCols As Double
    
    ' Store the total number of rows and columns to variables.
     TotalRows = Selection.Rows.Count
     TotalCols = Selection.Columns.Count
    
    ' Loop through every cell, from left to right and top to bottom.
    For RowNum = 1 To TotalRows
      For ColNum = 1 To TotalCols
        With Selection.Cells(RowNum, ColNum)
          Dim ColWidth As Integer
          ColWidth = Application.RoundUp(.ColumnWidth, 0)
    
          ' Store the current cells contents to a variable.
          Select Case .HorizontalAlignment
            Case xlRight
              CellText = Space(ColWidth - Len(.Text)) & .Text
            Case xlCenter
              CellText = Space((ColWidth - Len(.Text)) / 2) & .Text & _
              Space((ColWidth - Len(.Text)) / 2)
            Case Else
              CellText = Selection.Cells(RowNum, ColNum).Text & Space(ColWidth - Len(Selection.Cells(RowNum, ColNum).Text))
          End Select
        End With
       
        'Other misc stuff BEGIN
        '...
        'Other misc stuff END
     
      ' Loop to the next column.
      Next ColNum
    
    ' Loop to the next row.
    Next RowNum

    It's like the WITH...END WITH loses scope or something, I'm not sure.  Thoughts?




    • Edited by COB-MikeB Monday, February 13, 2017 10:16 PM
    Monday, February 13, 2017 10:14 PM

Answers

  • Hi COB-MikeB,

    you had mentioned that you got Run time error 5 due to line below.

    CellText = .Text & Space(ColWidth - Len(.Text))

    I try to test this line on my side and find that the value of "ColWidth" should be equal or greater then the value of "Len(.Text)"

    other wise you will get Run Time Error 5.

    please refer simple example below.

    below value I am referring from code.

    code:

    Sub demo2()
    Dim CellText As String
    Dim ColWidth As Integer
    ColWidth = 10
    With Selection.Cells(1, 1)
    CellText = .Text & Space(ColWidth - Len(.Text))
    Debug.Print (CellText)
    Debug.Print (Len(.Text))
    End With
    
    End Sub
    

    Output:

    so you can see that value of "Len(.Text)" =6.

    so if I set the value of "ColWidth " less then 6 , then I get error below.

    so now you know the root cause for the issue and you can try to correct it in your code to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, February 14, 2017 2:05 AM
    Moderator
  • Hey Deepak - I triple-checked and you were right, the problem was bad data that caused a negative value in the Space() function.  I had double-checked the data after I read your post and the data looked good.  Howerver, I just talked to the user and they told me that they noticed the bad data and changed it after I left, but didn't realize it was related to the error.

    As for why I thought that adding, "Selection.Cells(RowNum,ColNum)" to the ".Text" fixed the problem ... basically, my rusty VB6 skills didn't transfer to VBA very well.  Retracing my steps - First, I wanted to break down the line with the error by using the Immediates window but I had to use the fully-qualified reference in order to see the value of, ".Text".   Since I added it to Immediates, I temporarily added it to the Macro code for consistency while troubleshooting.  Second, I was examining the data in the spreadsheet being processed by the macro ... BUT ... I didn't know that by simply clicking a different cell in the sheet, I was also  changing the area of the, "Selection" object I was debugging.  I must've clicked a different cell to one with good data just before hitting F8 to attempt to run the line again.  When I got past the error, I thought all I had done was add the fully qualified reference to, ".Text". 

    Thanks for your help.

    • Marked as answer by COB-MikeB Tuesday, February 14, 2017 6:31 PM
    Tuesday, February 14, 2017 6:27 PM

All replies

  • Hi COB-MikeB,

    you had mentioned that you got Run time error 5 due to line below.

    CellText = .Text & Space(ColWidth - Len(.Text))

    I try to test this line on my side and find that the value of "ColWidth" should be equal or greater then the value of "Len(.Text)"

    other wise you will get Run Time Error 5.

    please refer simple example below.

    below value I am referring from code.

    code:

    Sub demo2()
    Dim CellText As String
    Dim ColWidth As Integer
    ColWidth = 10
    With Selection.Cells(1, 1)
    CellText = .Text & Space(ColWidth - Len(.Text))
    Debug.Print (CellText)
    Debug.Print (Len(.Text))
    End With
    
    End Sub
    

    Output:

    so you can see that value of "Len(.Text)" =6.

    so if I set the value of "ColWidth " less then 6 , then I get error below.

    so now you know the root cause for the issue and you can try to correct it in your code to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, February 14, 2017 2:05 AM
    Moderator
  • Deepak - Good to know, thanks.  However, that was not the case with my data this time.  Regardless, I will keep that in mind for future runs.

    In the end, changing this line:

    CellText = .Text & Space(ColWidth - Len(.Text))

    ... to this:

    CellText = Selection.Cells(RowNum, ColNum).Text & Space(ColWidth - Len(Selection.Cells(RowNum, ColNum).Text))

    ... resolved the problem.  

    Basically, I ignored the WITH and just added the fully-qualified reference of, "Selection.Cells(RowNum, ColNum)" back to each instance of, ".Text" and the error went away.  Can anyone explain why?

    Tuesday, February 14, 2017 5:06 PM
  • Hey Deepak - I triple-checked and you were right, the problem was bad data that caused a negative value in the Space() function.  I had double-checked the data after I read your post and the data looked good.  Howerver, I just talked to the user and they told me that they noticed the bad data and changed it after I left, but didn't realize it was related to the error.

    As for why I thought that adding, "Selection.Cells(RowNum,ColNum)" to the ".Text" fixed the problem ... basically, my rusty VB6 skills didn't transfer to VBA very well.  Retracing my steps - First, I wanted to break down the line with the error by using the Immediates window but I had to use the fully-qualified reference in order to see the value of, ".Text".   Since I added it to Immediates, I temporarily added it to the Macro code for consistency while troubleshooting.  Second, I was examining the data in the spreadsheet being processed by the macro ... BUT ... I didn't know that by simply clicking a different cell in the sheet, I was also  changing the area of the, "Selection" object I was debugging.  I must've clicked a different cell to one with good data just before hitting F8 to attempt to run the line again.  When I got past the error, I thought all I had done was add the fully qualified reference to, ".Text". 

    Thanks for your help.

    • Marked as answer by COB-MikeB Tuesday, February 14, 2017 6:31 PM
    Tuesday, February 14, 2017 6:27 PM