Trouble with a formula and named ranges

[John64]

I'm trying to get this line of code to work, but I'm dealing with two named ranges (xval and yval). The ranges work and I can select between them just fine.

Ideally, I'd prefer not to have to put it in to a formula. I'd just like to get the solution or value passed to a named string.

ie.
Dim DeclineV as long
decline = -LN(1-((LOGEST((yval),(xval))-1)*365

No clue what I'm doing. Any help would be appreciated.

Range("F2").Formula = "=-LN(1-((LOGEST((yval),(xval))-1)*365))"

 

[noboffinme]

Hi John64,

Do you want a Function to produce the result of that formula?

Does the formula work in Excel normally? (meaning manually not with VBA?)

 

[John64]

Yeah, I need it to solve for that equation, preferably all in VBA without having to pass a formula to an excel sheet.

This is how it looks when it's working in excel, but I have other code that finds where the ranges will be at. The range of data I'll be looking at will be moving.
=-LN(1-((LOGEST(B2:B29,A2:A29)-1)*365))

 

[noboffinme]

Have a look at this link;

'http://www.office-archive.com/4-excel/ec0f28c4daafc36b.htm

It shows how to use an array with VBA.

Hth

 

[John64]

Thanks for the help. I'm not there yet. I made a tad bit of progress by defining the value as a 'variant', but it opened up new problems.
If I run this it now works as a variant for the first line of code:

DeclineV = Application.LogEst(yval, xval, True, False)   'works fine

DeclineV = DeclineV - 1  'bugs out

I can't treat the variant as a number in order to complete the rest of the equation. I don't know why??? The first line also runs if you don't define DeclineV. I also don't know about taking the -LN in this manner.

 

[noboffinme]

Hi John64,

You may need to convert the Variant to an Integer.

Try something like the below before you do the DeclineV - 1 line.

I would say as the compiler doesn't recognise DeclineV as an Integer, you can't subtract 1 from it.

Public Sub Demo()
Dim Intval as Integer
Dim VariantVal As Variant

VariantVal = "test"

' CONVERT TO INTEGER <<<<<<<< EXAMPLE HERE <<<<<<<
IntVal = Int(VariantVal)

End Sub

 

[John64]

I don't see an "int" function, but I'm able to run your code with a few different conversion functions, ie. cbool, cint, clng, however, all of them bug out when I try them on the 'DeclineV' variable after the logest function. Also, all these conversions seem to round the number, dropping off all the decimals. I tried putting a variant in as "1.354352545" and kept losing all the digits during the conversion, even with CLng.

 

[noboffinme]

Sorry,

cInt would be the one in your list but as you need decimals, try cDbl.

I also found that the "LN' function is available as per below;

Application.WorksheetFunction.Ln ...

As you've probably guessed, I'm working through this with you so I'm learning as well.

Let me know how you go.

 

[John64]

Well..... I gave up on it. Thanks for the help noboffinme. Even with your last post, I'm still getting errors trying to convert back from a variant. I've tried everything I can think of. I just went a different route, and got it to put the formula in a temporary cell on the worksheet. It's pretty crude and inefficient, but I'm just an engineer, not a programmer. It works

Code I used:

Dim FirstR As String
FirstR = Split(ActiveCell(1).Address(1, 0), "$")(0) & ActiveCell.Row

'repeated the above 4 times for each of the corners of the array and had to activate the cells with searches

Dim TempVV As String
TempVV = ThirdR & ":" & FourthR & "," & FirstR & ":" & SecondR

Range("Y4").Formula = "=-LN(1-((LOGEST(" & TempVV & ")-1)*365))"

Thanks again
Complicated math within VBA is just a bit out of my experience level

 

[noboffinme]

I want to have another try.

Can you post your Array data (Source data that the code is working on) in an Excel file?

Also, your code isn't in a Sub or Function.

Can you post the full code including the Function or Sub so I can exactly what you're doing.

Something like the below;

Function LOGEST()
 
Dim FirstR As String
FirstR = Split(ActiveCell(1).Address(1, 0), "$")(0) & ActiveCell.Row
'repeated the above 4 times for each of the corners of the array and had to activate the cells with searches
Dim TempVV As String
TempVV = ThirdR & ":" & FourthR & "," & FirstR & ":" & SecondR
Range("Y4").Formula = "=-LN(1-((LOGEST(" & TempVV & ")-1)*365))"
End Function

Thanks

 

[John64]

Sorry I had to go out of town for a day. You've probably forgot all about this. Here's my file at any rate. I deleted off about 80% of the dataset due to the size and a bit of it may not be public. You can see from the final chart what the goal was. I'm not designing for end-users, just trying to make it easier to compile data. The reason I needed the effective exponential decline is so that I can compare the declines with those in Landmark ARIES, an oil and gas reserves software.

 

[noboffinme]

Hi John64,

I was working on this with a learned colleague but I haven't got any more answers from him as yet.

I'll let you know if/when I get an answer.

 

[PeterF]

Your code errors because you are trying to convert a string to a number.

DeclineV = [B][COLOR="Red"]"=-LN(1-((LOGEST((yval),(xval))-1)*365))"[/COLOR][/B]
Dim tempV As Variant
tempV = CDbl(DeclineV)

DeclineV is a string with the value in red, cDbl can't do anything with it.