none
Help me with my doubts in VBA... RRS feed

  • Question

  • Guys will u please let me know the function (in vba) equivalent to the "roundup" fn available in excell

    i.e i need to round up a value may it be 3.00009 or 3.99999999 the result should be ---> 4

    i can make it up with an if statement but it looks handy with a roundup function



    Secondly I want to create different modules of a program that i have coded into a command button click.
    my purpose is that, the module1 i have created should access the variables declared under the userforum---->cmd_clk
    and also the value i got from the module1 should be accessible to the userforum-->cmd_clk

    Eg.
    under userform---> cmd_clk
    dim a as integer
    dim b as integer
    dim c as integer

    a=a1.text ' a1 is the text box used in userform
    call m1

    c=a+b 'the value of b should be got from module1

    Msgbox(c)

    end sub

    under ---> Module1

    **** m1()

    b=2*a 'b should access the declaration what i made in the cmd_clk
    msgbox(b)

    end sub


    I hope you understand my question.It may be silly for u guys...but i'm new to these coding and stuffs...please just tolerate this..

    waiting for your reply.
    Manoj Kumar D
    Sunday, November 20, 2011 6:54 AM

Answers

  • Many functions (But not all) that are used in a worksheet are available in VBA. Simply use WorksheetFunction as follows:

    Dim lngResult As Long
    lngResult = WorksheetFunction.RoundUp(3.00009, 0)

    I am not sure that I understand your second question properly but if you want to use variables between modules then at the top of a standard module before any subs, declare the variables as public.

    Public strVariable As String

    If you want to access code that is in a standard module then use Call and call the sub from the sub in the Userform module.

    Call Test     'Where Test is the name of the sub.

    Also see in Help re passing Arguments (parameters) to a called routine.

     


    Regards, OssieMac
    Sunday, November 20, 2011 7:49 AM

All replies

  • Many functions (But not all) that are used in a worksheet are available in VBA. Simply use WorksheetFunction as follows:

    Dim lngResult As Long
    lngResult = WorksheetFunction.RoundUp(3.00009, 0)

    I am not sure that I understand your second question properly but if you want to use variables between modules then at the top of a standard module before any subs, declare the variables as public.

    Public strVariable As String

    If you want to access code that is in a standard module then use Call and call the sub from the sub in the Userform module.

    Call Test     'Where Test is the name of the sub.

    Also see in Help re passing Arguments (parameters) to a called routine.

     


    Regards, OssieMac
    Sunday, November 20, 2011 7:49 AM
  • actually i don't want to call the entire code inn that module...but want to get a value from that module....
    Manoj Kumar D
    Sunday, November 20, 2011 10:20 AM