Using The Exit Sub Statement in VBA

Question

I have a code where I'm basically looping through and copying data from one excel sheet and pasting it into my active workbook. However, the current way I have my code written I'm using the Exit Sub statement and everything below that statement will not execute. Is there an alternative I can use to the Exit Sub which will still work with my current code structure?

Dim ws As Worksheet

Dim Prefix As String, Suffix As String
Dim ROfs As Integer, COfs As Integer
Dim Source As Range, Dest As Range, This As Range


Set ws = ThisWorkbook.Worksheets("Sheet1")



  With ws
    'The formula becomes Prefix & Source.Address & Suffix
    Prefix = "='C:x\xx\[Workbook1.xls]Worksheet1'!"
    Suffix = "/1000"
    'Direction for the next Source cell
    COfs = -1

    'Destination cells
    Set Dest = .Range("G8:G12")
    Set Source = Range("G8")
    GoSub Fill

    Set Dest = .Range("G13:G17")
    Set Source = Range("G9")
    GoSub Fill
    
    Suffix = ""
    Set Dest = .Range("G3:G7")
    Set Source = Range("G10")
    GoSub Fill

    Suffix = "/1000"
    Set Dest = .Range("G26:G30")
    Set Source = Range("G35")
    GoSub Fill
   
    Suffix = ""
    Set Dest = .Range("G21:G25")
    Set Source = Range("G37")
    GoSub Fill
   
    Suffix = "/1000"
    Set Dest = .Range("G31:G35")
    Set Source = Range("G36")
    GoSub Fill
    
  End With
  
Exit Sub
 
Fill:
  For Each This In Dest
    This.Formula = Prefix & Source.Address(0, 0) & Suffix
    Set Source = Source.Offset(ROfs, COfs)
  Next
  Dest.Value = Dest.Value
  Return

'''Everything Below Does Not Execute'''

ws.Columns("C").EntireColumn.Delete

Dim strFormulas As Variant

    With ws
         strFormulas = "=(F3-C3)"
        .Range("G3:G17").Formula = strFormulas
        .Range("G3:G17").FillDown
   End With

End Sub

I use that to get rid of the cell reference. It's essentially the same as Paste as Values — a_js12
– a_js12, Commented Aug 25, 2020 at 6:03
I would make Fill: block a separate sub that takes args. It will make your code a lot easier to read. You could likely then remove your Exit Sub. — QHarr
– QHarr, Commented Aug 25, 2020 at 6:40
GoSub is a hangover from a much earlier era of Basic. Don't use it. Ever. — chris neilsen
– chris neilsen, Commented Aug 25, 2020 at 7:15

Tim Williams · Accepted Answer · 2020-08-25 07:21:57Z

2

Without Gosub/Return - as noted in the comments, that's not an "idiomatic" way to write in VBA

Sub Test()
    
    Dim prefix As String, suffix As String
    
    With ThisWorkbook.Worksheets("Sheet1")
        'The formula becomes Prefix & Source.Address & Suffix
        prefix = "='C:x\xx\[Workbook1.xls]Worksheet1'!"
        suffix = "/1000"
        
        'Destination cells
        DoFill Range("G8"), .Range("G8:G12"), prefix, suffix
        DoFill Range("G9"), .Range("G13:G17"), prefix, suffix
        DoFill Range("G10"), .Range("G3:G7"), prefix, ""
        DoFill Range("G35"), .Range("G26:G30"), prefix, suffix
        DoFill Range("G37"), .Range("G21:G25"), prefix, ""
        DoFill Range("G36"), .Range("G31:G35"), prefix, suffix
    
        .Columns("C").EntireColumn.Delete
        .Range("G3:G17").Formula = "=(F3-C3)"
    End With
      
End Sub

Sub DoFill(src As Range, dest As Range, prefix As String, suffix As String)
      Dim c As Range
      For Each c In dest
        c.Formula = prefix & src.Address(0, 0) & suffix
        Set src = src.Offset(0, -1)
      Next
      dest.Value = dest.Value
End Sub

answered Aug 25, 2020 at 7:21

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

a_js12 Over a year ago

Thanks so much for your help. This makes a lot more sense than what I was doing.

Gustav · Accepted Answer · 2020-08-25 06:38:35Z

0

Shuffle the last code blocks:

' <snip>

  End With
  
ws.Columns("C").EntireColumn.Delete

Dim strFormulas As Variant

    With ws
         strFormulas = "=(F3-C3)"
        .Range("G3:G17").Formula = strFormulas
        .Range("G3:G17").FillDown
   End With

Exit Sub
 

Fill:
  For Each This In Dest
    This.Formula = Prefix & Source.Address(0, 0) & Suffix
    Set Source = Source.Offset(ROfs, COfs)
  Next
  Dest.Value = Dest.Value
  Return

End Sub

answered Aug 25, 2020 at 6:38

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Comments

Javier Martin Gil · Accepted Answer · 2020-08-25 09:47:19Z

0

Dont use a "goto" for that, is better to call a function or sub.

The "goto" are perfect in vba for catch errors

answered Aug 25, 2020 at 9:47

Javier Martin Gil

363 bronze badges

Collectives™ on Stack Overflow

Using The Exit Sub Statement in VBA

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related