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

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.)

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...:rolleyes:

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:

VB Code:

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:

Code:

---

'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.

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 .

VB Code:

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)

What is wrong with

VB Code:

=A2 + 14

This you can put in the = section of the cell

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

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?

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.

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

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.