Several beginner VBA questions

[hfler]

Hi All,

So I'm a new user and feeling fairly confused regarding the limitations of User-Defined Functions versus macros. I tried to make a UDF the other day that referenced specific cells in a spreadsheet, and activated those cells for calculations - the general consensus from colleagues was that this doesn't work, can someone please confirm that?

The reason I wanted to activate specific cells was so that I could create a loop in my UDF that cycled through the values in cells below a given range using an IF statement. If it's not possible to activate specific cells in a sheet inside a UDF, can I create a variable input in the UDF function that is essentially the range of values, similar to highlighting a range using the generic SUM function in a spreadsheet? If so, how do I cycle through these values using a loop and IF statements in my UDF?

Any insight you can provide is much appreciated. Like I said, I'm a newbie VBA user, so something that might seem obvious could be totally over my head. Thank you!

 

[mikerickson]

If you post your code, it would help us give you guidance.
In general, Selecting or Activating cells is not nessesary in any VBA routine.
Also in general, a UDF will not pull values from cells unless they are passed as arguments of the UDF.

 

[pgc01]

I tried to make a UDF the other day that referenced specific cells in a spreadsheet, and activated those cells for calculations - the general consensus from colleagues was that this doesn't work, can someone please confirm that?

Hi

No, you cannot activate cells with a udf. In fact you cannot do anything that changes the environment (delete/insert row/column, insert/delete cell, copy cell, etc.).

Think of the udf a function you add to the 300+ functions that excel gives you by default.

I'm sure you have already used many functions in your formulas (maybe even more than 100), in different categories (Statisical, Text, Financial ...) and if you remember you never got as a result a deleted cell or a new worksheet, etc. A udf, like any other excel function, simply returns the result of data manipulation.

In conclusion, when you look at the 300+ functions that excel gives you by default and you think there's one extra function that you'd like to have, to perform some data manipulation that serves your specific purpose, that's when you write a udf.

Remark: as to the rest of the post, I don't understand exactly what you need, but it's usually bad practice to activate cells or worksheets in vba. It's unnecessary, inefficient and hurts readability.

 

[hfler]

Thanks everyone for your responses, they're very helpful. To follow up, I've pasted a portion of my code below. I think my loop contains language that is only applicable to a Sub, but I want to use it in an ETF. My function parameters are,

Function test(startdate As Double, enddate As Double, prices As Range, expirationdate As Range)

I'm trying to create a loop such that

For i = 0 To 17

Where 17 is the number of entries in the "expirationdate" range, and then cycle through the expirationdate range and select the associated price if it meets certain criteria. The problem is I can't figure out how to cycle through the expirationdate range using a UDF - with a sub, I would use something like

IF meets some criteria THEN do something, ELSE Offset(i, 0)

But I don't think that works for UDFs. How can I cycle through the expirationdate range using an IF loop?

I've seen examples online of people denoting a variable like expirationdatehat, and then saying,

For Each expirationdatehat in expirationdate do something...

But I don't want to do it for "Each" - I want it to cycle through the "i"s 17 times. If this is entirely unclear, please let me know - can I past spreadsheets to the site in anyway? That may make things easier.

Thanks again for your help!

 

[mikerickson]

Dim oneCell as Range

For each oneCell in experationDate
    If SomeCondition(oneCell) Then
        Rem do something
    End If
Next oneCell

 

[hfler]

Thanks for your help so far mikerickson. I'm including a detailed summary of what I want to do. Here are my data columns,



<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=384><COLGROUP><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5083" width=139><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 69pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20 width=92>Start date</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=74>End date</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=79>Price</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 104pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 width=139>Contract Expiration</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 height=20>6/1/2010</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67>4/18/2012</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68> 1,306 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>May-10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68> 1,306 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>Jul-11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68> 1,457 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>Aug-11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68> 1,017 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>Dec-11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68> 1,133 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>Mar-12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68> 735 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>May-12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68> 1,207 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>Jul-12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68> 1,307 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>Sep-12</TD></TR></TBODY></TABLE>


I don't want to paste too much of the code I've come up with because I don't want it to be too confusing, so here's the general gist,

Function newswap(startdate As Double, enddate As Double, prices As Range, contrexpir As Range) As Variant
 
Application.Volatile
Dim i As Range
Dim currmo As Double
Dim currmojump As Double
Dim px As Double
Dim pxjump As Double

currmo = startdate
For Each i < enddate In contrexpir    
   If currmo < i Then
        pxjump = prices + px
        px = pxjump
            If currmo < WorksheetFunction.EDate(currmo, 1) Then
            currmojump = currmo
            Else:
            currmojump = WorksheetFunction.EDate(currmo, 1)
            End If
        currmo = currmojump
    Else: currmo > i And IsDate(i) Then Next i

Okay, so the intention of this code is to take the current month (currmo; begins at start date), and check if it is less than the first i - i.e. the first date in the contract expiry column. If it is, I want to pull the corresponding price of that contract from the adjacent column.

The "pxjump" variable is that I can add to the price from each previous i - in other words, so that as the loop continues, to price from each segment of the loop will add on top of the prices from the previous segment.

If the i is less than the currmo > edate(i, 1), then I want the code to cycle to the next i in the range contrexpir, and so on, until the end date condition is satisfied. It's clear to be that the language,

For Each i < enddate In contrexpir

doesn't work. How can I rephrase this so that the loop doesn't exceed the enddate?

As always, your advice is much appreciated! Thanks everyone!