CODEWITHSUNDEEP
vbaexcel
Chet
Chet

Reputation: 11

Baffling "Object Required" issue

It's entirely possible -- probable, even -- that I'm missing something here, but for the life of me I can't figure out what.

I have a situation where I need a user defined structure (or a class, I guess, but I'm trying to stay simple) to carry two related bits of data: the column name and column number where certain data lives.

However, when I run the code, I get an "object required" error on the first assignment attempt:

Here's the truncated listing:

Public Type ConfigDataType
    ltr As String
    nbr As Integer
End Type

Sub MacroWorkbookName()
    Dim ProjCol, WBSCol, ResCol, ValCol As ConfigDataType
    Call GetParameters(ProjCol, WBSCol, ResCol, ValCol)
End Sub

Sub GetParameters(ByRef P, W, R, V As ConfigDataType)

    Dim MacroWorkBook As String
    MacroWorkBook = ThisWorkbook.Name

    ' The next line triggers the error
    P.ltr = Workbooks(MacroWorkBook).Sheets("Configuration").Cells(6, 3)
    W.ltr = Workbooks(MacroWorkBook).Sheets("Configuration").Cells(7, 3)
    R.ltr = Workbooks(MacroWorkBook).Sheets("Configuration").Cells(8, 3)
    V.ltr = Workbooks(MacroWorkBook).Sheets("Configuration").Cells(9, 3)

End Sub

I'm either fundamentally misunderstanding something about VBA syntax, or I've missed some braindead error.

It's also possible I'm going about this All Wrong in terms of idiomatic Excel automation, in which case I also welcome nudges towards the True Path - the client will presumably use this for a while, and I'd hate for them to think ill of me for deploying crappy code.

Upvotes: 0

Views: 32

Answers (1)

Chet
Chet

Reputation: 11

TigerAvatar immediately saw my problem. I was assuming a line like

Dim foo, bar, bash, baz as ConfigType

would result in four variables of type ConfigType, but that's not the case. Only the last one is; the rest are of type variant. The correct usage is apparently

Dim foo as ConfigDataType, bar as ConfigDataType, bash as ConfigDataType, bas as ConfigDataType

and that extends to the definition of the sub, too, which would correctly be written

Sub GetParameters(ByRef P as ConfigDataType, W as ConfigDataType, R as ConfigDataType, V As ConfigDataType)

Once I fixed both of those issues, the code worked as I expected. Thanks again to Tigeravatar for such a prompt answer.

Upvotes: 1

Related Questions