Invalid qualifier vba excel ошибка
Hello, I have the code below and am trying to have a a form allow Text Box to become visible based on selected items from a List box. Can some one please help as I am getting the error listed?
Nothing In Moderation
[sigh] sigh [/sigh]
The use of GoTo with a label for decision making should be re-evaluated.
For Starters:
Does your code module have
Option Explicit
at the top of the module?
If not, add this line then recompile the code page.
Repeat for every code module.
2nd line of code add:
On Error GoTo ErrTrap
2nd to bottom line just above the End Sub add:
Exit Sub
ErrTrap:
msgbox Err.desription
Now what happens?
Mihail
Registered User.
Just an add to what RX suggested:
Replace 169 to (say) lbl169 . Use this exact syntax, with NO spaces between the word lbl and the word 169.
Nothing In Moderation
If it worked, please post your changed code.
Use the Code tags around the code.
open square bracked CODE close square brackets
your code
open square bracket /CODE close square brackets
Also, as the poster, go to your first post, under thread tools, mark your thread as SOLVED
This will help many others later who are searching for a solution
I remember Romania well. In High-School (way before Internet), I joined a international pen-pal organization.
Established a regular coorespondence with a girl my age in Romania. Sent her posters of Rock & Roll and other things in the 1960's.
A few years later, I was undergoing what someone might call a fairly high-level background check for the then Cold War efforts.
On the application, the question about communicating or relationships with a member of the Communist Party was always answered No, truthfully by me.
After totally passing extensive polygraph test, fustrating those running the test, they proceeded to confront me with every pen pal coorespondence date and detailed content.
My Romanian girl friend failed to mention that daddy was someone in the top ten government offical positions, tied to the Kremlen.
That was just a little more shocking than a "dear john" letter. Being interrigated about things a teen would discuss with each other during teen year. whew!
Mihail
Registered User.
@Rx
I'm glad because you remember a small country as Romania but, even if I don't understand (or speak) very well English, I am very sorry that what you remembers seems to not be too good.
All the bests !
dgunbreakable
New member
When I add "Option Explicit" I get Compile Error: Invalid inside procedure. This code is not being run in a module, its part of an event on a form. I tried to run it with the other code the you provided, but it skips the error handler breaks the code and goes to the Invalid Qualifier error.
Mihail
Registered User.
Keep the Option Explicit but remove the error handler.
Run the code.
In what line the code is stopped ? What is the error message (again) ?
dgunbreakable
New member
Same error at the same spot:
Compile Error: Invalid qualifier
ctrler.Visible = True
Mihail
Registered User.
Hm
ctrler is a STRING
Dim users As Control
Dim ctrler As String
Dim xx As Long
.and a string can't be visible or invisible.
So, if your text box is named also "ctrler" change one of this names.
Good luck !
dgunbreakable
New member
Thank you Mahail, you got me looking in the right direction. it was actually the xx variable that I was using in the loop to try to assign the different text box names. unless there is a better way to assign the variables I will have to spell them out so I took out the loop and am going to use this ugly bit of code.
I'm new to VBA. Excel more my stuff. I've put together a template, that I want the users, who will be working with it, can save as new workbook. It has to save based on 3 cell values. 1st 2 cell value must be folder and sub folder, the 3rd cell, the new workbook name. For the reason that I kept on getting errors, I've taken out the 2nd cell value out of my first part of the code to see if I can just create the "parent" folder with new workbook.
The error (marked in red, below) I get is "Compile error: Invalid qualifier", if I change strMnf to Filename, I get "Compile error: Object required", If anybody can help, please?
Also some tips on how to, once a user has entered data into Cell C7, to automatically run the code. The code must stay within the template and not be save as part of the new workbook, if it is possible?
Dim wb As Thisworkbook
Dim Activesheet As Worksheet
Dim strMnf As String: strMnf = Range("C5").Value
'Declare / create variable data type.
'or object (but then you'll miss out on intellisense), and both can also contain more than one cell.
Dim oCell As Range: strMnf = Range("C5")
Set oCell = Range("C5")
'Assign Folder Name to Foldername.
Dim Path As String
Dim Folder As String
Dim filename: strMnf = Range("C5").Value
Dim Answer As VbMsgBoxResult
'Update the path to a valid path on your PC
Path = "E:\01.Laboratory\03.LAB TEST RESULTS\strMnf"
Folder = Dir(Path, vbDirectory)
If Folder = vbNullString Then
Answer = MsgBox("Path does not exist. Would you like to create it?", vbYesNo, "Create Path?")
Select Case Answer
MsgBox "Folder exists."
Dim Thisworkbook As String
Dim Activesheet As String
Dim strMnf As String: strMnf = Range("C5").Value
Dim strGrade As String: strGrade = Range("C6").Value
Dim filename As String: filename = Range("C7").Value
'Update the path to a valid path on your PC
filename = VBA.FileSystem.Dir("E:\01.Laboratory\03.LAB TEST RESULTS\strMnf\strGrade\filename_*start.xlsm?")
If filename = VBA.Constants.vbNullString Then
MsgBox "file does not exist."
'Update the path to a valid path on your PC
Workbooks.Open "E:\01.Laboratory\03.LAB TEST RESULTS\strMnf\strGrade & FileName"
[Moved from Excel / Windows 10 / Microsoft 365 Apps or Office 365 Business]
This thread is locked. You can follow the question or vote as helpful, but you cannot reply to this thread.
Report abuse
Harassment is any behavior intended to disturb or upset a person or group of people. Threats include any threat of suicide, violence, or harm to another. Any content of an adult theme or inappropriate to a community web site. Any image, link, or discussion of nudity. Any behavior that is insulting, rude, vulgar, desecrating, or showing disrespect. Any behavior that appears to violate End user license agreements, including providing product keys or links to pirated software. Unsolicited bulk mail or bulk advertising. Any link to or advocacy of virus, spyware, malware, or phishing sites. Any other inappropriate content or behavior as defined by the Terms of Use or Code of Conduct. Any image, link, or discussion related to child pornography, child nudity, or other child abuse or exploitation.
Also some tips on how to, once a user has entered data into Cell C7, to automatically run the code. The code must stay within the template and not be save as part of the new workbook, if it is possible?
Only if the file is saved as XLSX, but then all code is gone.
But as a new file is not saved we can skip the process and so the file is saved only once.
Right-click on the sheet tab
Choose "View Code"
Paste in the code below
Close the VBA editor
Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Thisworkbook.Saved Then Exit Sub
Select Case Target.Address(0, 0)
Case "C7"
PerformSaveAs
End Select
End Sub
Sub PerformSaveAs()
Dim strMnf As String, strGrade As String
Dim FilePath As String, FileName As String, FullName As String
Dim Extension As String
Dim Answer As VbMsgBoxResult
Dim i As Long
Dim FileFormat As XlFileFormat
'Be sure each path contains a backslash at the end
strMnf = AddBackSlash(Range("C5").Value)
strGrade = AddBackSlash(Range("C6").Value)
FileName = Range("C7").Value
i = InStrRev(FileName, ".")
If i = 0 Then
Extension = ".xlsm"
FileName = FileName & Extension
Else
Extension = Mid$(FileName, i)
End If
Select Case Extension
Case ".xlsx"
FileFormat = xlOpenXMLWorkbook
Case ".xlsm"
FileFormat = xlOpenXMLWorkbookMacroEnabled
Case Else
MsgBox "Invalid file type", vbCritical
Exit Sub
End Select
FilePath = strMnf & strGrade
'Already there?
If Dir(FilePath) = "" Then
'No
Answer = MsgBox("Path does not exist. Would you like to create it?", vbYesNo, "Create Path?")
If Answer <> vbYes Then Exit Sub
'Create it
If Not FolderCreate(FilePath) Then
MsgBox "No able to create that path", vbCritical
Exit Sub
End If
End If
FullName = FilePath & FileName
If Dir(FullName) <> "" Then
Answer = MsgBox("File exists. Overwrite?", vbYesNo)
If Answer <> vbYes Then Exit Sub
End If
Application.DisplayAlerts = False
Thisworkbook.SaveAs FilePath & FileName, FileFormat
Application.DisplayAlerts = True
End Sub
Private Function AddBackSlash(ByVal Path As String) As String
'Add a backslash if necessary
With Application
If Right$(Path, 1) <> .PathSeparator Then Path = Path & .PathSeparator
End With
AddBackSlash = Path
End Function
Private Function FolderCreate(ByVal Path As String) As Boolean
'Creates a complete sub directory structure
Dim Temp, i As Integer
On Error GoTo ExitPoint
If Dir(Path, vbDirectory) = "" Then
If Right$(Path, 1) = "\" Then Path = Left$(Path, Len(Path) - 1)
If Left$(Path, 2) = "\\" Then
i = InStr(3, Path, "\")
Temp = Split(Mid$(Path, i + 1), "\")
Temp(0) = Left$(Path, i) & Temp(0)
Else
Temp = Split(Path, "\")
End If
Path = ""
For i = 0 To UBound(Temp)
Path = Path & Temp(i) & "\"
If Dir(Path, vbDirectory) = "" Then MkDir Path
Next
End If
FolderCreate = True
ExitPoint:
End Function
Report abuse
Harassment is any behavior intended to disturb or upset a person or group of people. Threats include any threat of suicide, violence, or harm to another. Any content of an adult theme or inappropriate to a community web site. Any image, link, or discussion of nudity. Any behavior that is insulting, rude, vulgar, desecrating, or showing disrespect. Any behavior that appears to violate End user license agreements, including providing product keys or links to pirated software. Unsolicited bulk mail or bulk advertising. Any link to or advocacy of virus, spyware, malware, or phishing sites. Any other inappropriate content or behavior as defined by the Terms of Use or Code of Conduct. Any image, link, or discussion related to child pornography, child nudity, or other child abuse or exploitation.
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 problem is at the end of the code on the line indicated with astriscs below. This is the spot where the "Compile Error: Invalid Qualifier" comes up. I'm trying to apply the formatting to Columns B and F, but I only want it to go as far as the last row of date. The last row of data will vary from one sheet to the next.
When the error is triggered, the debugger highlights the word "count".
Thanks in advance for your help.
2 Answers 2
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.
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:
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:
Click F5 to run this macro and the following will appear in the Immediate Window:
This is the address of all rows in column B which is what you would expect.
Now add two further statements to the macro:
Run this macro again and you will get:
Again this is what was expected.
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:
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:
Run the macro and these statements will each output an integer. I am using Excel 2003 so I get:
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.
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?
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:
@L42. Thanks. It may be over 10 years since I first learnt Excel VBA but I still remember how confused I was with the object model.
Thanks to all for your contributions here. Tony, I greatly appreciate the time you took to share your knowledge. BAD CODE INDEED. LOL I have LOTS to learn. Thanks for your help! There is a lot to absorb here.
You have not asked the question to which this is an answer. See Requested answer for the answer you requested. Your code works so I doubt you know it is bad VBA.
From the comment at the top, I assume this code was created by the Macro Recorder. The Macro Recorder outputs syntactically correct code but it is poor code. At least part of the reason for the poor code is that statements are being recorded as you type. The Recorder does not know your objective. If the cursor is in cell A1 and you press Right , the Recorder will select B1 because that is what happens on the screen. There may be situations in VBA for which Select is a useful method but they are rare.
The Recorder also operates on the active worksheet which is rarely a good idea. If the wrong worksheet is active when this macro is started, it will be destroyed with 13 columns deleted.
I will assume you only have one workbook open so I can assume the active workbook is the required workbook. It is only a little more complicated to handle multiple workbooks but I would rather ignore that complication.
With Worksheets("Sheet1") states this code is to operate on the worksheet named "Sheet1" of the active workbook. End With terminates a With statement.
You can nest With statements:
The period at the beginning of .Range("A1") states .Range("A1") is to operate within the current With which is Worksheets("Sheet1") . If I omit that period, Range("A1") operates on the active worksheet.
The period at the beginning of .Value states it is to operate on the current With which is Range("A1") within Worksheets("Sheet1") . If I omit that period, Value operates on the active cell.
The use of With statements makes your code more compact and much clearer. However, you must include the periods. Consider:
If the active worksheet is "Sheet1", the output is:
The range addresses look the same but they apply to different worksheets.
You could replace a lot of your code with:
You can also write:
This VBA is a lot more advanced. You can copy values from a worksheet range to an array or from an array to a worksheet range. There are a number of questions with answers that explore this capability and I will not repeat those answers here. This is just a demonstration for you to explore later if you are interested.
I do not like what you are doing. In your first statement you delete some columns so column B becomes column A and column R becomes column I. You then change the column headings in their new positions. Since you are only moving the data, why do you need to change the column headings? More importantly, in nine months someone is going to add a column or rearrange existing columns. Your code will carry on regardless and your name will be mud.
I would prefer something like:
If your code requires a worksheet to be in a particular format and that format might change over time then check that format.
Когда возникает ошибка, отладчик выделяет слово «счетчик».
Заранее спасибо за помощь.
Я использую эту технику, когда не понимаю, какой тип объекта я создал, связав свойства.
В редакторе Visual Basic Excel создайте новый модуль, если у вас нет существующего модуля, который вы хотите использовать таким образом. Если вы выберете модуль в Project Explorer и нажмете F4, вы можете изменить имя модуля на «Эксперименты».
Введите или скопируйте:
Я всегда начинаю свои модули с Option Explicit . Посмотрите Option Explicit в справке VBA, и вы узнаете, почему это хорошая идея.
Я также создал пустую подпрограмму, в которую я введу несколько операторов.
Начните вводить новый оператор, чтобы у вас было:
Когда вы вводите точку в конце этой новой строки, всплывающее окно покажет вам доступные методы и свойства. Этот список, как и ожидалось, покажет все методы и свойства Range. Введите «Адрес» или выберите Адрес из списка, чтобы получить:
Нажмите F5, чтобы запустить этот макрос, и в окне немедленного выполнения появится следующее:
Это адрес всех строк в столбце B, чего и следовало ожидать.
Теперь добавьте в макрос еще два оператора:
Запустите этот макрос еще раз, и вы получите:
Опять же это то, что ожидалось.
Появившееся всплывающее окно не изменится, потому что Range.Rows это все еще диапазон.
Завершите оператор, добавив или выбрав «Адрес», и снова запустите макрос, чтобы получить:
Возможно, это не то, чего вы ожидали, но подумайте об этом. $B:$B,$F:$F - это все строки в столбцах B и F, поэтому добавление свойства Rows не меняет адрес.
Теперь добавьте в макрос следующие операторы:
Запустите макрос, и каждый из этих операторов выведет целое число. Я использую Excel 2003, поэтому получаю:
Если вы используете более позднюю версию Excel, вы получите более крупные целые числа. Второе целое число - это количество строк на листе для вашей версии Excel. Первое целое число - это количество ячеек в двух столбцах рабочего листа для вашей версии Excel.
Когда вы вводите последнюю точку, всплывающее окно не появляется, потому что целое число не имеет метода или свойства, которое вы можете выбрать таким образом. Метод .End(xlUp) работает по диапазону; это не свойство Count , поэтому вы получаете «Недействительный квалификатор».
При соединении свойств воедино очень легко запутаться. Лично я избегаю связывания свойств, потому что даже если он выполняется быстрее, мне требуется больше времени для понимания и отладки. Есть ситуации, в которых минимизация времени выполнения является главным приоритетом, но является ли это одним из таких случаев? Сколько часов вы потратили на такой подход?
Вам не нужно, RowMax но я включил его, чтобы вам было абсолютно ясно, что Rng3.Count возвращается. Я также прошел ОТТ с диапазонами. Я был бы счастлив напечатать: Set Rng3 = Union(Range("B:B"), Range("F:F")) потому что мне легко понять.
Метод .End(xlUp) действует на ячейку. MultiCellRange.End(xlUp).Row является допустимым синтаксисом, но я не могу заставить его возвращать полезную информацию. Если вы хотите использовать, .End(xlUp) подумайте:
New Member Join Date Jun 2008 Posts 5
VBA Invalid Qualifier on Strings
I'm working on a simple Macro for Excel, but I'm new to Visual Basic and I'm encountering some errors when working with strings. Here's an example of the error I keep running into:
Dim s1 As String
Dim s2 As String
s1 = "which case"
s2 = s1.ToUpper
When I try to run this, I keep getting the error "Invalid Qualifier" highlighting s1.ToUpper
Thanks for any help!
Former Admin/Moderator Join Date Sep 1999 Location San Jose, CA Posts 33,287
So are you doing this coding within Excel? If so let me know because you may have posted in the wrong forum.
New Member Join Date Jun 2008 Posts 5
I went in through Excel and am using the Visual Basic Editor under Macros. I then added a new module in the VB Editor which is where my code is located.
Former Admin/Moderator Join Date Sep 1999 Location San Jose, CA Posts 33,287
Head Hunted Join Date Aug 2007 Location Australia Posts 3,669
Originally Posted by amzngspiderman29
I'm working on a simple Macro for Excel, but I'm new to Visual Basic and I'm encountering some errors when working with strings. Here's an example of the error I keep running into:
When I try to run this, I keep getting the error "Invalid Qualifier" highlighting s1.ToUpper
Thanks for any help!
- Don't forget to use [CODE ] your code here [ /CODE] when posting code
- If your question was answered please use Thread Tools to mark your thread [RESOLVED]
- Don't forget to RATE helpful posts
Frenzied Member Join Date Feb 2003 Location Argentina Posts 1,950
However,if you write it in a formula in a cell, you would use Upper(A1), at least through XL 2K. And if you were writing VBA (macro) code, that is what you'd need.
Not sure if you're running XL as an application from another program.
No idea why the same versions of office would use UCase for some apps and Upper for others.
Head Hunted Join Date Aug 2007 Location Australia Posts 3,669
Originally Posted by salvelinus
However,if you write it in a formula in a cell, you would use Upper(A1), at least through XL 2K. And if you were writing VBA (macro) code, that is what you'd need.
Not sure if you're running XL as an application from another program.
No idea why the same versions of office would use UCase for some apps and Upper for others.
The Sub Test() code is VB6/VBA code.
UCase() and LCase() are 2 of basic functions in VB6/VBA, they works wherever these languages are used.
UPPER() and LOWER() are 2 WorksheetFunctions that can be used on worksheet within formulas only.
The reason why VB uses UCase() and LCase() but worksheetfunction uses UPPER() and LOWER() is at the creation of Excel, MS intended to make the functions compatible with Lotus 1-2-3 functions.
Читайте также: