在用于在两个工作簿之间进行计算的VBA代码上收到错误

Question

我正在编写VBA代码来计算PnL，在两个excel工作簿之间，一个wb(wb.Position(具有资产名称、期初和期末位置(A1、A2、A3(，第二个wb.Price与相同的资产(fx(名称、期终价格、单元格A1、A2和A3(。我使用一个循环逐行遍历每个资产，并使用vlookup从Wb.Price中提取价格，在循环中进行计算，汇总总数并在Wb上输出总PnL和%。在单元格F7和G7中的位置。

更新：我已经应用了建议的更改，代码中的一些更新仍然收到编译错误：

Option Explicit
Sub PnL()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim PnLUSDT, PnLUSDT_percent As Range
Dim Asset As String
Dim BegPos, EndPos, startprice, endprice, unreal_PnL, Real_PnL as Long 
Dim PriceDelta, PnLtotal_USDT, PnLtotal, AssetValue as Long 
Dim AssetValue_USDT, TotalAssetValue, PnlPercent As Long
Dim row As Integer
Dim wbPosition As Range
Dim wbPrice As Range
Dim wbPrice As Workbook
Dim wsPrice As Worksheet
Set wbPosition = ThisWorkbook
Set wsPosition = wbPosition.Sheets("Sheet1").Range("A:G")
Set wbPrice = Workbooks.Open("C:UsersUserDesktopExcel ExerciseExcel         
Exercise 2 of 2.xlsx")
Set wsPrice = wbPrice.Worksheets("Sheet1").Range("A:C")
row = 2               '<--position sheet has heads, starting with data
TotalAssetValue = 0   '<--aggregate of Asset Value in loop
PnLtotal_USDT = 0     '<--aggregrate of Profit & Loss in loop
With wsPosition       
While .Cells(row, 1) <> ""
Asset = .Cells(row, 1)        '<--security name
'Debug.Print "Asset: ", Asset
BegPos = .Cells(row, 2)       '<--start position in ws.Position
EndPos = .Cells(row, 3)       '<--end position in ws.Positon
With wsPrice
startprice = Application.VLookup(Asset, wsPrice, 2, 0)   '<--start price in ws.Price
endprice = Application.VLookup(Asset, wsPrice, 3, 0)     '<--end price in ws.Price

If startprice = endprice Then
PriceDelta = endprice     'Price change cannot be 0 for calc purposes
Else
PriceDelta = startprice - endprice
End If

unreal_PnL = EndPos * PriceDelta    '<--calcing unrealized P&L
Real_PnL = (BegPos - EndPos) * endprice   '<--calcing Real P&L
PnLtotal = unreal_PnL + Real_PnL   '<--totaling
PnLtotal_USDT = PnLtotal_USDT + (PnLtotal / endprice)   '<--aggregating total

AssetValue_USDT = EndPos * (1 / endprice)    '<--converting to USD
TotalAssetValue = TotalAssetValue + AssetValue_USDT  '<--aggregating total
row = row + 1
Wend
PnLPercent = (PnLtotal_USDT / AssetValue_USDT)*100   '<--calcing profit and loss % 
wsPosition.Cells(7, 6) = PnLtotal_USDT   '<--assigning total
wsPosition.Cells(7, 7) = PnlPercent

End Sub

Answer 1

使用"Option Explicit">
您的"cells(.."命令应该引用wsPrice！

更新：
小的更改以反映代码的更改！

使用F8一步一步地完成您的程序！

Sub PnL2()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim PnLUSDT, PnLUSDT_percent As Range
Dim Asset As String
Dim BegPos, EndPos, startprice, endprice, unreal_PnL, Real_PnL As Long
Dim PriceDelta, PnLtotal_USDT, PnLtotal, AssetValue As Long
Dim AssetValue_USDT, TotalAssetValue, PnlPercent As Long
Dim row As Integer
Dim wbPosition As Workbook, wbPrice As Workbook
Dim wsPosition As Range
Dim wsPrice As Range
Set wbPosition = ThisWorkbook
Set wsPosition = wbPosition.Sheets(1).Range("A:G")
'Instead of these two lines
Set wbPrice = Workbooks.Open("C:UsersUserDesktopExcel ExerciseExcel Exercise 2 of 2.xlsx")
Set wsPrice = wbPrice.Worksheets("Sheet1").Range("A:C")
'I use this line and the program works with my data without and error!
'
'        Set wsPrice = Worksheets("table1").Range("A:C")

row = 2               '<--position sheet has heads, starting with data
TotalAssetValue = 0   '<--aggregate of Asset Value in loop
PnLtotal_USDT = 0     '<--aggregrate of Profit & Loss in loop
With wsPrice
While .Cells(row, 1) <> ""
Asset = .Cells(row, 1)        '<--security name
'Debug.Print "Asset: ", Asset
BegPos = .Cells(row, 2)       '<--start position in ws.Position
EndPos = .Cells(row, 3)       '<--end position in ws.Positon
'       With wsPrice
startprice = Application.VLookup(Asset, wsPrice, 2, 0)   '<--start price in ws.Price
endprice = Application.VLookup(Asset, wsPrice, 3, 0)     '<--end price in ws.Price

If startprice = endprice Then
PriceDelta = endprice     'Price change cannot be 0 for calc purposes
Else
PriceDelta = startprice - endprice
End If

unreal_PnL = EndPos * PriceDelta    '<--calcing unrealized P&L
Real_PnL = (BegPos - EndPos) * endprice   '<--calcing Real P&L
PnLtotal = unreal_PnL + Real_PnL   '<--totaling
PnLtotal_USDT = PnLtotal_USDT + (PnLtotal / endprice)   '<--aggregating total

AssetValue_USDT = EndPos * (1 / endprice)    '<--converting to USD
TotalAssetValue = TotalAssetValue + AssetValue_USDT  '<--aggregating total
row = row + 1
'        End With
Wend
End With
PnlPercent = (PnLtotal_USDT / AssetValue_USDT) * 100 '<--calcing profit and loss %
wsPosition.Cells(7, 6) = PnLtotal_USDT   '<--assigning total
wsPosition.Cells(7, 7) = PnlPercent
End Sub