Begginers tutorials for Excel DNA.

[gustavo franceschi]

Hello, I just got to know about Excel DNA some weeks ago.

I don´t really know how to code in VBA nor C# and I wanted to know if there were some beginner´s tutorials out there that would help me utilize Excel DNA to its full potential.

[Will Ehrendreich]

Well, if you're new to writing code in general, I'd like to encourage you to skip vba and c# and go straight to the good stuff -> FSharp.

One of the best ways to do that is to go through this: FSharpKoans

Basically it's a tour of the basics of FSharp that has you making code pass tests, feature by feature.

Past that, check out fsharpforfunandprofiit.com or one of the MANY wonderful fsharp related youtube videos.

Why FSharp??

Because, in my humble opinion, it's leagues ahead of the other options, and, specifically for the purpose of writing user defined functions in excel, fsharp is perfectly suited to the task because it's whole focus is exactly that: functions.

Fsharp is a Functional-First language.

Perhaps you know already, but a Function in the functional programming sense is simply a piece of code that takes something and gives something.

A function always takes something (called an argument or a parameter). This could be a a string of text, an integer, a boolean, whatever you want to pass in, but it must take SOMETHING, even if that something is really fsharp's version of nothing, which is called Unit, which is represented by open and closed parenthesis "()". You can think of unit as basically a null, but one that won't blow up and cause things to crash and have exceptions.. its.. magical..

then, a function also always gives something back, again, even if that something is a Unit.

ideally, and by default, all the logic and computation inside a function should be done with immutable (unchanging) values, and will not touch or change ANYTHING outside of the start and end of the function (this is called a Pure function)

What the hell does that mean? 

Ok so in other languages that aren't immutable by default and don't care if you mutate values whenever you can easily do something like this: (csharp like code below)

using System;
var DirtyClass = new CSharpWithMutatedValueClass();
Console.WriteLine($"Value of MyTextVariable is: \n\n\"{DirtyClass.MyTextVariable}\"");

DirtyClass.ImpureTextMutatingClassMethodThatSeemsInnocentButHasSideEffectsEW();
Console.WriteLine("\nNow Calling the class Method called \nImpureTextMutatingClassMethodThatSeemsInnocentButHasSideEffectsEW....\n");

Console.WriteLine($"Value of MyTextVariable is: \n\n\"{DirtyClass.MyTextVariable}\"");



public class CSharpWithMutatedValueClass {

public string MyTextVariable = "random text that can change because csharp will let you shoot yourself in the foot";

public void ImpureTextMutatingClassMethodThatSeemsInnocentButHasSideEffectsEW()
{
  MyTextVariable = "Some Totally new value, because Csharp likes to watch the world burn. \nGood luck figuring out why it's changed if this method \nwas called somewhere in code that you didn't expect, \nor at a time when you weren't expecting, \nlike after an event, \nor by some other programmer who saw the method signature, \nsaw that it both took no parameters and gave no value back \nand just called it for kicks and giggles, because, \nhow are THEY supposed to know it would have an effect on MyTextVariable? ";

}

}

See the abomination above in SharpLab.io !!

this is.. a problem.. for so many reasons..

in fsharp, by default, what you saw above is impossible.

it would give a compiler error, you wouldn't even be able to build it.

instead you'd use a function that takes a value, and gives a new value in return. pure and simple. like an excel reference, it simply takes a look at the value you give it, then spits out another value entirely

in fsharp you might do something like this instead:

    let happyImmutableString = "I'm not gonna change on you, you can rely on me."

    let takeAStringGiveADifferentOneBack inputString =
      let result = sprintf "The string you gave me is ' %s '"  inputString
      result

    let newStringAsAResultOfTheFunction = takeAStringGiveADifferentOneBack happyImmutableString


newStringAsAResultOfTheFunction would give you back "The string you gave me is 'I'm not gonna change on you, you can rely on me.'"
happyImmutableString would give you back "I'm not gonna change on you, you can rely on me."

See the above at try.fsharp.org

No surprises.

No random change you didn't want.

Dead simple.

This is just the absolute surface level of fsharp's glorious goodness.

Please, do yourself a favor, cut out years of frustration of your life, learn fsharp early in your career, and you're going to be much more satisfied.