CODEWITHSUNDEEP
excelvba
blau
blau

Reputation: 55

VBA Left function issues

I,m trying to use left function like in this basic example code and it gives me compile error,However it works when I use Right function instead of Left can figure out why.

Example code:

Public Sub LeftSub()
Dim SourceRange As Range, DestinationRange As Range
Dim i As Integer

Set SourceRange = Workbooks("xx.xlsx").Sheets("Sheet1").Range("B1:B10")  
Set DestinationRange = Workbooks("xx.xlsx").Sheets("Sheet1").Range("A1:A10")

For i = 1 To SourceRange.Count
DestinationRange(i, 1).Value = Left(SourceRange(i, 1).Value, 2)
Next i
End Sub

Upvotes: 1

Views: 7716

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Left could resolve to (in that order):

  1. A local variable named Left
  2. A module-scoped variable, or a member in the same module
  3. A member in the base class (e.g. UserForm.Left in a form module)
  4. A public/global member in a standard module of the same project
  5. A public/global member in the VBA standard library or any referenced project (in the order specified in the project references dialog)

The first match is the one that is used, regardless of its signature:

IntelliSense showing how Left resolves to a same-module Left function even with a completely different signature

This function's identifier is hiding the built-in one. To access the built-in Left function, you need to qualify it now:

Debug.Print VBA.Strings.Left(...)

Slightly unrelated, but notice VBA.Strings.Left returns an implicit Variant:

enter image description here

And its little strongly-typed little brother returns an explicit String:

enter image description here

And therefore avoids the cost of implicit type conversions; prefer Left$ to Left whenever the String parameter can never be Null - the two functions are very similar, except for this:

Debug.Print Left(Null, 1)  ' outputs Null
Debug.Print Left$(Null, 1) ' raises runtime error 94 - Invalid use of Null

That said unless you're dealing with , you'll probably never encounter a Null in VBA.

Upvotes: 4

Vityata
Vityata

Reputation: 43585

This works for me w/o compile error:

Option Explicit

Public Sub LeftSub()
    Dim SourceRange As Range, DestinationRange As Range
    Dim i As Integer

    Set SourceRange = Range("B1:B10")
    Set DestinationRange = Range("A1:A10")

    For i = 1 To SourceRange.Count
        DestinationRange(i, 1).Value = Left(SourceRange(i, 1).Value, 2)
    Next i

End Sub

Upvotes: 0

Related Questions