Results 1 to 12 of 12

Thread: function in visual basic

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2013
    Posts
    16

    function in visual basic

    Hi, i wrote a procedure and function. I transfer this function into procedure. If i try execute procedure i have an error: argument not optional on this function.

    Code:
    Public Function inserthgbstshow(level As String, relation As Long) As String
    
        Dim funObjid As Long
        funObjid_show = pobierz_objid(ptype_id_inserthgbstshow)
        
        conn.Execute "insert into table_hgbst_show(OBJID, LAST_MOD_TIME, TITLE, DEV_VAL, DEV, CHLD_PRNT2HGBST_SHOW) values (" + funObjid_show + ", to date(1753-01-01), '" + level + "', 0, "", " + relation + ")"
        inserthgbstshow = funObjid_show
    End Function

    Code:
    Sub Zaimportuj()
        Dim objid_show_level_1 As Long
        Dim objid_show_level_2 As Long
        Dim objid_show_level_3 As Long
        Dim objid_show_level_4 As Long
        Dim objid_show_level_5 As Long
        Dim last_name_level_1 As String
        Dim last_name_level_2 As String
        Dim last_name_level_3 As String
        Dim last_name_level_4 As String
        Dim last_name_level_5 As String
        
        objid_show_level_1 = pobierz_list
        
        delete_dane
        
        last_name_level_1 = ""
        last_name_level_2 = ""
        last_name_level_3 = ""
        last_name_level_4 = ""
        last_name_level_5 = ""
        
        Dim c As Object
        
    With Sheets(1).Range("A:A")
    Set c = .Find(what:="*", LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByRows, searchdirection:=xlPrevious)
    End With
    MsgBox ("Ostatni zapisany wiersz to: " & c.Row)
    Dim x As Integer
    x = 3
    For x = 3 To c.Row
        If Range("B" + CStr(x)) <> "" Then
            
            inserthgbstelement
            inserthgbstelm0hgbstshow1
            
        If Range("D" + CStr(x)) <> "" Then
            If last_name_level_1 <> Range("A" + CStr(x)) Then
                objid_show_level_2 = inserthgbstshow
            End If
            inserthgbstelement
            inserthgbstelm0hgbstshow1
        
        If Range("F" + CStr(x)) <> "" Then
            If last_name_level_2 <> Range("C" + CStr(x)) Then
                objid_show_level_3 = inserthgbstshow
            End If
            inserthgbstelement
            inserthgbstelm0hgbstshow1
        
        If Range("H" + CStr(x)) <> "" Then
            If last_name_level_3 <> Range("E" + CStr(x)) Then
                objid_show_level_4 = inserthgbstshow
            End If
            inserthgbstelement
            inserthgbstelm0hgbstshow1
        
        If Range("J" + CStr(x)) <> "" Then
            If last_name_level_4 <> Range("G" + CStr(x)) Then
                objid_show_level_5 = inserthgbstshow
            End If
            inserthgbstelement
            inserthgbstelm0hgbstshow1
                    
                    End If
                 End If
            End If
        End If
        
        last_name_level_1 = Range("A" + CStr(x))
        last_name_level_2 = Range("C" + CStr(x))
        last_name_level_3 = Range("E" + CStr(x))
        last_name_level_4 = Range("G" + CStr(x))
        last_name_level_5 = Range("I" + CStr(x))
    End If
    Next x
    End Sub

  2. #2
    PowerPoster
    Join Date
    Sep 2006
    Location
    Egypt
    Posts
    2,579

    Re: function in visual basic

    This line is wrong objid_show_level_2 = inserthgbstshow because according to the function inserthgbstshow declaration, it is require to pass two params, e.g.
    Code:
    objid_show_level_2 = inserthgbstshow(2, "relation")



  3. #3

    Thread Starter
    Junior Member
    Join Date
    Apr 2013
    Posts
    16

    Re: function in visual basic

    thanks you for reply i have another small problem.
    Could you tell me why this line is wrong? inserthgbstelement is a function:

    Code:
    Public Function inserthgbstelement(title As String, status As String, rank As Integer) As String
    
        Dim funObjid As Long
        funObjid_elm = pobierz_objid(ptype_id_inserthgbstelement)
        
        conn.Execute "insert into table_hgbst_elm(OBJID, TITLE, S_TITLE, RANK, STATE, DEV, INTVAL) values (" + funObjid_elm + ", '" + title + "', Upper('" + title + "'), " + rank + ", '" + status + "', "",0)"
        inserthgbstelement = funObjid_elm
    End Function
    Code:
    Sub Zaimportuj()
        Dim objid_show_level_1 As Long
        Dim objid_show_level_2 As Long
        Dim objid_show_level_3 As Long
        Dim objid_show_level_4 As Long
        Dim objid_show_level_5 As Long
        Dim last_name_level_1 As String
        Dim last_name_level_2 As String
        Dim last_name_level_3 As String
        Dim last_name_level_4 As String
        Dim last_name_level_5 As String
        
        objid_show_level_1 = pobierz_list
    
        delete_dane
        
        last_name_level_1 = ""
        last_name_level_2 = ""
        last_name_level_3 = ""
        last_name_level_4 = ""
        last_name_level_5 = ""
        rank_level_1 = 0
        rank_level_2 = 0
        rank_level_3 = 0
        rank_level_4 = 0
        rank_level_5 = 0
        Dim c As Object
    
    With Sheets(1).Range("A:A")
    Set c = .Find(what:="*", LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByRows, searchdirection:=xlPrevious)
    End With
    MsgBox ("Ostatni zapisany wiersz to: " & c.Row)
    Dim x As Integer
    x = 3
    For x = 3 To c.Row
        If Range("B" + CStr(x)) <> "" Then
            
            inserthgbstelement("Range("A" + CStr(x))","Range("B" + CStr(x))",-3 + cstr(x))
            inserthgbstelm0hgbstshow1
            
        If Range("D" + CStr(x)) <> "" Then
            If last_name_level_1 <> Range("A" + CStr(x)) Then
                objid_show_level_2 = inserthgbstshow("Range("A2:A2")",
            End If
            inserthgbstelement("Range("C" + CStr(x))","Range("D" + CStr(x))",)
            inserthgbstelm0hgbstshow1
        
        If Range("F" + CStr(x)) <> "" Then
            If last_name_level_2 <> Range("C" + CStr(x)) Then
                objid_show_level_3 = inserthgbstshow("Range("C2:C2")",)
            End If
            inserthgbstelement("Range("E" + CStr(x))","Range("F" + CStr(x))",)
            inserthgbstelm0hgbstshow1
        
        If Range("H" + CStr(x)) <> "" Then
            If last_name_level_3 <> Range("E" + CStr(x)) Then
                objid_show_level_4 = inserthgbstshow("Range("E2:E2")",)
            End If
            inserthgbstelement("Range("G" + CStr(x))","Range("H" + CStr(x))",)
            inserthgbstelm0hgbstshow1
        
        If Range("J" + CStr(x)) <> "" Then
            If last_name_level_4 <> Range("G" + CStr(x)) Then
                objid_show_level_5 = inserthgbstshow("Range("G2:G2")",)
            End If
            inserthgbstelement("Range("I" + CStr(x))","Range("J" + CStr(x))",)
            inserthgbstelm0hgbstshow1
                    
                    End If
                 End If
            End If
        End If
        
        last_name_level_1 = Range("A" + CStr(x))
        last_name_level_2 = Range("C" + CStr(x))
        last_name_level_3 = Range("E" + CStr(x))
        last_name_level_4 = Range("G" + CStr(x))
        last_name_level_5 = Range("I" + CStr(x))
    End If
    Next x
    End Sub

    ERROR at thi line: syntax error



    Code:
    inserthgbstelement("Range("A" + CStr(x))","Range("B" + CStr(x))",-3 + cstr(x))

  4. #4
    PowerPoster
    Join Date
    Sep 2006
    Location
    Egypt
    Posts
    2,579

    Re: function in visual basic

    I think it should be
    Code:
    inserthgbstelement(Range("A" + CStr(x)), Range("B" + CStr(x)), -3 + cstr(x))



  5. #5

    Thread Starter
    Junior Member
    Join Date
    Apr 2013
    Posts
    16

    Re: function in visual basic

    You have right byt why Visual basic tell mi that : "expected = " on this function in this line?


    Code:
     dim y as long
    Code:
    y = inserthgbstelement(Range("A" + CStr(x)), Range("B" + CStr(x)), -3 + CStr(x))
    i wrote like that and its ok, but i don't understand why... Can you tell me?
    Last edited by kaczak1108; May 9th, 2013 at 08:01 AM.

  6. #6
    PowerPoster
    Join Date
    Sep 2006
    Location
    Egypt
    Posts
    2,579

    Re: function in visual basic

    If you call a function without assign the returned value to a variable, you must remove ()

    Code:
    inserthgbstelement Range("A" + CStr(x)), Range("B" + CStr(x)), -3 + CStr(x)
    and add them only when assign the returned value to a variable
    Code:
    v = inserthgbstelement (Range("A" + CStr(x)), Range("B" + CStr(x)), -3 + CStr(x))



  7. #7

    Thread Starter
    Junior Member
    Join Date
    Apr 2013
    Posts
    16

    Re: function in visual basic

    ok thanks, I understand it now !

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Apr 2013
    Posts
    16

    Re: function in visual basic

    I have another problem: in function i wrote that type must be long and in parameters.

    Function:
    Code:
    Function inserthgbstelm0hgbstshow1(funObjid_elm As Long, funObjid_show As Long) As Long
       
        Dim funhgbst_elm2hgbst_show As Long
        
         conn.Execute "insert into table mtm_hgbst_elm0_hgbst_show1(hgbst_elm2hgbst_show, hgbst_show2hgbst_elm) values (" + funObjid_elm + ", " + funObjid_show + ")"
        inserthgbstelm0hgbstshow1 = funhgbst_elm2hgbst_show
    End Function

    Code:
     For x = 3 To c.Row
        If Range("B" + CStr(x)) <> "" Then
            
            inserthgbstelement("Range("A" + CStr(x))","Range("B" + CStr(x))",-3 + cstr(x))
            inserthgbstelm0hgbstshow1
    Code:
     inserthgbstelm0hgbstshow1 funObjid_elm, funObjid_show

    On this line i get an error: by ref argument type mismatch

  9. #9
    PowerPoster
    Join Date
    Sep 2006
    Location
    Egypt
    Posts
    2,579

    Re: function in visual basic

    What the type of funObjid_elm and funObjid_show? they should be Long



  10. #10

    Thread Starter
    Junior Member
    Join Date
    Apr 2013
    Posts
    16

    Re: function in visual basic

    Of course, and they are in type Long
    Code:
    Public Function inserthgbstelement(title As String, status As String, rank As Integer) As String
    
        Dim funObjid_elm As Long  !!!!!! there is type
        funObjid_elm = pobierz_objid(ptype_id_inserthgbstelement)
        
        conn.Execute "insert into table_hgbst_elm(OBJID, TITLE, S_TITLE, RANK, STATE, DEV, INTVAL) values (" + funObjid_elm + ", '" + title + "', Upper('" + title + "'), " + rank + ", '" + status + "', "",0)"
        inserthgbstelement = funObjid_elm
    End Function
    Code:
     Public Function inserthgbstshow(level As String, relation As Long) As String
    
        Dim funObjid_show As Long    !!!!!! there is type
        funObjid_show = pobierz_objid(ptype_id_inserthgbstshow)
        
        conn.Execute "insert into table_hgbst_show(OBJID, LAST_MOD_TIME, TITLE, DEV_VAL, DEV, CHLD_PRNT2HGBST_SHOW) values (" + funObjid_show + ", to date(1753-01-01), '" + level + "', 0, "", " + relation + ")"
        inserthgbstshow = funObjid_show
    End Function
    and i don't know why i get error msg

  11. #11
    PowerPoster
    Join Date
    Sep 2006
    Location
    Egypt
    Posts
    2,579

    Re: function in visual basic

    funObjid_show variable is declared inside a function, so it is invisible outside that function.

    You must add Option Explicit at the first line of the code, it is important to warn you about undeclared variable.



  12. #12

    Thread Starter
    Junior Member
    Join Date
    Apr 2013
    Posts
    16

    Re: function in visual basic

    i resolve this problem like that: i use data type converter
    Code:
     inserthgbstelm0hgbstshow1 clng(funObjid_elm), clng(funObjid_show)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width