Vba compile error invalid qualifier

This code is designed to take a spreadsheet of raw data, omit several columns, and reformat what remains. I welcome any and all critiques of the code as I am new to VBA and know nothing. The key pr...

I use this technique when I get confused about the type of object I have created by stringing properties.

Within Excel’s Visual Basic Editor, create a new module if you do not have an existing one that you wish to use in this way. If you select the module in Project Explorer and click F4, you can change the module’s name to «Experiments» say.

Type or copy:

Option Explicit
Sub TestA()

End Sub

I always start my modules with Option Explicit. Look Option Explicit up in VBA Help and it will tell you why this is a good idea.

I have also created an empty sub-routine into which I will type some statements.

Start typing a new statement so you have:

Sub TestA()

  Debug.Print Range("B:B").

End Sub

When you type the period at the end of this new line, a pop-up window will show you the available methods and properties. This list will show, as expected, all the methods and properties of a Range. Type «Address» or select Address from the list to get:

Sub TestA()

  Debug.Print Range("B:B").Address

End Sub

Click F5 to run this macro and the following will appear in the Immediate Window:

$B:$B

This is the address of all rows in column B which is what you would expect.

Now add two further statements to the macro:

  Debug.Print Range("F:F").Address
  Debug.Print Union(Range("B:B"), Range("F:F")).Address

Run this macro again and you will get:

$B:$B
$F:$F
$B:$B,$F:$F

Again this is what was expected.

Now add:

  Debug.Print Union(Range("B:B"), Range("F:F")).Rows.

The pop-up window that appears will be unchanged because Range.Rows is still a range.

Complete the statement by adding or selecting «Address» and run the macro again to get:

$B:$B
$F:$F
$B:$B,$F:$F
$B:$B,$F:$F

This may not be what you expected but think about it. $B:$B,$F:$F is all rows in columns B and F so adding the property Rows does not change the address.

Now add the following statements to the macro:

  Debug.Print Union(Range("B:B"), Range("F:F")).Count
  Debug.Print Union(Range("B:B"), Range("F:F")).Rows.Count

Run the macro and these statements will each output an integer. I am using Excel 2003 so I get:

 131072 
 65536 

If you are using a later version of Excel, you will get larger integers. The second integer is the number of rows in a worksheet for your version of Excel. The first integer is the number of cells in two columns of a worksheet for your version of Excel.

Now add:

  Debug.Print Union(Range("B:B"), Range("F:F")).Rows.Count.

When you type the final period, no pop-up window will appear because an integer has no method or property that you can select in this way. Method .End(xlUp) operates on a range; it is not a property of Count which is why you get «Invalid qualifier».

It is very easy to get oneself confused when stringing properties together. Personally I avoid stringing properties because even if it is faster to run, it takes longer for me to understand and debug. There are situations in which minimising runtime is the top priority but is this one of those cases? How many hours have you wasted with this approach?

Consider:

  Dim Rng1 As Range
  Dim Rng2 As Range
  Dim Rng3 As Range
  Dim RowMax As Long

  Set Rng1 = Range("B:B")
  Set Rng2 = Range("F:F")
  Set Rng3 = Union(Rng1, Rng2)
  RowMax = Rng3.Count

  Debug.Print RowMax

  Debug.Print Rng3.Find("*", Range("B1"), xlValues, xlWhole, xlByRows, xlPrevious).Row

You do not need RowMax but I have included it so you are absolutely clear what Rng3.Count returns. I have also gone OTT with the ranges. I would be happy to type: Set Rng3 = Union(Range("B:B"), Range("F:F")) because I find it easy to understand.

Method .End(xlUp) operates on a cell. MultiCellRange.End(xlUp).Row is valid syntax but I cannot get it to return useful information. If you want to use .End(xlUp) consider:

  Dim RowMaxColB As Long
  Dim RowMaxColF As Long

  RowMaxColB = Cells(Rows.Count, "B").End(xlUp).Row
  RowMaxColF = Cells(Rows.Count, "F").End(xlUp).Row

I agree with Siddharth, Find appears to be the best approach in this situation. However, you should look at this answer of mine, https://stackoverflow.com/a/20849875/973283, to a different question. It includes a macro that demonstrates a selection of methods of finding last rows and columns and shows the situations in which they fail.

pasha7598

0 / 0 / 0

Регистрация: 12.03.2020

Сообщений: 9

1

Excel

12.03.2020, 09:35. Показов 6645. Ответов 11

Метки vba (Все метки)


Значит, ситуация обстоит следующая. Имеется одна книга, в которой есть форма, помимо неё может быть несколько параллельно открытых книг. В пользовательской форме имеется два комбобокса, в первом из них нужно выбрать книгу, а во втором уже должны быть показаны листы выбранной книги в предыдущем комбобоксе.
Пока что остановился на таком коде, не понимаю почему на меня ругается этот вба:

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
Private Sub UserForm_Initialize()
    Dim WBook As Object
    Dim kniga As String
        For Each WBook In Workbooks
        Me.ComboBox1.AddItem (WBook.Name)
    Next
    kniga = ComboBox1.Text
        Dim Sheet As Object
    For Each Sheet In Workbooks(kniga.Name).Worksheets
        Me.ComboBox2.AddItem (Sheet.Name)
    Next
End Sub

__________________
Помощь в написании контрольных, курсовых и дипломных работ, диссертаций здесь



0



223 / 134 / 45

Регистрация: 08.09.2012

Сообщений: 283

Записей в блоге: 1

12.03.2020, 10:13

2

pasha7598, и вам здравствуйте!

Цитата
Сообщение от pasha7598
Посмотреть сообщение

Имеется одна книга

а у нас этой книги нет. Предлагаете за Вас создать книгу, вставить туда форму, создать контролы, вставить код?
По делу: почему Вы решили, что строковая переменная kniga должна вести себя как объект и иметь свойство Name?
Дальше не вникал…



1



0 / 0 / 0

Регистрация: 12.03.2020

Сообщений: 9

12.03.2020, 10:39

 [ТС]

3

Я бы с удовольствием прикрепил бы файл, но ведь вба сохраняется только в формате .xlsm, а здесь такой прикрепить нельзя, либо я чего-то недопонимаю.

По делу: почему Вы решили, что строковая переменная kniga должна вести себя как объект и иметь свойство Name?

То есть ошибка в том, что неправильный тип у переменной? Прошу у вас помощи, ибо сам не сильно шарю за это все. Просто если я даю ей As Object, оно тоже выдает ошибку «Object veriable or With block variable not set». Какой тип тогда нужно дать этой переменной? Либо может это как-то можно реализовать по-другому?



0



Модератор

Эксперт MS Access

11271 / 4601 / 740

Регистрация: 07.08.2010

Сообщений: 13,200

Записей в блоге: 4

12.03.2020, 10:41

4

Цитата
Сообщение от pasha7598
Посмотреть сообщение

только в формате .xlsm, а здесь такой прикрепить нельзя, либо я чего-то недопонимаю.

зазипуйте .xlsm и выкладывайте архив



1



pashulka

4129 / 2233 / 940

Регистрация: 01.12.2010

Сообщений: 4,624

12.03.2020, 10:45

5

Visual Basic
1
2
3
4
5
6
7
dim wb as workbook, ws as worksheet
for each wb in workbooks
    combobox1.additem wb.name
    for each ws in wb.worksheets
        combobox2.additem ws.name
    next
next



2



🙋 🐗

3286 / 2882 / 663

Регистрация: 13.04.2015

Сообщений: 6,814

12.03.2020, 10:45

6

Цитата
Сообщение от pasha7598
Посмотреть сообщение

kniga.Name

Уберите Name



1



amd48

828 / 459 / 79

Регистрация: 18.05.2016

Сообщений: 1,229

Записей в блоге: 4

12.03.2020, 10:48

7

в строке 9
kniga.Name
замените на
kniga

И вообще эта kniga не очень-то и нужна:

Visual Basic
1
2
3
4
5
6
7
8
9
10
Private Sub UserForm_Initialize()
    Dim WBook As Workbook
    Dim Sheet As Worksheet
    For Each WBook In Workbooks
        Me.ComboBox1.AddItem (WBook.Name)
        For Each Sheet In WBook.Worksheets
            Me.ComboBox2.AddItem (Sheet.Name)
        Next
    Next
End Sub

Ну а задавать вопросы на форуме следует в таком стиле:
— прикладывание файла
— сообщение об ошибке полностью: номер ошибки и текст. Можно просто скриншот
— номер строки кода, на которой выполнение остановилось с этой ошибкой

апаздал…



1



1786 / 1114 / 340

Регистрация: 11.07.2014

Сообщений: 3,928

12.03.2020, 10:50

8

aequit, да и в ComboBox1.Text при инициализации ничего ещё нет



1



0 / 0 / 0

Регистрация: 12.03.2020

Сообщений: 9

12.03.2020, 23:43

 [ТС]

9

Спасибо большое, практически то, что я и хотел, не ожидал таких быстрых ответов, вообще впервые оказался на форуме этом. Но имеется еще один вопрос. Книг может быть несколько, но как реализовать так, чтобы при выборе книги, были видны листы именно из неё, а из остальных книг не высвечивались.



0



828 / 459 / 79

Регистрация: 18.05.2016

Сообщений: 1,229

Записей в блоге: 4

13.03.2020, 07:11

10

ActiveWorkbook



0



pashulka

4129 / 2233 / 940

Регистрация: 01.12.2010

Сообщений: 4,624

13.03.2020, 08:44

11

Лучший ответ Сообщение было отмечено pasha7598 как решение

Решение

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
Private Sub UserForm_Initialize()
    Dim wb As Workbook
    For Each wb In Workbooks
        ComboBox1.AddItem wb.Name
    Next
    ComboBox1.Style = fmStyleDropDownList 'Можно установить вручную
End Sub
 
Private Sub ComboBox1_Change()
    Dim ws As Worksheet: ComboBox2.Clear
    For Each ws In Workbooks((ComboBox1)).Worksheets
        ComboBox2.AddItem ws.Name
    Next
End Sub



0



0 / 0 / 0

Регистрация: 12.03.2020

Сообщений: 9

13.03.2020, 09:03

 [ТС]

12

Хорошо, а как сделать тогда, чтобы была выбрана книга, а потом из нее был выведен список листов? То есть как мне сделать выбранную через комбобокс книгу активной?

Добавлено через 6 минут
Благодарю, как раз то, что было нужно.



0



You may have encountered an error code indicating an invalid qualifier for a Microsoft Visual Basic compilation error. There are several steps you can take to fix this problem. Let’s discuss it now.

PC running slow?

  • 1. Download ASR Pro from the website
  • 2. Install it on your computer
  • 3. Run the scan to find any malware or virus that might be lurking in your system
  • Improve the speed of your computer today by downloading this software — it will fix your PC problems.

    This error has the above cause and solution: the qualifier should not define an ideal design, module, object, or UDT variable within a valid range. Check the spelling of certain qualifiers. Make sure the corresponding ID is indeed in the current range.

    Used qualifiers continue to serve to clarify terms. This error identifies the cause and solution:

    • The qualifier does not define a project , module , object or huge variable user-defined type in the most recent range .

      Check the spelling of the qualifier. Make sure the corresponding id it contains is the current scope. For example, a UDT type variable in a private module will probably not be visible only in that module.

    For more care, select the appropriate item and press F1 (Windows or Windows) HELP (current Macintosh).

    Support Comments

    Do you have questions or comments about Office VBA or thisdocumentation? Visit Office VBA Support & Feedback for tips and tricks on how to get help and provide feedback.

    • Article
    • 2 minutes to read.

    I use this method when I don’t understand how to get the type of the object that I collected by combining properties.

    If someone doesn’t have an existing module that you want to use this way, create a new module in the Excel Visual Basic Editor. If you select a module in the project explorer and press F4, you can pronounce the name of the module in Experiments.

      Explicit parameterSubtest A ()Still under 

    What is object required error in VBA?

    Required Life object in Excel VBA. A required object is any runtime error when we set a variable that would not be an object, but try to assign values ​​using the SET fact. This error occurs at runtime for a variety of reasons.

    I am launching my modules with Option Explicit . Search for Option Explicit in the VBA help and it will show you why it fits any idea.

    I also created an empty subroutine into which I will insert some instructions.

      SubtestA ()  Debug.Print Range ("B: B").The end of the submarine 

    If you keep typing period at the end of this awesome line, a popup will show you the available methods and properties. As expected, this listing showsall our methods and properties are ranked. Enter “address” or select an address from this list to get:

    microsoft visual basic compile error invalid qualifier

      SubtestA ()  Debug.Print Range ("B: B"). The addressThe end of the submarine 

    Press F5 to run this macro. The next window displays the following:

      $ B: $ B 

    This is the address of all expected series in column B.

      Debug.Print Range ("F: F"). The address  Debug.Print Union (Range ("B: B"), Range ("F: F")). 

    microsoft visual basic compile error invalid qualifier

      $ B: $ B$ F: $ F$ B: $ B, $ F: $ F 
      Debug.Print Union (Range ("B: B"), Range ("F: F")). Rows. 

    The popup remains unchanged because Range.Rows is still a range.

    PC running slow?

    ASR Pro is the ultimate solution for your PC repair needs! Not only does it swiftly and safely diagnose and repair various Windows issues, but it also increases system performance, optimizes memory, improves security and fine tunes your PC for maximum reliability. So why wait? Get started today!

    Complete the statement by adding or selecting Address and calling the macro again to get:

      $ B: $ B$ F: $ F$ B: $ B, $ F: $ F$ B: $ B, $ F: $ F 

    This may be less than expected, but it is related. $ b: $ b, $ f: $ f – all rows in columns B and F only, so adding a new property to n ‘rows does not include the change address part.

      Debug.Print Union (Range ("B: B"), Range ("F: F")).  Debug.Print Union (Range ("B: B"), Range ("F: F")). Rows.Count 

    Run a macro and each of theseX instructions will also be the output integer. When I have an Excel 2003 application I get:

      131072 65536 

    If you are using a newer version of Excel, these are large integers. The second became an integer, the number of rows on a specific sheet for your version of Excel. The first integer is the number of cells in two columns of the worksheet in your version of Excel.

      Debug.Print Union (Range ("B: B"), Range ("F: F")). Rows.Count. 

    If you enter the last period, the popup will not appear because the integer does not have a method or property that your organization can select this way. The .End (xlUp) method works with a zone; it’s not just a Count property, so you’ll definitely get an Invalid Qualifier.

    What is type mismatch error in VBA?

    The VBA type mismatch error occurs when you try to allow them to assign a value between two different flexible types. The error is displayed as “Runtime Error: Thirteen Types Mismatch”. For example, if you are trying to put text in a long integer variable or if you need to put text in a date variable.

    Every time you chain objects together, it’s incredibly easy to get lost. Personally, I stick with string properties because while it is faster it takes me longer to understand and debug. There are situations where reducing runtime is a top priority, but is this one such case? How many hours did you spend on this papproach?

      Dim Rng1 As Range  Dim Rng2 like a zone  Dim Rng3 like a zone  Dim RowMax until then  Set Rng1 = Range ("B: B")  Define Rng2 means range ("f: f") Set Rng3 = Union (Rng1, Rng2)  RowMax = Rng3.Count  Debug Print RowMax  Debug.Print Rng3.Range ("B1"), find ("*", xlValues, xlWhole, xlByRows, xlPrevious) .Row 

    You don’t actually need RowMax , but I’ve included it so you can see exactly what Rng3.Count returns. I could even go to OTT with zones. I would like to enter: Set equals rng3 Union (Range ("B: B"), Range ("F: F")) because I find the game easy to understand.

    The .End (xlUp) method works with a huge cell. MultiCellRange.End (xlUp) .Row is valid syntax, except I can’t get to very important information. If you want to draw on .End (xlUp) , please note:

      Dim As rowmaxcolb Long  Dim RowMaxColF until  RowMaxColB = Cells (Rows.Count, "B"). End (xlUp) .Row  RowMaxColF = Cells (Rows.Count, "F"). End (xlUp) .Row 

    I agree with Siddhart, Find seems to be the best approach under the circumstances. However, you should check out such an answer from me, https://stackoverflow.com/a/20849875/973283, for a completely different question. It containsa macro that demonstrates various methods of getting the last rows and columns and shows in which situations they do not work.

    Improve the speed of your computer today by downloading this software — it will fix your PC problems.

    Comment Résoudre Un Qualificatif Non Valide Pour Une Erreur De Compilation Microsoft Visual Basic
    Microsoft Visual Basic 컴파일 오류에 대한 잘못된 한정자를 해결하는 방법
    So Beheben Sie Einen Ungültigen Qualifizierer Für Einen Microsoft Visual Basic-Kompilierungsfehler
    Как устранить новый недопустимый квалификатор для ошибки компиляции Microsoft Visual Basic
    Cómo Resolver Un Calificador No Válido Para Un Error De Compilación De Microsoft Visual Basic
    Jak Rozwiązać Nieprawidłowy Kwalifikator W Przypadku Błędu Kompilacji Microsoft Visual Basic
    Come Questo Aiuterà A Risolvere Un Qualificatore Non Valido Per Un Errore Di Compilazione Di Microsoft Visual Basic
    Hoe Een Ongeldige Kwalificatie Voor Een Microsoft Visual Basic-compilatiefout Op Te Lossen
    Como Resolver Um Qualificador Inválido Para Um Erro De Compilação Do Microsoft Visual Basic
    Hur Dags För Att Lösa Ett Ogiltigt Kvalificerande För Ett Microsoft Visual Basic-kompileringsfel

    I use this technique when I get confused about the type of object I have created by stringing properties.

    Within Excel’s Visual Basic Editor, create a new module if you do not have an existing one that you wish to use in this way. If you select the module in Project Explorer and click F4, you can change the module’s name to «Experiments» say.

    Type or copy:

    Option Explicit
    Sub TestA()
    
    End Sub
    

    I always start my modules with Option Explicit. Look Option Explicit up in VBA Help and it will tell you why this is a good idea.

    I have also created an empty sub-routine into which I will type some statements.

    Start typing a new statement so you have:

    Sub TestA()
    
      Debug.Print Range("B:B").
    
    End Sub
    

    When you type the period at the end of this new line, a pop-up window will show you the available methods and properties. This list will show, as expected, all the methods and properties of a Range. Type «Address» or select Address from the list to get:

    Sub TestA()
    
      Debug.Print Range("B:B").Address
    
    End Sub
    

    Click F5 to run this macro and the following will appear in the Immediate Window:

    $B:$B
    

    This is the address of all rows in column B which is what you would expect.

    Now add two further statements to the macro:

      Debug.Print Range("F:F").Address
      Debug.Print Union(Range("B:B"), Range("F:F")).Address
    

    Run this macro again and you will get:

    $B:$B
    $F:$F
    $B:$B,$F:$F
    

    Again this is what was expected.

    Now add:

      Debug.Print Union(Range("B:B"), Range("F:F")).Rows.
    

    The pop-up window that appears will be unchanged because Range.Rows is still a range.

    Complete the statement by adding or selecting «Address» and run the macro again to get:

    $B:$B
    $F:$F
    $B:$B,$F:$F
    $B:$B,$F:$F
    

    This may not be what you expected but think about it. $B:$B,$F:$F is all rows in columns B and F so adding the property Rows does not change the address.

    Now add the following statements to the macro:

      Debug.Print Union(Range("B:B"), Range("F:F")).Count
      Debug.Print Union(Range("B:B"), Range("F:F")).Rows.Count
    

    Run the macro and these statements will each output an integer. I am using Excel 2003 so I get:

     131072 
     65536 
    

    If you are using a later version of Excel, you will get larger integers. The second integer is the number of rows in a worksheet for your version of Excel. The first integer is the number of cells in two columns of a worksheet for your version of Excel.

    Now add:

      Debug.Print Union(Range("B:B"), Range("F:F")).Rows.Count.
    

    When you type the final period, no pop-up window will appear because an integer has no method or property that you can select in this way. Method .End(xlUp) operates on a range; it is not a property of Count which is why you get «Invalid qualifier».

    It is very easy to get oneself confused when stringing properties together. Personally I avoid stringing properties because even if it is faster to run, it takes longer for me to understand and debug. There are situations in which minimising runtime is the top priority but is this one of those cases? How many hours have you wasted with this approach?

    Consider:

      Dim Rng1 As Range
      Dim Rng2 As Range
      Dim Rng3 As Range
      Dim RowMax As Long
    
      Set Rng1 = Range("B:B")
      Set Rng2 = Range("F:F")
      Set Rng3 = Union(Rng1, Rng2)
      RowMax = Rng3.Count
    
      Debug.Print RowMax
    
      Debug.Print Rng3.Find("*", Range("B1"), xlValues, xlWhole, xlByRows, xlPrevious).Row
    

    You do not need RowMax but I have included it so you are absolutely clear what Rng3.Count returns. I have also gone OTT with the ranges. I would be happy to type: Set Rng3 = Union(Range("B:B"), Range("F:F")) because I find it easy to understand.

    Method .End(xlUp) operates on a cell. MultiCellRange.End(xlUp).Row is valid syntax but I cannot get it to return useful information. If you want to use .End(xlUp) consider:

      Dim RowMaxColB As Long
      Dim RowMaxColF As Long
    
      RowMaxColB = Cells(Rows.Count, "B").End(xlUp).Row
      RowMaxColF = Cells(Rows.Count, "F").End(xlUp).Row
    

    I agree with Siddharth, Find appears to be the best approach in this situation. However, you should look at this answer of mine, https://stackoverflow.com/a/20849875/973283, to a different question. It includes a macro that demonstrates a selection of methods of finding last rows and columns and shows the situations in which they fail.

    • #2

    Code:

    [COLOR=#333333]c.Value = maxv.Value[/COLOR]
    [COLOR=#333333]c.Address = maxa.Address[/COLOR]

    You’re asking the Value and Address of two variables you already explicitly made Values and Addresses in

    Code:

    [COLOR=#333333]maxv = numberrange.Cells(1, 1).Value[/COLOR]
    [COLOR=#333333]maxa = numberrange.Cells(1, 1).Address[/COLOR]

    Furthermore, the .Address property will return the column(s) and row(s) of whatever range it’s attached to. Asking the address from Cells(1,1) will always return the same address, being $A$1.

    Last edited: Jan 17, 2019

    • #3

    When I execute the VBA code that is given below, I get an error «Compiler Error: Invalid Qualifier» and the name of subprocedure gets highlighted. Why does this happen? I have attached a copy of the code below:

    Option Explicit
    Sub TestA()

    Dim numberrange As Range
    Dim c As Range, maxv As Long, maxa As String

    Set numberrange = Application.InputBox(«Select a range to find max value», «Max Value», , , , , , 8)

    maxv = numberrange.Cells(1, 1).Value
    maxa = numberrange.Cells(1, 1).Address

    For Each c In numberrange.Value
    If c.Value > maxv.Value Then
    c.Value = maxv.Value
    c.Address = maxa.Address
    End If
    Next c

    MsgBox («max:» & maxv)

    End Sub

    The variable maxv is declared as Long… it does not have a Value property. Try removing the .Value from the two red highlighted text above.

    Similarly, the variable maxa is declared as a String… it does not have an Address property. Try removing the .Address from the blue highlighted text above.

    • #4

    An alternative for you to consider after resolving the issues in your current code
    — avoids looping
    — uses Max function

    Code:

    Sub TestB()
        Dim NumberRange As Range, c As Range, v As Range, maxV As Double
        Set NumberRange = Application.InputBox("Select a range to find max value", "Max Value", , , , , , 8)
        maxV = WorksheetFunction.Max(NumberRange)
        Set v = NumberRange.Find(maxV, LookAt:=xlWhole)
        MsgBox "Max value = " & maxV & " in " & v.Address(0,0)
    End Sub

    Last edited: Jan 17, 2019

    Понравилась статья? Поделить с друзьями:
  • Vba 400 error
  • Vast error вики персонажи
  • Vast error twitter
  • Vast error eng
  • Valueerror mean squared logarithmic error cannot be used when targets contain negative values