Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557

    Unanswered: Basic VBA Help for Excel!

    I have used VBA in Access quite alot. I know how to get to the VBA editor in excel (by pressing F11), but the problem is I cant get a cell to use the function I have written, and I also dont know how to return a value to a cell.

    Please help,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Quote Originally Posted by JSThePatriot
    I have used VBA in Access quite alot. I know how to get to the VBA editor in excel (by pressing F11), but the problem is I cant get a cell to use the function I have written, and I also dont know how to return a value to a cell.

    Please help,
    JS
    With the first part, without seeing your code, there is no way we can help. Can you post it?

    Did you put the code in Private or Public?

    For value to a cell something like:

    Workbooks("Book2").Worksheets("Sheet1").Range("A2" ).Value = 20

    Again, without specifics, it is hard to know what you are doing.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    He wants to create a function and use it in the workbooK. For instance:

    Function MultByTen(i as long) as long
    MultByTen = i * 10
    End function


    then reference it in the workbook in a cell like:

    =MultByTen(3.145)


    I think you have to save the code or book with the function as an add in, then reference it in you new workbook.

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi TerpInMD

    You function work OK when pasted into a MODULE, they do not seem to work in then ThisWorkbook object module.

    ie typing

    =MultByTen(A1)

    in any cell (Except A1 !).

    My only other comment is that you have declared both i as long AND the function as long, therfore MultByTen(3.145)=30 !!??.

    HTH

    MTB

  5. #5
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Wow I am loving all of these replies... not sure if they help. I dont really need much help with the coding, but I will need some I am sure.

    My first problem is making a function actually return a value to a cell.

    I have a form that has a start date, an end date, and days in progress.

    The start date will be entered automatically. The end date may be blank until we end the project, but we want the running total of days (- weekends so every 6 days it should subtract 1 and at 7 it should subtact 2 ect). I can make just basic excel date calculations do everything but the day subtraction. :-/

    So any more help would be wonderful if you understand what I am trying to get at.

    To be more acurate it would be good if it knew it was a weekend if it was already only 3 days into the project... so I dunno.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  6. #6
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    This is the code I have so far... I am going to have to change it to a loop... because I need this to check the total days and divide it by 7... Take that number and multiply it by 2, Then subtract the number after multiplying from total days.

    Example:
    Total Days = 34
    TD/7 = 4 (and a fraction but I dont need that)
    4 * 2 = 8 (the reason for 2 is because we dont work on weekends)
    TD - 8 = 26

    So I want my cell D19 to show that days in production = 26.

    Here is the code I am currently using... and it works if the project doesnt last more than 4 weeks. I want to be able to track a project if it lasts for 2 to 3 years. Which leads me to believe I need to do some kind of math... to check the total days and so on... Maybe I can do it from what I have above. I will post back if I get it done.


    Code:
    Function Days(i As Long) As Long
    Select Case i
        Case Is > 27
            Days = i - 8
            Days = Days + 1
        Case Is > 20
            Days = i - 6
            Days = Days + 1
        Case Is > 13
            Days = i - 4
            Days = Days + 1
        Case Is > 6
            Days = i - 2
            Days = Days + 1
        Case Else
            Days = i
    End Select
    
    End Function
    Thanks for everyone's posts,
    JS
    Last edited by JSThePatriot; 06-03-04 at 15:12.
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  7. #7
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    Put the VBA away. Based on what I've read, this is all you need:

    A1: 5/15/2003
    A2: 4/28/2004
    A3: =NETWORKDAYS(A1,IF(A2="",TODAY(),A2))

    Read the help on NETWORKDAYS. It'll tell you how to deal with holidays as well.

  8. #8
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Quote Originally Posted by actuary
    Put the VBA away. Based on what I've read, this is all you need:

    A1: 5/15/2003
    A2: 4/28/2004
    A3: =NETWORKDAYS(A1,IF(A2="",TODAY(),A2))

    Read the help on NETWORKDAYS. It'll tell you how to deal with holidays as well.

    That would be wonderful if it works... I have my VB working it just doesnt exclude any holidays as of right now... It does exclude weekends...
    I couldnt find Networkdays in my function list. I tried just typing it in and I got the #Name? error.

    Hrm any more help would be appreciated.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  9. #9
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    A quick google of networkdays yields this promising info:

    http://www.igetit.net/newsletters/Y02_04/NWD.htm

  10. #10
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Okay... The NETWORKDAYS() doesnt bring back anything for me. It just gives me the #NAME? error.

    Thanks,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  11. #11
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    Were you able to reference the Analysis Pack Add-in?

Posting Permissions

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