# Writing ANN in Excel VBA

#1

So I’m trying to program a basic Artificial Neural Network using Excel VBA. I’ve been following one example in particular:

I’ve been basing the code off of the Python example in the article. I don’t have access to Python unfortunately so I’m trying to do this with VBA. I’ve done my best to convert the code into a useable format for Excel, however there are some things that I do not understand, as well as some issues I’m encountering. Any assistance would be appreciated, thank you.

Here is my VBA Code:

Sub ANN()
Dim X(1010, 1011, 101) As Integer 'Input
Dim Y(1, 1, 0) As Double 'output for comparison
Dim E(0, 0, 0) As Double 'Error

'Variable Initialization
Dim Epoch As Long
Dim LearnRate As Double
Dim InputLayer_Neurons() As Integer 'Number of Features in data set
ReDim InputLayer_Neurons(ArrayLen(X))
Dim HiddenLayer_Neurons As Integer
Dim Output_Neurons As Integer
Dim hidden_layer_input1 As Variant
Dim hidden_layer_input As Variant
Dim hiddenlayer_activations As Variant
Dim output_layer_input1 As Variant
Dim output_layer_input As Variant
Dim slope_output_layer As Variant
Dim slope_hidden_layer As Variant
Dim d_output As Variant
Dim Output As Variant
Dim Wh As Double 'Weight Hidden Layer
Dim Bh As Double 'Bias Hidden Layer
Dim Wout As Double 'Weight output Layer
Dim Bout As Double 'Bias Ouput layer
Dim i As Long
Epoch = 5000 'Training Iterations
LearnRate = 0.1 'Learning Rate
HiddeLayer_Neurons = 3 'Number of Neurons in Hidden Layer
Output_Neurons = 1 'Number of Neurons at output layer

'Weight & Bias Initialization
Wh = Application.WorksheetFunction.RandBetween(InputLayer_Neurons, HiddenLayer_Neurons)
Bh = Application.WorksheetFunction.RandBetween(1, HiddenLayer_Neurons)
Wout = Application.WorksheetFunction.RandBetween(HiddenLayer_Neurons, Output_Neurons)
Bout = Application.WorksheetFunction.RandBetween(1, Output_Neurons)

For i = 0 To Epoch

'Forward Propagation
hidden_layer_input1 = WorksheetFunction.MMult(X, Wh)
hidden_layer_input = hidden_layer_input1 + Bh
hiddenlayer_activations = Sigmoid_Activation(hidden_layer_input)
output_layer_input1 = WorksheetFunction.MMult(hiddenlayer_activations, Wout)
output_layer_input = output_layer_input1 + Bout
Output = Derivatives_Sigmoid(output_layer_input)

'Backpropagation
E = Y - Output
slope_output_layer = Derivatives_Sigmoid(Output)
slope_hidden_layer = Derivatives_Sigmoid(hiddenlayer_activations)
d_output = E * slope_output_layer
Error_at_hidden_layer = WorksheetFunction.MMult(d_output, Transpose(Wout))
d_hiddenlayer = Error_at_hidden_layer * slope_hidden_layer
Wout = Wout + WorksheetFunction.MMult(Transpose(hiddenlayer_activations), d_output) * LearnRate
Bout = Bout + WorksheetFunction.Sum(d_ouput) * LearnRate
Wh = Wh + WorksheetFunction.MMult(Transpose(X), d_hiddenlayer) * LearnRate
Bh = Bh + WorksheetFunction.Sum(d_hiddenlayer) * LearnRate

Next

Debug.Print Output

End Sub

Function Sigmoid_Activation(X) As Variant
Sigmoid_Activation = 1 / (1 + Application.WorksheetFunction.Power(-X))
End Function

Function Derivatives_Sigmoid(X) As Double
Derivatives_Sigmoid = X * (1 - X)
End Function

Public Function ArrayLen(arr As Variant) As Integer
ArrayLen = UBound(arr) - LBound(arr) + 1
End Function

Some questions:

1. ``````  Are my arrays correct?
``````

It seems like I’m not able to input 0101 as the third element in the array X. The IDE changes the value to 101. I suppose that’s because there are no integer values that start with 0. How do I correct this?

1. ``````  Am I using the correct method for matrix multiplication by using the worksheet function MMULT()?
``````
2. ``````  One specific issue I am having is in the section under Backpropagation.  I am getting a type mismatch error on the line:
``````

E = Y – Output

I suppose this is because in VBA there is no built in function for subtracting a Double type value (Output) from an array (Y). It seems you can do this in Python, which is probably why it is used for scientific calculations.

In any case, how do you resolve this issue in the context of this neural network? I can do research on how to perform this using VBA, but I don’t know what would be correct in this case. Any assistance would be helpful.