I’m writing a VBA application in Excel. It’s coming back to me, but not entirely;
to get the natural log of a cell value, the syntax is “Ln(number)” right? where number is a positive number. The program hangs up on this line, saying the sub or function is not recognized.
I mean, this isn’t brain rocketry. what am I forgetting?
much obliged
bizz
At the risk of being a smidgen impolite …
I could not remember either. So I typed [VBA natural log function] into Google and in about 1 second it answered with “LOG()”.
That was less typing than creating your post.
I too sometimes forget that rather than wading through the cumbersome built-in help system of various apps, a quick search gets the same result much more easily.
Teach a man to fish …
You sorta remembered correctly; LN() is the corresponding function to use inside a cell formula. There are a lot of places where the function names differ between VBA & Excel formulas. Due to the very different historical basises of the two “languages” that were later brought together.
I did it in fewer clicks:
New Excel workbook (No fair counting this click!)
Type ‘=L’ in the func bar and Excel suggests function formats from a dropdown, including LN and LOG. The = is required as always.
FWIW, there are a lot of contexts where “log” means “natural (or Napierian) logarithm”. And lots of other contexts where in means “common logarithm”. The only way to know which one LOG() means in a given context is reading the documentation (or testing).
But the problem with that is that =LOG() in a spreadsheet defaults to the common log, not the natural log, and, as @LSLGuy pointed out, the relevant function in VBA isn’t necessarily the same.
I overlooked the VBA part, cunningly hidden in the title and elsewhere.
I hate it when that happens. 
no offense taken; i wanted the natural log, i.e. to the base e
also, I am aware that the syntax needed is “=LN()”
I know it, you know it, but VBA keeps throwing an error when I debug/run it. I’m saying it doesn’t work.
“=LOG()” works just dandy, except that it gives me the wrong answer.
In my experience, when I run into situations like this, it’s because I’m doing something stupid.
So, Here’s a stripped-down version that gives me the same compile error (“Sub or Function not defined.”):
Sub FindQ()
Dim R1 As Double
Dim R2 As Double
Dim Term4 As Double
R1 = 5
R2 = 5.01
Term4 = LN(R2 / R1)
Worksheets(“Annulus”).Range(“A” & 1).Value = Term4
End Sub
any insights humbly accepted
Two things.
1) I was guilty of confidently repeating a Google search result, forgetting that that’s now AI-generated Bullshit, not cite-able fact. So it lied to me, and I in turn lied to you. Sorry.
2) Here’s the truth, direct from Microsoft:
There is only one function for logarithms in VBA. It’s LOG(). There is no LN() function.
The VBA LOG() function computes only natural (base e) logarithms. The help topic shows an example of how to write a custom function based on LOG() to return a logarithm using a different base.
Sorry to have been unhelpful.
It’s a bit disappointing.
The workaround hack, in case it is helpful, is to change the base.
LN(x) = LN(10)*LOG(X)
with ln(10) roughly 2.303 (or use as much/little precision as required), which is basically what Microsoft suggests but in the other direction.
If you only have the natural log function, common log of x = common log of e * natural log of x
with common log of e roughly 0.434 (=1/2.303)
Or just use
ln(x) == log(x)/log(e)
I’m not in front of a computer with Excel right now, but there are a host of functions available in VBA as methods of the Application.WorksheetFunction object. So, maybe Application.WorksheetFunction.ln() works, or .log() works?
But, if you’re looking for the natural log anyway, it looks like Log() will work for you in VBA, according to @LSLGuy.
I love discussions like this where everything is in English, but I don’t understand one friggin’ thing that is being said. It’s like ASMR. Swoon. Carry on.
Not quite sure I’d label that a “hack”. It’s a very well-known and often used identity in math.
I would not be surprised to learn that in most languages and libraries that provide a COMMONLOG() function and/or or a generalized LOG(value, base) function, the reality is that under the hood they’re using that same identity and the only function in the library that truly computes a logarithm from first principles does it in base e.
The only exception I could see is if log base 2 is especially easy to implement on binary floating point hardware. If so, then that’d probably be the underlying first-principles function and any other bases, including e, would be derived from that identity.
I have a vague recollection from ~50 years ago about that, but not motivated enough to go read up on it now.
What’s ASMR? swoon
Apparently, “ASMR stands for “autonomous sensory meridian response” and describes the tingling sensation some people experience when exposed to certain stimuli.”
I am hesitant to reply to this thread because I do not use Excel… but it may be relevant that popular processor architectures like Intel and AMD that implement binary floating point have instructions to compute binary logarithms. To convert to base 10 or base e, constants like \log_2e are available.
Nevertheless, you are not programming basic algorithms; you are using VBA. The VBA documentation pretty clearly explains that the syntax =Log(...) is the function that computes natural logarithms.
On the contrary; you’ve all been most helpful. I came up with a work-around. Thanks, all!
/mods, hit the lights, please.
Sigh…color me abashed. Thanks.