0

I am a new programmer. I am programing in VBA. I am practicing writing a sub and a function.

I want the function to return a value each time it is called from the sub and the function return become the cell value. This is the code:

Sub Practice1()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim i As Integer
    Dim contador As Integer
    Dim salir As Boolean
    Dim FilePath As String
    Dim FileOpen As String
        
    
    FilePath = IsWBOpen("\\uscnap60\Depts\Global Procurement Organization\Supporting Documents\User's Backup Files\Gaspar Gomez\GESTION COMPRA DE MATERIALES 2023 - 2.xlsm")
    FileOpen = "GESTION COMPRA DE MATERIALES 2023 - 2.xlsm"
    
    If FilePath = True Then
    
        Workbooks("GESTION COMPRA DE MATERIALES 2023 - 2.xlsm").Activate
        MsgBox "File is Open"
        
    Else

        Workbooks.Open (FileOpen)
        
    End If
    
    i = 1
           
    Worksheets("Formulas").Activate
    'MsgBox ("Worksheet Formulas")
    Worksheets("Formulas").Range("a1").Select
    'MsgBox ("rango A1 seleccionado")
    
    Do While True
           
       
                     
        If Cells(i, 1).Value = "hola" Then
                        
            Worksheets("Formulas").Cells(i, 1).Select
            MsgBox ("llegamos al final")
            Exit Do
            
        Else
            
            contador = mathop(i, i)
            'MsgBox ("Valor de Contador e I =" & contador & " " & i)
            Worksheets("Formulas").Cells(i, 1).Select
            Worksheets("Formulas").Cells(i, 1).Value = contador
             
            
        End If
        
        i = i + 1
        
    Loop
    
End Sub

Function mathop(num1 As Integer, num2 As Integer) As Integer
     
    num1 = i
    num2 = num1 + 2
    
    mathop = num1 + num2
    
    'MsgBox ("Valor retornado de mathop = " & mathop)

End Function
braX
  • 11,506
  • 5
  • 20
  • 33
sendog3c
  • 1
  • 1
  • 6
    `i` within `mathop` is not the same as `i` within `Practice1`. It'd be useful to read about variable scope. – BigBen May 19 '23 at 20:21
  • Not what you are asking about, but it's a good idea to [void using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code – cybernetic.nomad May 19 '23 at 20:23
  • thank for the answers. But how can I do to set a range of cells based on certain cell values without go one by one?, and without using select also. – sendog3c May 20 '23 at 02:15
  • [Here](https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/writing-a-function-procedure) is a description of function calling and declaring. – Black cat May 20 '23 at 09:14

0 Answers0