Posts by widgetwonka

widgetwonka · Mar 31st 2011

Re: Make my formula copy across columns

The INDIRECT() method is probably best. you will need to build the ranges somewhere else (like above the cells), and then use the indirect where you reference the cells.

widgetwonka · Mar 31st 2011

Re: Max value from a graph

Do you have a trendline for this plot? Is the equation quadratic? Can you display the regression equation, then take the first derivative and solve for zero? I can't envision another scenario.

Please post an example if you can.

widgetwonka · Mar 31st 2011

Re: Max value from a graph

Firstly, let's assume that your Y values are not multi-modal.

Next, let's assume that your X Values are in column C and your Y values are in column D

So, the max Y value is:

=MAX(D:D)

And the Max X value for that Y Value is:

=INDEX(C:C,MATCH(MAX(D:D),D:D,1))

Does this work for you?

widgetwonka · Mar 30th 2011

Re: Replace Grid Lines

Jena,

gridlines are just a fancy type of border. You can add them to a range without the code:

1. select the range you want to add them to
2. go to format --> cells --> border
3. select the color in the bottom right of the first block (grey - 25%)
4. apply to the interior and exterior using the appropriate buttons

If you have to do this continually, you can use a module in the VBE editor, but you might not want to go the code route for just this.

widgetwonka · Mar 30th 2011

Re: Replace Grid Lines

Does tools --> options --> view --> gridlines (check and apply) work?

If not, what about:

Code

Sub RemoveGridLines()
'
    ActiveWindow.DisplayGridlines = true
End Sub

widgetwonka · Mar 30th 2011

Re: Changing formula based on drop down list

1. Use a drop down box to drive the value in a cell (the non-vba dropdown).
2. Use a lookup to decode the target cell into the region that you want to use in the sumproduct.
3. Drive the sumproduct off the lookup result.

This would be easier if you could post an example formula. Is Japan further segmented into cities, etc? You could also handle this w/o VBA, and that would be easier to update without having to go to the code when new breakdowns are added.

widgetwonka · Mar 30th 2011

Re: Change negative numbers to positive numbers

Yes. In another cell, type in -1

Then, copy that cell.

Select the column that you want to change and go to edit --> paste special --> multiply

Hit OK.

That should do it.

widgetwonka · Mar 29th 2011

Re: how to skip blank cells using a vlookup FORMULA

Can you be more specific about the data and what you want the VLOOKUP to accomplish? The title says you want to skip blank cells, but in the post you say you want the VLOOKUP formula to run only on blank cells.

widgetwonka · Mar 29th 2011

Re: Need Help with VBA Array - For Each Running Slowly

Rory,

I had not thought about condensing to one line. I suppose I used the variable when I was writing the formula to make it easier for me. But, now that I have all the pieces I need, I can write it directly:

Code

Public Function FRONGELLO(Current As Double, portreturns As Range, BenchReturn As Double, Previous As Double) As Double
 
'Thanks to Rory & GlennUK on Ozgrid for helping me sort this mess
FRONGELLO = (Evaluate("PRODUCT(1+ " & portreturns.Address & ")") * Current) + (BenchReturn * Previous) + Previous
 
End Function

Thanks for all the help. Hopefully I will be able to pay it forward one day.

widgetwonka · Mar 29th 2011

Re: Need Help with VBA Array - For Each Running Slowly

Rory,

I was unaware that using a double would return a decimal. I had been having problems with my formula evaluating to an integer when I first wrote it, so I thought the variables were the problem. Using your tip, I have written it as compactly as I think I can. I went ahead and swtiched to a single, since these numbers are quite small, and I only have five decimal points of precision on the inputs anyway.

Code

Public Function FRONGELLO(Current As Single, portreturns As Range, BenchReturn As Single, Previous As Single) As Single


'Variable declaration
Dim frong1 As Single
Dim frong2 As Single
Dim frong3 As Single
Dim myprod As Single
 
'Calls PRODUCT function to run against portreturns range
myprod = Evaluate("PRODUCT(1+ " & portreturns.Address & ")")
 
'Links prior periods to current period
frong1 = (Current * myprod)
frong2 = (BenchReturn * Previous + Previous)
frong3 = (frong1 + frong2)
 
'displays value of formula
FRONGELLO = frong3


End Function

Display More

Thanks to both of you for your help.

Any other thoughts would also be appreciated.

widgetwonka · Mar 29th 2011

Re: Need Help with VBA Array - For Each Running Slowly

GlennUK,

Thanks for the advice. That has sped it up about 50%

New code:

Code

Public Function FRONGELLO1(Current As Variant, portreturns As Range, BenchReturn As Variant, Previous As Variant) As Variant


'Variable declaration
Dim frong1 As Variant
Dim frong2 As Variant
Dim frong3 As Variant
Dim myprod As Variant
 
'Calls PRODUCT function to run against portreturns range
myprod = Evaluate("PRODUCT(1+ " & portreturns.Address & ")")
 
'Links prior periods to current period
frong1 = CDec(Current * myprod)
frong2 = CDec(BenchReturn * Previous + Previous)
frong3 = CDec(frong1 + frong2)
 
'displays value of formula
FRONGELLO1 = CDec(frong3)


End Function

Display More

Anyone have any idea about the decimal? I have read (and tried) to declare a variable as decimal, but that is not accepted, which is why I have the CDec() code in there.

widgetwonka · Mar 29th 2011

Re: Adding a sheet and then referencing the new sheet in an existing code

Is the number of sheets to add undetermined? If so, why not something like

Code

dim intgr as integer
dim wsName as string
dim ws as Excel.Worksheet
 
wsName = "ws"
intgr = 1
 
set ws = worksheets.Add
ws.Name = wsName & intgr
 
'/do whatever you need with this sheet
 
intgr = intgr + 1
 
'/repeat as needed.

Display More

widgetwonka · Mar 29th 2011

I'm a long time lurker, first time poster to Ozgrid. So, hello all, and thanks for all the help to date.

My question is around how to improve my code so that it takes less time to run.

I have a UDF that uses a for each statement to calculate an array. Here is the code:

Code

Public Function FRONGELLO(Current As Variant, portreturns As Variant, BenchReturn As Variant, Previous As Variant) As Variant
 
'Variable declaration
Dim frong1 As Variant
Dim frong2 As Variant
Dim frong3 As Variant
Dim prod As Variant
Dim r As Range
 
'Set Array to 1
ArrayCalc = 1
 
'calculates array as {PRODUCT(1+periodic portfolio returns)}
For Each r In portreturns
ArrayCalc = ArrayCalc * (r.Value + 1)
Next
 
'Links prior periods to current period
frong1 = CDec(Current * ArrayCalc)
frong2 = CDec(BenchReturn * Previous + Previous)
frong3 = CDec(frong1 + frong2)
 
'displays value of formula
FRONGELLO = CDec(frong3)
 
End Function

Display More

I can accomplish this more quickly with an array formula in Excel, but then I lose the ability of the user to interpret the fact that this formula is a specific algorithm (known as the frongello alogrithm). Also, if a user wants to use the algorithm and this formula isn't defined, then they have to know how to use an array formula...easier said than done in this crowd...

Really, this code could probably be improved in two ways:

1) is there a better way to have the formula evaluate as a decimal? I realize that declaring my variables as variant is taking up more space than is probably neccesary, but I am not sure of the proper method

2) is there a more efficient way to write an array formula in VBA that mimics {=PRODUCT(1+"range")}

I have been an Excel user for a long time, but I am fairly new to VBA, so any help would be appreciated. I have been reading several programming guides to get up to speed (and Ozgrid obviously!)

--Jeremy