Jump to content

Replace goto vba


Recommended Posts

Hi all,

I have a VBA code, I must translate this VBA code with Autoit. I have many Goto line in my VBA code but there isn't the goto function in autoit. Is there an alternative ?

It's my VBA code

    Selection.EntireRow.Insert
    b = Selection.Row
    
    Dim a As String
    
    a = 2
    
line1:
    If Cells(a, 1) <> "" Then
		'########### Code ########
        a = a + 1
        GoTo line1
    Else
        If a = b Then
			'########### Code ########
            a = a + 1
            GoTo line1
        End If
    End If

Thanks in advance

Qui ose gagneWho Dares Win[left]CyberExploit[/left]

Link to comment
Share on other sites

I'd assume something like this

;~ line1:
Global $a = 0
While 1
     If Cells(a, 1) <> "" Then ; assuming this (Cells) is a sub or function?
          ;########### Code ########
;~         a = a + 1
          $a += 1
;~         GoTo line1
     Else
          If $a = $b Then
               ;########### Code ########
;~             a = a + 1
               $a += 1
;~             GoTo line1
          EndIf
     EndIf
WEnd

I'm not sure what you want done  in the flow of the script if Cels(a, 1) <> "" and if $a doesn't equal $b, I'd assume you want it to keep running past the end of this code, so  you'd need an ExitLoop at that point, probably as an Else in the If $a = $b comparison? 

Edited by BrewManNH

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Link to comment
Share on other sites

Usage of GoTo is a terribly bad programming practice. I remember when I first started programming in Fortran (that tells a lot of my age) ppl were using it everywhere.  So it made software maintenance a nightmare.  Glad that autoit has not implemented that archaic statement. :)

Link to comment
Share on other sites

4 minutes ago, Nine said:

Usage of GoTo is a terribly bad programming practice. I remember when I first started programming in Fortran (that tells a lot of my age) ppl were using it everywhere.  So it made software maintenance a nightmare.  Glad that autoit has not implemented that archaic statement. :)

I agree with you !!!! Completely 😃

I must take all the VBA code and translate it with Autoit. I have many times Goto 😡

I have a lot of functions that I do not know how to replace the "Goto"

Qui ose gagneWho Dares Win[left]CyberExploit[/left]

Link to comment
Share on other sites

11 minutes ago, jerem488 said:

I have a lot of functions that I do not know how to replace the "Goto"

Then you need to understand the logic of the function, and redesign it without it...

Link to comment
Share on other sites

Hi jerem488 :)
Though the following code will not run "as-is", it should show a pattern to solve your issue.
I assume your VBA code is for Excel, so with the help of some VBA forums (and Autoit help file), here is a way to bypass the "Goto line" in your VBA script :

; a = 2
$iStartRow = 2 ; line 1 is probably filled with Excel column titles

; Cells(a, 1)
; "Refers to the cell indicated by its coordinates (row, column)" => _Excel_RangeRead() in AutoIt

; Selection.EntireRow.Insert
; "This command will add an entire row before the selected row" => _Excel_RangeInsert() in AutoIt

; Selection.Row
; "Gives the row number of the current selection" => $oExcel.ActiveCell.Row in AutoIt
$iInsertRow = $oExcel.ActiveCell.Row ; $oExcel to be defined before, also $oWorkbook etc...

; Last row already filled in the worksheet (thanks Jfish)
$iLastRow = $oWorkbook.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row

For $iRow = $iStartRow To $iLastRow ; loop on each row, from row 2 to the last filled row
    If _Excel_RangeRead(...) <> "" Then ; if content of 1st cell in this row is not empty then...
        Code_Row()
    Else
        If $iRow = $iInsertRow Then
            Code_InsertRow()
        EndIf
    EndIf
Next

Func Code_Row()     
    ; ########### Code for already filled rows (before and after inserted blank one) ########
EndFunc
        
Func Code_InsertRow()       
    ; ########### Code for inserted blank row ########
EndFunc

As BrewManNH wrote, where should this loop stop ?
I guess the loop shouldn't stop at the inserted blank row ($iInsertedRow in the script) because the user has probably inserted a blank row in the middle of filled rows in an Excel sheet and he wants them all processed (looking at his VBA script)

Hope this helps as I'm a newbie in Excel automation.
Good luck :)

Edit: I just edited this post because I found 1 line of code that retrieves the last filled row of a worksheet, which should be very helpful in this script. Thanks to Jfish for this :

https://www.autoitscript.com/forum/topic/166468-finding-last-row-in-an-excel-column/?do=findComment&comment=1215890

Edited by pixelsearch
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...