VBA virgin needs Excel help please *RESOLVED* with thanks Frans especially

[Spooner]

Gang-

I've never ever used VBA so I don't even know where to start.

I think I need VBA because I can't see the function I need as a normal Excel function: it's VB's DateAdd I want to emulate.

I wan't to take a date which will be in one cell, and add a value to it, and bung the result in another cell.

Can someone advise please. (If there is a way of doing this in Excel itself, as opposed to using VBA, that's cool too.)

[ITboy]

What value do u want to add? Can u xplain ur ques more clearly..I'll try to help u if I can manage...coz I've done dispalying of time dynamically in Excel..but i don't know how u want it to be done...

[Spooner]

Well let's say I need to calculate an expiry date from a start date. Like in a library for instance, the return date might be 2 weeks from today, so I would add 14 days to today's date.

In normal VB I would use the DateAdd function, where I would specify "d" to show I'm working in days, 14 to be the value to add, and Date meaning today's date from the system, thus:

Private Sub Form_Load()
Debug.Print DateAdd("d", 14, Date)
End Sub

for example.

In Excel, I want to have a start date in one cell (it'll be there already), and I want to do a DateAdd to that date, with a value like the 14 above.

Something like this in pseudocode:

'pseudocode
Cell(A2) =  DateAdd("d", 14, Cell(A1))

But as I say I know zip about using VBA. According to the Object Browser, DateAdd is a VBA function; I just don't know how to code VBA in Excel, and in so doing to access the content of another cell.

[Frans C]

Well, you can't use VBA functions in your worksheet, but you can use functions from a module. So we could make a wrapper for the dateadd function.

Add a module to your project, and paste this code.
To avoid confusion i named the function AddDate .

Public Function AddDate(ByVal Interval As String, ByVal Number As Integer, ByVal MyDate As Date) As Date
    AddDate = DateAdd(Interval, Number, MyDate)
End Function

Now we can use the AddDate function in a cell.
for example: enter the following expression in a cell, and put a date in cell A1, and a number in cell B1

=adddate("d",B1,A1)

[swatty]

What is wrong with

=A2 + 14

This you can put in the = section of the cell

[Frans C]

If you always want to add days (interval = "d"), you can use swatty's approach just as well.

[opus]

Why don't you just use EXCLE like
Formula: =A1+14

Asuming that Cell A1 had a date, The Cell holding that formula will have date 14 days later!
Isn't that the thing you wanted?

[Spooner]

Hmmm... I hadn't thought of just doing arithmetic on a date to be honest Swatty. That was too simple!

But let's say I want to go the VBA approach. (I need to know how this VBA stuff works anyway, you see!)

So that looks simple enough Frans... just one question: You say I should add a module to my project. Do you mean a normal VB project, and if so then how does Excel know about it, or do I add a module directly to Excel. (Excuse the absolute ignorance here, please!)


Thanks guys.

[Spooner]

Ok, I found where to add a module in the VB editor inside Excel, so I should be fine now good people!

[Frans C]

I meant you need to add a module to your excel project.
Open your excel workbook, and press ALT + F11. The VBA window will open. Now add a module to this excel project (you could use an existing module just as well, but if you didn't record a macro yet there will be no existing module).
You can add a module with the menu (Insert --> Module), or you can right click the project in the project explorer (the little window with the treeview), and select Insert --> Module.