Basic VBA Help for Excel!

JSThePatriot · #1 (**permalink**) 06-02-04, 12:34

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

shades · #2 (**permalink**) 06-02-04, 13:46

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.

TerpInMD · #3 (**permalink**) 06-03-04, 08:30

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.

MikeTheBike · #4 (**permalink**) 06-03-04, 08:46

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

JSThePatriot · #5 (**permalink**) 06-03-04, 11:52

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

JSThePatriot · #6 (**permalink**) 06-03-04, 14:04

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

actuary · #7 (**permalink**) 06-03-04, 14:46

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.

JSThePatriot · #8 (**permalink**) 06-03-04, 16:31

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