The article doesn't seem to actually answer this question. It talks about cocaine's anaesthetic use but doesn't explain why Holmes was using it. Was he using it as a nootropic similar to kids who use modafinil or amphetamine to study for tests? Or did he use it for the euphoric effects? "For the rest of his career, Sherlock confined himself to shag tobacco and black coffee..." makes it sound like Holmes was rationally using cocaine as a nootropic to help him solve cases.
That's because Holmes explains it himself, in several of the stories, and the author of this article no doubt assumed that his readers would be familiar enough with the stories to know what Holmes's explanation was. In The Sign Of Four, he tells Watson that he only needs cocaine as a stimulant to his thought processes when he is not working on a case. When he is working on a case, the case itself is more than enough mental stimulation and he neither needs nor uses cocaine. In fact, when Watson a little later in the discussion offers to test Holmes's deductive abilities by giving him a watch and asking him what he can deduce from it, Holmes says that even this minor problem would prevent him from taking a second dose of cocaine.
It's the same story, The Sign Of Four. When Watson sees Holmes taking out the hypodermic, which he has seen him do every day for quite some time but that day feels compelled to say something about it, he asks, "Which is it today, morphine or cocaine?" In A Study in Scarlet, Watson does make a list of things he's observed about Holmes, but drug use of any kind is not among them.
AFAIK that's the only mention of morphine in any of the stories, although Holmes is mentioned as taking cocaine in several others (and in some that are after the Return Watson says he has been able to wean Holmes of the drug but can tell that Holmes still thinks about it sometimes). Opium does figure in one story, The Man With The Twisted Lip (in the Adventures), but it's being taken by a friend of Watson's--although when Watson finds Holmes in disguise in the opium den he goes to to rescue his friend, he understandably wonders whether Holmes has been trying a new drug until Holmes explains that he's there working on a case.
To GP's point, this is essentially the same reason stimulants are prescribed for the modern sufferer of ADHD as well.