excel - 在 Excel 中编写 ANN:VBA 类型不匹配错误
问题描述
所以我正在尝试使用 Excel VBA 编写一个基本的人工神经网络。我一直在关注一个特别的例子:
https://www.analyticsvidhya.com/blog/2017/05/neural-network-from-scratch-in-python-and-r/
我一直基于文章中 Python 示例的代码(代码示例在文章的底部)。不幸的是,我无法访问 Python,所以我试图用 VBA 来做到这一点。我已尽力将代码转换为 Excel 的可用格式,但是我遇到了一个问题,我不知道如何解决它:
这是我的 VBA 代码:
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
我的问题是:
在反向传播下的部分。我在该行收到类型不匹配错误:
E = Y – Output
我想这是因为在 VBA 中没有用于从数组(Y)中减去 Double 类型值(输出)的内置函数。即使 Output 被声明为变体,我相信它将包含的值将包含一个浮点值。我不确定这是否是冲突的原因。但是,您似乎可以在 Python 中执行此操作,这可能就是它用于科学计算的原因。
无论如何,解决此问题的最佳方法是什么?
解决方案
不幸的是,VBA 不支持对数组的直接操作。相反,您必须循环:(
如果你真的想用 VBA 来做,你可以看看这个CodeReview 帖子。
如果您想使用 Excel 来完成,但无法接受 VBA 的限制,有一些 Python 工具允许使用 Python 编写 Excel。快速搜索会让你得到很多,包括https://www.xlwings.org/,它已经在我的列表中尝试太久了。
推荐阅读
- python - tsv 文件中的行到单独的数组中
- javascript - 如何在javascript代码中动态使用表单
- xml - XML Vector - 如何创建带圆角的虚线
- mysql - MySQL 查询优化与大型数据库上的子查询和 JOIN
- facebook - 如何将 Instagram 贴纸导入 spark ar?
- javascript - 无法在 heroku 服务器上获取/注册错误
- mysql - SQL查询,根据行是否存在选择顶部结果或底部结果
- python - 无法在 Python 中使用我的类实例进行多处理
- arduino - 一个文本中有 2 个变量和 1 个字符串
- flutter - 颤振:从颤振应用程序构建apk失败