Help with Visual Basic for Excel?

[Grimey]

I have a project for school where I have to build a Financial model to find several Net Present Values and I had never used VBA before this class. It is pretty basic stuff, but I am just stuck in a few places.

Anyone have some knowledge they wouldn't mind sharing?:)

[IronSooner]

I'd never had VB but had to work on a project where I edited macros. In Excel you can just start a macro, do what you want, and it'll record it in VB. Occasionally I'd record smaller macros to do one or two things, then open up the editor and see what code it wrote and try to figure things out that way. Otherwise I'm not too skilled with the computer programming.

[Ike]

I know nothing of VB. I have done exactly the kind of things that Iron mentioned though. Personally, I'd recommend dropping Excel from the whole damn thing and use a language you are already comfortable with. Pretty graphics are overrated.

[Grimey]

I have never done programming before, so this really is like learning a foreign language.

I've got to use Excel. That is the whole point of the project...

[Vaevictis]

Is VBA required? If not, you might want to avoid it.

VB causes irreparable brain damage in people who have never programmed before.

[NormanPride]

What do you need to do? VB in Excel is different from just plain VB in a few areas... Are you pulling data from a database or just another sheet?

I've never worked with VB in Excel, but I did a bunch in Access.

[Ike]

I have never done programming before, so this really is like learning a foreign language.

I've got to use Excel. That is the whole point of the project...


egads...I hate projects like that. get a book I guess.

Seriously, I want to throat punch professors who would rather teach you how to use a particular tool (calculators, pre-genned software like excel, etc) than teach, you know, the subject they are supposed to be teaching.

anyone can learn on their own how to use a tool.

[Grimey]

It really is probably a simple project to anyone who has ever done VBA in excel. There are just a couple of places I am getting stuck on. I was looking to get my cheat on. But you dorks are no help:)

We definitely have to use VBA and Excel though. That is pretty much the point of the whole class. That and later we will learn something called XBRL(?) and Monte Carlo simulations

[Vaevictis]

Meh, like I say, brain damage.

There are very few people who actually *want* to program in VB, for good reason :/

Sorry to be of no help. I'm too busy trying to get a @#%#*^!#*(&$~#!@$&!@*@~ VB ActiveX control to integrate into my C++ program.

[Grimey]

brain damage is right.

At least I can cross 'programmer' off my list of possible career options.

[Vaevictis]

You got that right.

People who start out programming in any language containing the word "basic" usually have to spend several years undoing the damage.

[jkm, the stolen pifwafwi]

what are you stuck on?

[sends up the norm signal]

[Grimey]

there you are. about time!!

I am prepared to be humbled, because I am sure it is a stupid question. Basically I am trying to write a function to calculate NPV in one module, then call that function in the next module. Sounds simple enough, yet I cannot get the function to work right. There are more details that make it more complex, but I can get into those if you don't mind helping.

[Ike]

It really is probably a simple project to anyone who has ever done VBA in excel. There are just a couple of places I am getting stuck on. I was looking to get my cheat on. But you dorks are no help:)

We definitely have to use VBA and Excel though. That is pretty much the point of the whole class. That and later we will learn something called XBRL(?) and Monte Carlo simulations


monte carlo is useful (we use it in particle physics all the time), but trust me, its much much better to write your own MC than to use some pre-packaged thing that you have only a nebulous understanding of....either that, or be prepared to spend a long time reading documentation to make sure your pre-packaged MC program is really doing what you think it should be doing.

[Grimey]

I have a feeling I am going to come out of this thread feeling much dumber than I did before.

[NormanPride]

I have a feeling I am going to come out of this thread feeling much dumber than I did before.

You're on the SO, so of course you will.

[Grimey]

that could be the S. Oval motto.

So, no one wants to help me???

[jkm, the stolen pifwafwi]

there you are. about time!!

I am prepared to be humbled, because I am sure it is a stupid question. Basically I am trying to write a function to calculate NPV in one module, then call that function in the next module. Sounds simple enough, yet I cannot get the function to work right. There are more details that make it more complex, but I can get into those if you don't mind helping.

can i ask why? excel has an NPV calculation in it...

[Grimey]

He put in more variables than a standard NPV function. Plus, there are to be low and high variables to produce a range of NPV's

[sooner_born_1960]

I think you should post the actual assignment. You'd probably get a lot more help.

[jkm, the stolen pifwafwi]

Function Yo(rate As Double, values As Variant) As Double
Yo = NPV(rate, values)
End Function

Sub callnpv()
Dim grimey(4) As Double
grimey(0) = 1
grimey(1) = 2
grimey(2) = 5
grimey(3) = 3

Dim stupidfunction As Double
stupidfunction = Yo(1, grimey)

End Sub

[SoonerInKCMO]

Post the code you have too... easier to figure out what it is you're trying to accomplish and how you've started going about it.

[Grimey]

I didnt want to seem too needy:O OK, here goes


What we want to do in this project is allow the user to specify a range of inputs for unit sales and a range of inputs for the weighted average cost of capital. We want to produce two outputs. The first is a table of net present values on a new worksheet. There should be a column in the table for every step in the range of unit sales that the user specifies. For example, if the user specifies a unit sales range from 2,000 to 3,000 in increments of 100, the program should automatically produce table columns showing NPVs for unit sales of 2,000, 2,100, 2,200, 2,300, 2,400, 2,500, 2,600 2,700, 2,800, 2,900, and 3,000. The table should also contain a row for each discount rate (i.e., WACC) in the range specified by the user. For example, if the user specifies a range for discount rates of 8% to 12% in increments of 0.5%, the program should automatically produce table rows of discount rates of 8.0%, 8.5%, 9.0%, 9.5%, 10.0%, 10.5%, 11.0%, 11.5%, and 12.0%. The table should be appropriately formatted with applicable column and row labels. Positive numbers in the table should display in blue, negative numbers should display in red, and zeros should display in black. You should also use other features such as number formatting and borders to make your output as aesthetically appealing as possible. The program should automatically label the tab at the bottom of the page with the project identifier specified by the user. For example, if the user chooses to call the project “Project 1” then the worksheet tab should be automatically labeled Project 1.

The second output is a line graph of the table output...

[SoonerInKCMO]

That sounds hard. :O

[Grimey]

Function Yo(rate As Double, values As Variant) As Double
Yo = NPV(rate, values)
End Function

Sub callnpv()
Dim grimey(4) As Double
grimey(0) = 1
grimey(1) = 2
grimey(2) = 5
grimey(3) = 3

Dim stupidfunction As Double
stupidfunction = Yo(1, grimey)

End Sub


I sadly cannot even be sure if you are making fun of me. Haven't learned that code yet:P

[jkm, the stolen pifwafwi]

we are missing some pieces
what is the unit cost?
what is the unit sales?

[NormanPride]

Looks like I chose a bad day to quit sniffing glue...

[jkm, the stolen pifwafwi]

this has the basics of what you are looking for, but units <> money

[Grimey]

we are missing some pieces
what is the unit cost?
what is the unit sales?

those are inputs provided by the user. I have already done the code where the user inputs them.

[Norm In Norman]

Function Yo(rate As Double, values As Variant) As Double
Yo = NPV(rate, values)
End Function

Sub callnpv()
Dim grimey(4) As Double
grimey(0) = 1
grimey(1) = 2
grimey(2) = 5
grimey(3) = 3

Dim stupidfunction As Double
stupidfunction = Yo(1, grimey)

End Sub

Damn consultants.

I don't mess with vba in excel so I don't know how to set cell values or get cell values, but this is what you basically should do (a little more specific than jkm's example). Of course, i might not even understand the problem.


private sub GrimeyLives()
dim i as double
dim j as double

for i = cellthathaslboundsales to cellthathasuboundsales step cellthathasstepforsales
cellusedforsalesamountlabel = i
for j = cellthathaslboundrate to cellthathasuboundrate step cellthathasstepforrate
cellusedforratelabel = j
cell(x,y) = npv(j,i)
next j
next i
End Sub



Please keep in mind that I don't know how to get the value of a cell and I don't know how to set the value of a cell. Also cellusedforratelabel is going to be set to the same thing several times, but who cares? And there is no error checking, so it will error out if they put the word "whale" in a cell where you are expecting a number.

[BlondeSoonerGirl]

Hold on just a damned minute here...

You mean I could post some of my work here and you people would do it for me? Holy crap - that's awesome!

Thanks, Soonerfans.com!

[jkm, the stolen pifwafwi]

norm, it depends on the approach i guess. i was taking input from a user form doing the calculations and then using excel as the presentation layer. you are using excel as the input layer.

[jkm, the stolen pifwafwi]

Hold on just a damned minute here...

You mean I could post some of my work here and you people would do it for me? Holy crap - that's awesome!

Thanks, Soonerfans.com!

i was looking through some c# code at microsoft a couple of weeks ago and the guy had named all of his variables variable

he had
variable
Variable
vaRIable
vaRIAble

only the evil bastard who thought of case sensitivity could allow something like this to happen...

[Norm In Norman]

norm, it depends on the approach i guess. i was taking input from a user form doing the calculations and then using excel as the presentation layer. you are using excel as the input layer.
You can do a form in excel? Who knew? I'd just put 4 cells with the questions in them and go from there. Then I guess I'd put some sort of button on there to call the sub.

[Norm In Norman]

i was looking through some c# code at microsoft a couple of weeks ago and the guy had named all of his variables variable

he had
variable
Variable
vaRIable
vaRIAble

only the evil bastard who thought of case sensitivity could allow something like this to happen...
Did you ever see that list of things to make your code so hard to fix that they can never fire you? It has all sorts of stuff exactly like that.

[NormanPride]

i was looking through some c# code at microsoft a couple of weeks ago and the guy had named all of his variables variable

he had
variable
Variable
vaRIable
vaRIAble

only the evil bastard who thought of case sensitivity could allow something like this to happen...

Did you get him fired? I would personally kick that person in the nuts. They think it's so funny to do crap like that, but don't make the connection when they complain that others do it. :mad:

[Grimey]

Hold on just a damned minute here...

You mean I could post some of my work here and you people would do it for me? Holy crap - that's awesome!

Thanks, Soonerfans.com!

technically this is school. I am just doing it at work.

Big Difference.

[skycat]

To get/put data into a cell, assuming that you have the worksheet active that you want to get/put data to /from:

X = ActiveSheet.Range("YourCellName").Value
ActiveSheet.Range("YourCellName").Value = 3

Activate a sheet using this:

Sheets("NPV").Activate

[BlondeSoonerGirl]

technically this is school. I am just doing it at work.

Big Difference.

Hang on now - I meant no offense. I in no way meant to imply you were a razy American workah. :D

However, I just wanna find a way for these guys to some of my work because I am a razy American workah.

:eddie:

[jkm, the stolen pifwafwi]

You can do a form in excel? Who knew? I'd just put 4 cells with the questions in them and go from there. Then I guess I'd put some sort of button on there to call the sub.

yeah, the sample i gave frank pops up an input form. just go to the visual basic editor and click "insert" -> form

[Grimey]

OK, time to be really embarassed.

Here is what I have so far. Hit ctrl + a to run it. The example sheet is, first of all, what I want it to look like and then below it is an example that he gave to do a single NPV with single inputs. The other two tabs are what happens when I run the sub-procedures. Obviously the one I am having troubles with is the sub NetPresValue, which is pretty key.

[Norm In Norman]

What is "YourCellName"? You have to name each cell? Boy, that sucks.

This is the first time I've used Excel2003 on this computer and it's crashed twice. I was just trying to make a pic of what it would look like.

[jkm, the stolen pifwafwi]

Did you get him fired? I would personally kick that person in the nuts. They think it's so funny to do crap like that, but don't make the connection when they complain that others do it. :mad:

he was an FTE so he was untouchable. i swear to god, he had over 50 variables in different combinations. i was pizzled...

[Norm In Norman]

yeah, the sample i gave frank pops up an input form. just go to the visual basic editor and click "insert" -> form
My excel just said something about not running macros and proceeded to crash, so i don't know what your example looks like.

[jkm, the stolen pifwafwi]

What is "YourCellName"? You have to name each cell? Boy, that sucks.

This is the first time I've used Excel2003 on this computer and it's crashed twice. I was just trying to make a pic of what it would look like.

i'm using 2007 ;)

[jkm, the stolen pifwafwi]

My excel just said something about not running macros and proceeded to crash, so i don't know what your example looks like.

go to tools -> macros -> security and set it to medium

[BlondeSoonerGirl]

My excel just said something about not running macros and proceeded to crash, so i don't know what your example looks like.

http://www.soonerfans.com/forums/images/icons/icon15.gif

[jkm, the stolen pifwafwi]

the input boxes are killing me. look at the form i build 1 billion times better

[Grimey]

Guys, I really appreciate the help you all are giving. But please don't let me eat up too much of your time. I don't mean for you all to drop all your work just to help me. Well, except for jkm;)

[Grimey]

the input boxes are killing me. look at the form i build 1 billion times better

can you do it with default entries? He wants it to be as aesthetically pleasing as possible...

[SoonerInKCMO]

However, I just wanna find a way for these guys to some of my work because I am a razy American workah.

:eddie:

A'ight razy... whaddya got?

[Newbomb Turk]

I am a razy American workah.

:eddie:

you should come work for the 'gubment.

[jkm, the stolen pifwafwi]

can you do it with default entries? He wants it to be as aesthetically pleasing as possible...

yep, in the properties of the textbox add the default values

it looks like where you are struggling is figuring out your cash flows to discount.

[Grimey]

If I start using forms, he is going to know I am getting help. We're covering that in a couple of weeks...

[jkm, the stolen pifwafwi]

if you don't you are going to have some nasty input errors

[Grimey]

it looks like where you are struggling is figuring out your cash flows to discount.

yup. The user inputs and formatting I can kinda handle. It's just the actual numbers that are screwing me up.

I know how to calculate NPV's and all that, just not how to program them.

BTW, major spek for all the help.

[skycat]

What is "YourCellName"? You have to name each cell? Boy, that sucks.

This is the first time I've used Excel2003 on this computer and it's crashed twice. I was just trying to make a pic of what it would look like.

You can use cell naming convention, but it sucks to see things like (E5) all over your damn code. So yeah, I end up naming a lot of the cells by hand when I do have to do this stuff.

But it's pretty easy to use forms. Follow jkm's lead and you'll do a'ight.

[Norm In Norman]

Ok, first off there is a problem with NetPresentValue = (PresentValue). I can't see that you are ever setting "PresentValue", so it is 0 because you declared it, so your function should be returning 0 every time (unless I'm missing something)

Next off, you should declare all of your variables. That's going to get pretty confusing if you mistype something. If you put "Option Explicit" at the very top of each module, it forces you to declare your variables.

[Grimey]

Ok, first off there is a problem with NetPresentValue = (PresentValue). I can't see that you are ever setting "PresentValue", so it is 0 because you declared it, so your function should be returning 0 every time (unless I'm missing something)

Next off, you should declare all of your variables. That's going to get pretty confusing if you mistype something. If you put "Option Explicit" at the very top of each module, it forces you to declare your variables.

NetPresentValue should be the sum of all the present values, but when I put in NetPresentValue = sum(PresentValue) it didnt like it.

[Grimey]

Posts: 666

Yikes, don't like that...

[Grimey]

I have to run, meeting with a headhunter. I will check this thread later. Seriously, please don't waste too much of your time on this.

Thanks again you all.

:twinkies: :twinkies: :twinkies:

[Norm In Norman]

PresentValue is always 0 the way you have it set up. Sum won't work because you only passed it one value. You need to do a loop and add all of your present values (wherever those may be).

[BlondeSoonerGirl]

A'ight razy... whaddya got?

:D

[jkm, the stolen pifwafwi]

okay here is the easy way to handle this minus your financial crap which i'm not going to get into


function whatever()
dim salesqty as integer
salesqty = (saleshigh-saleslow)/salesincr

dim cursales as double
cursales = saleslow
dim presentvalues(salesqty) as double
dim npv as double

for i = 0 to salesqty
presentvalues(i) = getpresentvalue(cursales)
next

npv = (currentrate, presentvalues)

'put npv in a cell
end function

function getpresentvalue(val as double)
'figure out what the present value actually is
getpresentvalue = 'results of your calculations
end function

[Grimey]

okay here is the easy way to handle this minus your financial crap which i'm not going to get into


function whatever()
dim salesqty as integer
salesqty = (saleshigh-saleslow)/salesincr

dim cursales as double
cursales = saleslow
dim presentvalues(salesqty) as double
dim npv as double

for i = 0 to salesqty
presentvalues(i) = getpresentvalue(cursales)
next

npv = (currentrate, presentvalues)

'put npv in a cell
end function

function getpresentvalue(val as double)
'figure out what the present value actually is
getpresentvalue = 'results of your calculations
end function



cool. I will copy that in and see how that works when I am working on it this evening. Thanks again!!

[jkm, the stolen pifwafwi]

i forgot an increment in there



for i = 0 to salesqty
presentvalues(i) = getpresentvalue(cursales)
cursales = cursales+salesincr
next

[Grimey]

It didnt like the parts in red...


i forgot an increment in there



Function NPV()
Dim salesqty As Integer
salesqty = (saleshigh - saleslow) / salesincr

Dim cursales As Double
cursales = saleslow
Dim presentvalues(salesqty) As Double
Dim NPV As Double

For i = 0 To salesqty
presentvalues(i) = getpresentvalue(cursales)
cursales = cursales + salesincr
Next
NPV = (currentrate, presentvalues)
'put npv in a cell
End Function

Function getpresentvalue(val As Double)
'figure out what the present value actually is
getpresentvalue = 'results of your calculations
End Function

[jkm, the stolen pifwafwi]

well, if you are going to name your function NPV...


Dim myNPV As Double

For i = 0 To salesqty
presentvalues(i) = getpresentvalue(cursales)
cursales = cursales + salesincr
Next
myNPV = NPV(currentrate, presentvalues)
'put npv in a cell
End Function

[Grimey]

just so you all can see how cool this would look if I knew what I was doing, this is what it should look like.

He gave us an example for valuating bonds instead of doing NPV's. Just thought you all would like to see what one should look like. And yes, I am aware that he is giving us so many hints that this should be very easy. I am making this harder than it should be, I think.:O

[Grimey]

well, if you are going to name your function NPV...



I cannot figure out why this isnt giving me NPV values...


Function NetPresentValue(ProjectCosts As Double, _
UnitSales As Double, _
SellingPrice As Currency, _
UnitCosts As Currency, _
WACC As Double, _
TaxRate As Double) _
As Currency
Dim n As Integer, i As Single, _
CashFromSales As Currency, _
CashPaid As Currency, _
PresentValue As Currency, _
NetTaxes As Single, _
NetCashFlow0 As Single, _
NetCashFlow1 As Single
CashFromSales = UnitSales * SellingPrice
CashPaid = UnitSales * UnitCosts
IncomeBeforeTaxes = CashFromSales - CashPaid
NetTaxes = (-IncomeBeforeTaxes * TaxRate) + (ProjectCosts * 0.2 * TaxRate)
NetCashFlow0 = -ProjectCosts
NetCashFlow1 = (IncomeBeforeTaxes - NetTaxes)
NetPresentValue = NetCashFlow0 + ((NetCashFlow1 / (1 + WACC) ^ 1) + ((NetCashFlow1 / (1 + WACC) ^ 2) + ((NetCashFlow1 / (1 + WACC) ^ 3) + ((NetCashFlow1 / (1 + WACC) ^ 4) + ((NetCashFlow1 / (1 + WACC) ^ 5) + ((NetCashFlow1 / (1 + WACC) ^ 6)))))))
End Function

[Grimey]

Just thought you all would like to see the end results. Submitted with five minutes to spare:) Might not be perfect, but it is much better than I thought it would turn out.

Thanks to all who contributed.

:twinkies: