CODEWITHSUNDEEP
vbaexcel
Sijie Huang
Sijie Huang

Reputation: 21

VBA usedrange run-time error 438 object doesn't support this property or method

I am working on a macro involving usedrange. Basically my sub tries to open up a text file with opentext and copy the formulas in the text file and paste over to the worksheet "SOE Summary" of my destination workbook. However, a run-time error 438 would occur when I try to set the formulas of my worksheet "SOE Summary" equal to the formulas in the text file. Any insight would be appreciated.

Sub Sample()
Application.ScreenUpdating = False 
Dim WB As Workbook
Set WB = ActiveWorkbook 


Dim fileToOpen As Variant 'Txt file that contains formulas
Dim fileName As String _
'Name of the temporary workbook that contains formulas from fileToOpen
Dim sheetName As String _
'Name of the worksheet of the temporary workbook that contains formulas from fileToOpen
fileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt") _
'User selects the txt file that contains formulas
If fileToOpen <> False Then 'Make sure the user selects a txt file
    Workbooks.OpenText fileName:=fileToOpen, _
    DataType:=xlDelimited, Tab:=True 'Transport formulas into a temporary workbook
End If
fileName = Mid(fileToOpen, InStrRev(fileToOpen, "\") + 1) _
'Name of the temporary workbook
sheetName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) _
'Name of the temporary worksheet


Application.DisplayAlerts = False 'Disable any popup windows
WB.Sheets("SOE Summary").UsedRange.Value = Workbooks(fileName).Sheets(sheetName).UsedRange.Formula_
'[ERROR 438 OCCURRED] Paste formulas into "SOE Summary" worksheet
Application.DisplayAlerts = True 'Enable popup windows after the previous line of code


Workbooks(fileName).Close savechanges:=False 'Close the temporary workbook and discard any changes
WB.Sheets("SOE Summary").UsedRange.Value = WB.Sheets("SOE Summary").UsedRange.Value _
'Replace all formulas in the "SOE Summary" worksheet with values
Application.ScreenUpdating = True 

End Sub

Upvotes: 2

Views: 1038

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71177

In VBA an instruction is terminated either by a new line, or if you want multiple instructions on a single line, by an instruction separator token (i.e. a colon : - not that you typically want multiple instructions on a single line).

A line of code in VBA can be seen as either a logical code line, or a physical code line. When a code line gets too wide/lengthy, you can continue a logical line of code one the next physical line of code using the line continuation token (a whitespace followed by an underscore _).

As was pointed out, most of these lines don't need to be continued - in fact, your code only compiles because the continued lines "continue" into a comment, which implicitly terminates the instruction:

fileName = Mid(fileToOpen, InStrRev(fileToOpen, "\") + 1) _
'Name of the temporary workbook
sheetName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) _

Remove these comments and your code stops compiling:

fileName = Mid(fileToOpen, InStrRev(fileToOpen, "\") + 1) _
sheetName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) _

...because you can't have two assignments in the same instruction.

So yeah, remove the extraneous line continuations, and problem solved.

The specific error you're getting is rather confusing, because UsedRange returns an actual Range object reference, which gives you all rights to expect a compile-time error here instead of run-time. Under the hood, this points to the Range COM interface being extensible - meaning although most member calls are early-bound/resolved at compile-time, VBA lets you make any funky member call on a Range object, and defers validation to run-time. Proof:

Sub Foo()
    Dim bar As Range
    bar.Whatever = 42 ' happily compiles
End Sub

This means you need to be especially careful for typos when doing Range member calls, because even Option Explicit isn't going to help you with that - in other words, use IntelliSense / auto-completion whenever referring to known members, to avoid such errors.

Since in VBA identifiers can very well end with an underscore (e.g. Formula_), it's only at run-time that VBA queries the Range interface and discovers that there's no such thing as a Formula_ - and boom, error 438.

Upvotes: 3

CallumDA
CallumDA

Reputation: 12113

A few things:

  • _ is used to indicate that a single line statement will continue on the next line. It is rarely needed and you will see below, it's actually not necessary anywhere in your subroutine
  • In that vein, your actual issue is that you have UsedRange.Formula_ rather than UsedRange.Formula _ on your error line. Note the missing space
  • Your commenting is cluttering your code a bit. Try to only comment on things that explain the less obvious features of your code. Your variables are well named so you don't need to comment that fileName is 'Name of the temporary workbook, for example.
Sub Sample()
    Application.ScreenUpdating = False
    Dim WB As Workbook
    Set WB = ActiveWorkbook

    Dim fileToOpen As Variant
    Dim fileName As String, sheetName As String

    fileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")

    If fileToOpen <> False Then
        Workbooks.OpenText fileName:=fileToOpen, DataType:=xlDelimited, Tab:=True
    End If

    fileName = Mid(fileToOpen, InStrRev(fileToOpen, "\") + 1)
    sheetName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)

    Application.DisplayAlerts = False
    WB.Sheets("SOE Summary").UsedRange.Value = Workbooks(fileName).Sheets(sheetName).UsedRange.Formula
    Application.DisplayAlerts = True

    Workbooks(fileName).Close savechanges:=False

    WB.Sheets("SOE Summary").UsedRange.Value = WB.Sheets("SOE Summary").UsedRange.Value
    Application.ScreenUpdating = True
End Sub

Upvotes: 2

Related Questions