Expected function or variable vba ошибка

I’m new and trying to learn VBA. When I’m typing in the code I get Compile Error Expected Function Or Variable.

Is something regarding the activecell, but can’t figure it out.

Sub Testare()
    Dim FilmName As String
    Dim FilmLenght As Integer
    Dim FilmDescription As String

    Range("b10").Select
    FilmName = ActiveCell.Value
    FilmLenght = ActiveCell.Offset(0, 2).Value

    If FilmLenght < 100 Then
       FilmDescription = "Interesant"
    Else
       FilmDescription = "Suficient"
    End If

    MsgBox FilmName & " is " & FilmDescription
End Sub

Josh Crozier

228k54 gold badges386 silver badges301 bronze badges

asked Apr 24, 2014 at 16:41

This error happens also when a Sub is called the same as variable (i.e. in one Sub you have for loop with iterator «a», whilst another Sub is called «a»).

This gives an error that fits to your description.

Regards

answered Mar 10, 2015 at 17:22

Artur RutkowskiArtur Rutkowski

4274 gold badges9 silver badges16 bronze badges

It is possible to make your code fail in two different ways:

Place a very large value in D10
Place a text value in D10

This will result in either an overflow error or type mismatch error.

answered Apr 24, 2014 at 17:20

Gary’s StudentGary’s Student

94.9k9 gold badges58 silver badges97 bronze badges

I know this was asked a while ago, but I ended up with the same error when I created a Sub within a worksheet with the Sub Name the same as the Worksheet name.

Unfortunately when this happens, no error is highlighted by a compile; the error only appears at run time. Also, the offending line is not highlighted, which makes it a bit difficult to find.

Changing the Sub name solves it though.

Nippey

4,64835 silver badges44 bronze badges

answered Oct 23, 2014 at 11:47

Here is what caused this error in my case:

Inside a new Public Function that I was starting to write, I began to type a statement, but needed to copy a long varname from another location, so I just inserted the letter a to prevent Excel from popping-up the uber-annoying Compile Error: Expected Expression dialog. So I had this:

myVarName = a

I then attempted to step-through my code to get to that point, and when Excel entered that function I received the message "Compile Error: Expected Function Or Variable". To resolve, I just changed the placeholder to a number:

myVarName = 1

And all continued just fine.

answered Aug 7, 2018 at 16:22

cssyphuscssyphus

36.6k18 gold badges93 silver badges108 bronze badges

Источник

Permalink

Cannot retrieve contributors at this time

title	keywords	f1_keywords	ms.prod	ms.assetid	ms.date	ms.localizationpriority
Expected Function or variable	vblr6.chm1040076	vblr6.chm1040076	office	c4f8e6fb-43b7-3dcd-c93a-7f9b2e542817	06/08/2017	medium

The syntax of your statement indicates a variable or function call. This error has the following cause and solution:

The name isn’t that of a known variable or Function procedure.

Check the spelling of the name. Make sure that any variable or function with that name is visible in the portion of the program from which you are referencing it. For example, if a function is defined as Private or a variable isn’t defined as Public, it’s only visible within its own module.
You are trying to inappropriately assign a value to a procedure name.

For example if MySub is a Sub procedure, the following code generates this error:
```
MySub = 237    ' Causes Expected Function or variable error
```
Although you can use assignment syntax with a Property Let procedure or with a Function that returns an object or a Variant containing an object, you can’t use assignment syntax with a Sub, Property Get, or Property Set procedure.

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

[!includeSupport and feedback]

Источник

bazboy

Пользователь

Сообщений: 8
Регистрация: 27.08.2018

27.08.2018 16:50:39

Добрый день, уважаемые форумчане!

Прошу подсказать — написал такой макрос и привязал его к кнопке. При написании с отладкой проблем не было, режим был доступен.
Сейчас при попытке сделать дебаг, возникает Compile Error: Expected Function or Variable — режим не запускается.

Просьба подсказать, в чем косяк.

Код

Sub DataTransfer()
'
' DataTransfer Макрос
'
    
    Dim strFileToOpen
    Dim wrkBook As Workbook
    Dim columnN As Integer, rowN As Integer
    Dim DateType As Variant, CodeType As Variant
    Dim i As Integer, Ind As Integer
    Dim s As String
    Dim lastRow As Integer
    
    'Адреса файлов  -  индекс файла должен четко соответствовать коду БЕ
    Dim Source(1 To 6) As String

        Source(1) = ThisWorkbook.Path & "Д.xlsx"
        Source(2) = ThisWorkbook.Path & "Р.xlsx"
        Source(3) = ThisWorkbook.Path & "Ф.xlsx"
        Source(4) = ThisWorkbook.Path & "Н.xlsx"
        Source(5) = ThisWorkbook.Path & "Ла.xlsx"
        Source(6) = ThisWorkbook.Path & "УК.xlsx"
    
    
    ' Поиск даты из ячейки А1 в строке 9 для копирования данных
    ' ВАЖНО!!! Предполагается, что столбцы в сводном файле и файле БЕ совпадают -
    ' т.е. столбцы с датами соответствуют друг другу
    Set DateType = ThisWorkbook.Worksheets(1).Rows(9). _
                        Find(ThisWorkbook.Worksheets(1).Cells(2, 9).Value, , xlValues, xlWhole)
    If Not DateType Is Nothing Then
        columnN = DateType.Column
    Else
    MsgBox "Дата не найдена"
    End If
    
    'запускаем цикл поэтапной обработки файла
    For Ind = 1 To UBound(Source)
    
    'назначаем файл в работу
    strFile = Source(Ind)
    Set wrkBook = Workbooks.Open(strFile)
    
    'снимаем защиту с листа
    wrkBook.Worksheets(1).Unprotect 
    wrkBook.Worksheets(1).Columns(columnN).Locked = False
    
    'Определяем последний элемент в столбце
    lastRow = ThisWorkbook.Worksheets(1).Cells(Rows.Count, 9).End(xlUp).Row
    
    'копируем данные
    For i = 9 To lastRow
        If Not ThisWorkbook.Worksheets(1).Cells(i, 1).Value = "" Then
            s = ThisWorkbook.Worksheets(1).Cells(i, 9).Value
            If Left(ThisWorkbook.Worksheets(1).Cells(i, 9).Value, 1) = Ind Then
            
            Set CodeType = wrkBook.Worksheets(1).Columns(9). _
                        Find(ThisWorkbook.Worksheets(1).Cells(i, 9).Value, , xlValues, xlWhole)
            If Not CodeType Is Nothing Then
                        rowN = CodeType.Row
            Else
                        MsgBox "Код " & ThisWorkbook.Worksheets(1).Cells(i, 9).Value & " не найден!"
            End If
            Set CodeType = Nothing
            wrkBook.Worksheets(1).Cells(rowN, columnN).Value = _
                             ThisWorkbook.Worksheets(1).Cells(i, columnN).Value
            End If
        End If
    Next i

    'устанавливаем параметр "защищаемая ячейка" для этих ячеек
    wrkBook.Worksheets(1).Columns(columnN).Locked = True
    wrkBook.Worksheets(1).Protect 
    
    wrkBook.Save
    wrkBook.Close
    Set wrkBook = Nothing
    Next Ind
    MsgBox "Готово!"
End Sub

Изменено: bazboy — 27.08.2018 20:53:39

Дмитрий(The_Prist) Щербаков

Пользователь

Сообщений: 13958
Регистрация: 15.09.2012

Профессиональная разработка приложений для MS Office

27.08.2018 16:57:14

Вы хоть напишите в какой строке возникает ошибка. И что значит:

Цитата
bazboy написал: при попытке сделать дебаг

как Вы его пытаетесь сделать?

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

bazboy

Пользователь

Сообщений: 8
Регистрация: 27.08.2018

27.08.2018 17:04:39

Цитата
Дмитрий(The_Prist) Щербаков написал: как Вы его пытаетесь сделать?

указатель в код и F8

	Допустим. А ошибка-то в какой строке? Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

bazboy Пользователь Сообщений: 8 Регистрация: 27.08.2018	в том-то и дело, что ничего не подсвечивает

ivanok_v2

Пользователь

Сообщений: 712
Регистрация: 19.08.2018

27.08.2018 17:37:29

Цитата
bazboy написал: в том-то и дело, что ничего не подсвечивает

Видиш суслика? Нет.
А он есть)))

Alemox

Пользователь

Сообщений: 2183
Регистрация: 25.02.2013

27.08.2018 18:50:53

Если у вас в модуле стоит
Option Explicit
то у вас не все переменные описаны, например
strFile
А тут что происходит

Код
If Not DateType Is Nothing Then columnN = DateType.Column Else MsgBox "Дата не найдена" End If

у вас переменная не найдена, а код дальше продолжает своё действие, и далее упирается опять в ошибку когда выполняет строку

Код
wrkBook.Worksheets(1).Columns(columnN).Locked = False

потому что ColumnN=0
Я думаю, что в Else выше недостаточно просто Msgbox.
Дальше смотреть не стал.

Мастерство программиста не в том, чтобы писать программы, работающие без ошибок.
А в том, чтобы писать программы, работающие при любом количестве ошибок.

Дмитрий(The_Prist) Щербаков

Пользователь

Сообщений: 13958
Регистрация: 15.09.2012

Профессиональная разработка приложений для MS Office

27.08.2018 20:40:05

Цитата
bazboy написал: При написании с отладкой проблем не было

если почитать, то раньше как-то иначе отладку проводили, не по F8? В какой момент перестало работать?
Проверьте, что открываемые файлы открываются НЕ в режиме защищенного просмотра. Иначе сразу после этой строки:

Код
Set wrkBook = Workbooks.Open(strFile)

переменная wrkBook будет Nothing и далее посыпят ошибки.

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

bazboy

Пользователь

Сообщений: 8
Регистрация: 27.08.2018

28.08.2018 16:05:40

Добрый день!

Цитата
ivanok_v2 написал: Видиш суслика? Нет.А он есть)))

Именно))

Цитата
Alemox написал: Если у вас в модуле стоитOption Explicitто у вас не все переменные описаны, напримерstrFile

Нет Option Explicit не стоял, спасибо — установил Require Variable Declaration в настройках.
strFile удалил, теперь сразу

Код
Set wrkBook = Workbooks.Open(Source(Ind))

Цитата
Alemox написал: Я думаю, что в Else выше недостаточно просто Msgbox.

Спасибо, поправил — теперь в случае невыполнения условия сразу к закрытию после сообщения.

Цитата
Дмитрий(The_Prist) Щербаков написал: если почитать, то раньше как-то иначе отладку проводили, не по F8? В какой момент перестало работать?

так и проводил, после выходных перестало
Поскольку в целом весь макрос отладил и с моими файлами все работало, повесил его на кнопку — думаю что, где-то в этот момент все и произошло.
Причем, получилось не с первого раза — первый раз вешал Лист1.DataTransfer — что-то не пошло.
Второй раз переназначал на кнопку — уже назывался DataTransfer. При этом возможно что-то предложило сделать, но сейчас не могу вспомнить.
Сразу подозрений не возникло — все отрабатывало.

Дмитрий, понимаю, что с этого походу надо было начинать, но только сегодня осознал :oops:

Изменено: bazboy — 28.08.2018 16:06:01

bazboy

Пользователь

Сообщений: 8
Регистрация: 27.08.2018

#10

28.08.2018 16:08:00

Цитата
Дмитрий(The_Prist) Щербаков написал: Проверьте, что открываемые файлы открываются НЕ в режиме защищенного просмотра.

Проверил, нормально открываются — думаю, изначально были бы проблемы. Через кнопку макрос отрабатывает.

Поместите код макроса из модуля листа в стандартный модуль. Тогда F8 будет работать.
Скорее всего у Вас кнопка сделана на основе ActiveX — лучше по возможности избегать этого и сделать кнопку из обычной автофигуры. Это надежнее.

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

bazboy Пользователь Сообщений: 8 Регистрация: 27.08.2018	#12 28.08.2018 17:27:47 Дмитрий(The_Prist) Щербаков, оно! Спасибо!

Источник

Remove From My Forums

Question
I’m getting this odd error in my code. its probably obvious but i’ve been staring at it for so long i’ve got code blindness ! i’ve highlighted the error line in red below.

error is : expected function or variable

the msdn definition is here :http://msdn.microsoft.com/library/default.asp?url=/library/en-us/msaa/msaaccrf_0bla.asp

Public Function WinEventFunc(ByVal HookHandle As Long, ByVal LEvent As Long, ByVal HWnd As Long, ByVal idObject As Long, ByVal idChild As Long, ByVal idEventThread As Long, ByVal dwmsEventTime As Long) As Long
Dim ObA As IAccessible
Dim LRet As Long
Dim V As Variant
Dim s As String, s1 As String, sName As String
    On Error Resume Next
Dim pxLeft As Long, pyTop As Long, pcxWidth As Long, pcyHeight As Long

  pxLeft = 0
pyTop = 0
pcxWidth = 0
pcyHeight = 0

  ‘Select Case LEvent
‘Case SYS_FOREGROUND, SYS_ALERT, OB_FOCUS, OB_SELECTION, OB_VALUECHANGE
      LRet = AccessibleObjectFromEvent(HWnd, idObject, idChild, ObA, V)
      If LRet = 0 Then
         With F1
            sName = ObA.accName(V)
            .Text1(0).Text = sName            .Text1(1).Text = ObA.accDescription(V)
            s1 = ObA.accValue(V)
            .Text1(2).Text = s1
            .Text1(3).Text = ObA.accRole(V)
            .Text1(4).Text = ObA.accState(V)



RECT = ObA.accLocation(pxleft, pytop, pcxwidth, pcyheight, V)
         End With

                  If (ObA.accState(V) And STATE_UNAVAILABLE) Then
            s = » disabled»
         End If

                  If LEvent = OB_VALUECHANGE Then
            s = s1 & s
         Else
            s = sName & s

          End If

               End If

          WinEventFunc = 0
End Function

Answers

You have this at the bottom:

End Function

End Sub

is that a typo?
If the problem is on the line you’ve highlighted, then i would make sure that the ObA object has a function called accLocation()

Also, where is RECT being defined? I don’t see it in this function.

RECT is also commonly used as a object name. You may want to consider calling it something else.

Источник

VBForums
Visual Basic
Visual Basic 6 and Earlier
[RESOLVED] Compile Error: Expected Function or Variable

Apr 18th, 2010, 01:07 AM

johnsonlim026 is offline

Thread Starter

New Member

[RESOLVED] Compile Error: Expected Function or Variable

Hi, i am new to vb6 and this is my first time deal with vb6
I have created from A and form B. In form A i call function X in form B.
And it is workable.

However, this is i have a project that is written by other programmer , i face this error: Compile Error: Expected Function or Variable . when a function call a public function in other form.
I not yet even step into the coding and this error has appear.

Here is the coding in the calling form

Code:

Dim lofrmImport As New frmImport
If lofrmImport.cmdAddWEB(vbTab) Then
                        
End If

Here is the coding for the function to be called.

Code:

Public Sub cmdAddWEB(Optional psDelimiter As String = vbTab)

Dim lbLoaded As Boolean
Dim lbVerify As Boolean ' Verify status
Dim llCurrent As Long   ' Pointer to file position
Dim lnCount As Integer
Dim loListImport As MSComctlLib.ListItem
Dim loListStatus As MSComctlLib.ListItem
Dim llFNum As Long
Dim lnTag As Integer    ' Tag pointer
Dim llLine As Long          ' Line number
Dim lsLine As String * MAX_LINE_LENGTH
Dim lsFileArray() As String
Dim lsFileName As String
Dim nFiles As Integer
Dim lsFields() As String
    
    'terri If automateOn Then
    If gbAutomateOn Then
        lbLoaded = False
        On Error GoTo 0
        'goSlrError.Trace "AddLog recorded for " & gsFileName
        WriteLog 1, "AddLog recorded for " & gsFileName   'Open Logfile, for cmdAdd recording
        GoTo Automate
    End If

    dlgCommon.CancelError = True
    dlgCommon.DialogTitle = "Select import file"
    dlgCommon.Filter = "All Files (*.*)|*.*|Text Files (*.txt)|*.txt"
    dlgCommon.Flags = cdlOFNExplorer + cdlOFNFileMustExist + cdlOFNReadOnly + cdlOFNAllowMultiselect
    dlgCommon.FilterIndex = 2
    dlgCommon.CancelError = False
    dlgCommon.ShowOpen

    On Error GoTo ErrorHandler

Automate:
    'terri If automateOn Then
    If gbAutomateOn Then
        ReDim lsFileArray(1)
        lsFileArray(1) = gsFileName     'Assign commandline arguement
    Else
        lsFileArray = GetAllFileNames(dlgCommon.FileName)
    End If

    On Error GoTo 0
    For nFiles = 1 To UBound(lsFileArray)
        If UBound(lsFileArray) = 1 Then
            lsFileName = lsFileArray(nFiles)
        Else
            ' Array 1 contains the directory of the files.
            If nFiles > 1 Then
                lsFileName = lsFileArray(1) & "" & lsFileArray(nFiles)
            Else
                lsFileName = ""
            End If
        End If
        ' Check whether entry is already in
        lbLoaded = False
        For lnCount = 1 To lvwImport.ListItems.count
            If lvwImport.ListItems(lnCount).Text = lsFileName Then
                lbLoaded = True
            End If
        Next

        ' Display each file in List View
        If Not lbLoaded Then
            ' Process each file, verify for correctness
            lbVerify = True
            If Trim$(lsFileName) <> "" Then
                Set loListImport = lvwImport.ListItems.Add(, , lsFileName)
                StatusBar "Verifying file " & lsFileName
                llFNum = FreeFile
                llLine = 0
                Open lsFileName For Binary Access Read Shared As #llFNum
                barProgress.Max = LOF(llFNum)
                barProgress.Value = 0
                Do While Not EOF(llFNum)
                    barProgress.Value = IIf(Loc(llFNum) >= LOF(llFNum), LOF(llFNum), Loc(llFNum))
                    DoEvents
                    ' Increment line number
                    llLine = llLine + 1

                    ' Read the PO Input File line by line, including CR & LF
                    Line Input #llFNum, lsLine
                    'Trim out vbTab at the end of the line
                    If Right$(Trim$(lsLine), 1) = psDelimiter Then
                        lsLine = Left$(Trim$(lsLine), Len(Trim$(lsLine)) - 1)
                    End If
                    lsFields() = Split(Trim$(lsLine), psDelimiter)   'Field delimiter is Tab

                    ' Hardcode tag to MSGLEVEL2
                    lnTag = 1

                    ' Check for valid line, ie. total fields is 54 for PO Input File
                    If UBound(lsFields) + 1 <> mnSize(lnTag) And Trim$(lsLine) <> "" Then
                        lbVerify = False
                        Set loListStatus = lvwStatus.ListItems.Add(, , lsFileName)
                        'Flag error
                        loListStatus.SubItems(1) = CStr(llLine)     ' Line No.
                        loListStatus.SubItems(2) = IIf(UBound(lsFields) > 0, Trim$(lsFields(0)), " ")
                        loListStatus.SubItems(3) = "Invalid line"   ' Status
                        ' Do not proceed further
                        Exit Do
                    End If
                    'terri If automateOn Then
                    If gbAutomateOn Then
                       ' goSlrError.Trace "Line : " & CStr(llLine) & " - " & Trim$(lsFields(0))
                       WriteLog 2, "Line : " & CStr(llLine) & " - " & Trim$(lsFields(0))
                    End If
                    
                    If Loc(llFNum) >= LOF(llFNum) Then
                        ' Exit if read past file size
                        Exit Do
                    End If
                Loop
                barProgress.Value = barProgress.Max
                Close #llFNum
                
                If lbVerify Then
                    loListImport.SubItems(1) = "Passed"
                    'terri If automateOn Then
                    If gbAutomateOn Then
                        'goSlrError.Trace "Passed: File process completed. "
                        'goSlrError.Trace "-------------- End of Add Report -------------"
                        WriteLog 2, "Passed: File process completed. "
                        WriteLog 0, "-------------- End of Add Report -------------"
                    End If
                Else
                    loListImport.SubItems(1) = "Failed"
                    'terri If automateOn Then
                    If gbAutomateOn Then
                        'goSlrError.ErrorTrap "frmImport.cmdAddWEB", "Failed: SlrEdi Import cmdAdd Failed!!"
                        'goSlrError.Trace "------------ End of Add Report ------------"
                        WriteLog 2, "Failed: SlrEdi Import cmdAdd Failed!!"
                        WriteLog 0, "------------ End of Add Report ------------"
                    End If
                    AutoProcessFail "IMPORT FAIL: SlrEdi Import cmdAdd Failed."
                End If
                StatusBar ""
            End If
        End If
    Next
    On Error GoTo 0
    Exit Sub

ErrorHandler:
    'terri If automateOn Then
    If gbAutomateOn Then
        AutoProcessFail "IMPORT FAIL: SlrEdi Import cmdImport Failed."
        'goSlrError.ErrorTrap "frmImport.cmdAddWEB", "IMPORT FAIL - SlrEdi Import cmdAdd Failed!!"
        'goSlrError.Trace "------------ End of Add Report ------------"
        WriteLog 2, "Error: IMPORT FAIL - SlrEdi Import cmdAdd Failed!!"
        WriteLog 0, "------------ End of Add Report ------------"
    End If
    
    Dim MyError As Error
    For Each MyError In DBEngine.Errors
    With MyError
        If gbAutomateOn Then
            WriteLog 2, "Error: frmImport.cmdAddWEB - Error Number: " & .Number & ", Description : " & .Description
            'goSlrError.ErrorTrap "frmImport.cmdAddWEB", "Error Number: " & .Number & ", Description : " & .Description
        Else
            MsgBox .Number & " " & .Description
        End If
    End With
    Next MyError
    Screen.MousePointer = vbDefault
    On Error GoTo 0
    Exit Sub
 
End Sub

Does anyone has idea on this? Help pls!!

Apr 18th, 2010, 01:59 AM

#2
Re: Compile Error: Expected Function or Variable
Welcome to the forums…

The second code that you have provided is not a function. It’s a subprocedure. It will not return anything.
But using this code:

Code:

Dim lofrmImport As New frmImport If lofrmImport.cmdAddWEB(vbTab) Then End If

VB is expecting for a return value. But since it’s a sub, it won’t return a value.
So, if you want to simply invoke the sub, you could use this line instead:

Code:

Dim lofrmImport As New frmImport lofrmImport.cmdAddWEB vbTab End If

Good luck…
If my post was helpful to you, then express your gratitude using Rate this Post.
And if your problem is SOLVED, then please Mark the Thread as RESOLVED (see it in action — video)
My system: AMD FX 6100, Gigabyte Motherboard, 8 GB Crossair Vengance, Cooler Master 450W Thunder PSU, 1.4 TB HDD, 18.5″ TFT(Wide), Antec V1 Cabinet
Social Group: VBForums — Developers from India

Skills: PHP, MySQL, jQuery, VB.Net, Photoshop, CodeIgniter, Bootstrap,…
Apr 18th, 2010, 09:07 PM

#3

Thread Starter

New Member

Re: Compile Error: Expected Function or Variable

Yo, got it !! Thaks akhileshbc !!
Apr 19th, 2010, 12:03 AM

#4

Re: [RESOLVED] Compile Error: Expected Function or Variable

You’re welcome…

If my post was helpful to you, then express your gratitude using Rate this Post.
And if your problem is SOLVED, then please Mark the Thread as RESOLVED (see it in action — video)
My system: AMD FX 6100, Gigabyte Motherboard, 8 GB Crossair Vengance, Cooler Master 450W Thunder PSU, 1.4 TB HDD, 18.5″ TFT(Wide), Antec V1 Cabinet
Social Group: VBForums — Developers from India

Skills: PHP, MySQL, jQuery, VB.Net, Photoshop, CodeIgniter, Bootstrap,…

VBForums
Visual Basic
Visual Basic 6 and Earlier
[RESOLVED] Compile Error: Expected Function or Variable

Posting Permissions

You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
[VIDEO] code is On
HTML code is Off

Forum Rules

Click Here to Expand Forum to Full Width

Источник

07-11-2012, 07:31 AM

#1

Solved: VBA Compile error: Expected function or variable & SQL code help needed

Hi,
Im new to this forum and VBA but have experience in many other languages.

I want the SQL code to get the last Reading_Number of a certain Disc_Size when the Disc_Size field is changed.

eg The last Reading_Number of a Disc_Size 2 is 33
The Disc_Size field is changed from 1.6 to 2 so now the value 34 will be automatically entered into the Reading_Number field.

I’ve think I’ve attached a picture of the form and below is the code I’m trying to use.

I’m getting the following error at Private Sub Disc_Size_Change() :

«Compile error: Expected function or variable»
[VBA]Private Sub Disc_Size_Change()
Dim vDisc As Variant
vDisc = Me![Disc_Size]
‘Get last reading number of disc size and put it (+ 1) in current record
Me![Reading_Number] = DoCmd.RunSQL(«SELECT MAX([Reading_Number]) FROM Grinding_Control WHERE [Disc_Size]=vDisc+1;»)

End Sub[/VBA]

Thanks, I hope I was clear? I have other VBA code that runs under certain conditions which I can post if it might be involved.
07-11-2012, 04:14 PM

#2

RunSQL is intended for use with action queries (INSERT, UPDATE, DELETE, SELECT INTO, and DML statements such as CREATE TABLE) according the to Access help topic. It looks like you want to retrieve a maximum value from your table.

Consider the DMax() function with a Criteria parameter (see the help topic) which mimics the WHERE clause you used in your query.

[VBA]Private Sub Disc_Size_Change()
Dim strCriteria As String
strCriteria = «[Disc_Size]=» & Me![Disc_Size] + 1
Debug.Print strCriteria
Me![Reading_Number] = DMax(«Reading_Number», «Grinding_Control», strCriteria)
End Sub[/VBA]

Make adjustments to accommodate Null values if you will be dealing with any, but that code outline should get you started.
07-12-2012, 12:03 AM

#3

That is perfect.
Thanks very much.
07-12-2012, 03:40 AM

#4

I now have a slightly more complicated issue.

I want it to find the Max value where Week equals the current week (the ISOWEEK gets this) and Day = todays day (Because I don’t want it grabbing reading numbers from previous days)

Here is the code:
[VBA]Private Sub Disc_Size_Change()
Dim strCriteria As String
Dim vCurrent_Day As String

vCurrent_Day = WeekdayName(Weekday(Date), False, 1)
strCriteria = «[Disc_Size]=» & Me![Disc_Size] & » AND » & «[Week]=» & ISOWEEK(Now(), 1) & » AND » & «[Day]=» & vCurrent_Day

Me![Reading_Number] = Nz(DMax(«Reading_Number», «Grinding_Control», strCriteria), 0) + 1[/VBA]

I’m getting the following error:
Run-time error ‘2471’:
The expression you entered as a query parameter produced this error: ‘Thursday’

At this line:
[VBA]Me![Reading_Number] = Nz(DMax(«Reading_Number», «Grinding_Control», strCriteria), 0) + 1[/VBA]

Last edited by davetherave; 07-12-2012 at 04:04 AM.
07-12-2012, 05:56 AM

#5

Solved it.

I left out the quote marks around the vCurrent_Day string.

Источник

MrExcell created this code for me and I’ve been running it fine until all of a sudden I started getting the VBA Compile Error: Expected Function or Variable. I don’t know what caused it or how to fix it. The «NewColl =» is highlighted blue when I try to debug it. Please help. Thank you!

Code:

Sub AddCollection()
Sheets("Add NewA").Select
  OldColl = Range("c8").Value
  RightChar = Right(OldColl, 1)
  RightCode = Asc(RightChar)
  Select Case RightCode
    Case 48 To 57
     'last character is a digit, add "A"
     NewColl = OldColl & "a"
     Case 65 To 89
     'Capital A through Y.Replace with next letter
     NewColl = Left(OldColl, Len(OldColl) - 1) & Chr(RightCode + 1)
     Case 90
     'Capital Z, Add Capital A to end
     NewColl = OldColl & "A"
     Case 97 To 121
     'lower case a through y.Replace with next letter
     NewColl = Left(OldColl, Len(OldColl) - 1) & Chr(RightCode + 1)
     Case 122
     'Capital Z.Add Capital A to end
     NewColl = OldColl & "a"
     Case Else
     'Unexpected! Add a to end
     NewColl = OldColl & "a"
     End Select
     Range("c7").Value = NewColl
     Range("a1").Select

Last edited by a moderator: Feb 18, 2014

Lock one reference in a formula

Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

The code looks OK to me ….

Have you by any chance created another VBA Sub called «NewColl» ?

Thanks for the response Stephen…No, I basically created about 10 identical worksheets within one workbook and use the same code to create a new sub record. What I started doing was adding the activesheet.unprotect and activesheet.protect code to help me unlock and locking it.

MrExcell created this code for me and I’ve been running it fine until all of a sudden I started getting the VBA Compile Error: Expected Function or Variable.

I basically created about 10 identical worksheets within one workbook and use the same code to create a new sub record. What I started doing was adding the activesheet.unprotect and activesheet.protect code to help me unlock and locking it.

Your first post implied that your original code had been working fine, but suddenly stopped working. That code works with one specific worksheet. Based on the code posted, and the error message you were getting, it sounded as if the problem was a name conflict with NewColl.

You second post implies that you have made changes to and/or replicated the original code, e.g. to accommodate 10 different sheets. I am also not clear what you mean by «use the same code to create a new sub record«

So …

— Is the code that you have posted the code that is causing the error?
— Have you shown all the code for Sub AddCollection()
— Is there other code in your workbook, and can you post that too?

Thanks

RoryA

MrExcel MVP, Moderator

Also, have you named a module NewColl?

Источник

05-21-2014, 12:55 PM

#1

Registered User

Compile Error Expected Function or Variable

Hi,

I’m fairly new to VBA so please bare with me. I’ve basically created this macro which print screens and saves the file into pdf looking up which sheet and range in a tab that i created. I’m however getting this compile error with the «Sub test()» turning yellow:

Any ideas?

Last edited by shiser; 05-22-2014 at 09:19 AM.
05-21-2014, 12:56 PM

#2

Forum Contributor

Re: Compile Error Expected Function or Variable

Sheet35 is a variable you did not declare what is in it.

Do you means Sheets(«Sheet35») or Sheets(35)?
05-21-2014, 12:59 PM

#3

Re: Compile Error Expected Function or Variable

Used in that fashion, Sheet35 must be the CodeName of a sheet, which is what appears to the left of the sheet name in the Project Explorer window. In a new workbook with two sheets, you would see

Sheet1 (Sheet1)
Sheet2 (Sheet2)

Change the name of Sheet1 to Bob, and you’d see

Sheet1 (Bob)
Sheet2 (Sheet2)

After doing that, you could refer to the first sheet as either Sheet1, or Worksheets(1), or Worksheets(«Bob»)

Last edited by shg; 05-21-2014 at 01:02 PM.

Entia non sunt multiplicanda sine necessitate
05-21-2014, 01:11 PM

#4

Registered User

Re: Compile Error Expected Function or Variable

I’ve called the worksheet «test» and I’m getting the same error. I’ve adjusted it as:

I’ve also tried:

which I believe is the same thing. I still get the same error annoyingly
05-21-2014, 01:33 PM

#5

Re: Compile Error Expected Function or Variable

Originally Posted by shiser

I’ve called the worksheet «test» and I’m getting the same error. I’ve adjusted it as:

I’ve also tried:

which I believe is the same thing. I still get the same error annoyingly

The first line should never generate a compile error; it would generate a runtime (subscript) error if there is no worksheet named test in the active workbook.

The second line is missing a closing quote after test.

Last edited by shg; 05-22-2014 at 04:17 PM.
05-22-2014, 04:19 AM

#6

Registered User

Re: Compile Error Expected Function or Variable

I’m confused then because I’m getting a compile error.

The second code was written in heist on the forum but I’ve had the correct formula in the macro.

Wouldn’t the worksheet part of the code turn yellow if that was the issue? Just to clarify, the «Sub test()» is getting the error and turning yellow.
05-22-2014, 06:50 AM

#7

Registered User

Re: Compile Error Expected Function or Variable

I think I know what the problem is! Its this code:

Basically, what I want it to do is look up the sheet dependent on what I have in my tab.:

and the lookup cell contains for example:

Which is located in cell C4. With my Macro stating i = 4 and sTab location being (i, 3), it should be picking up the correct cell I think.
05-22-2014, 07:10 AM

#8

Re: Compile Error Expected Function or Variable

Remember what the dormouse said
Feed your head
05-22-2014, 08:00 AM

#9

Registered User

Re: Compile Error Expected Function or Variable

«subscript out of range»

So close to figuring this out!
05-22-2014, 08:14 AM

#10

Re: Compile Error Expected Function or Variable

That means sTab does not contain the tab name of one of your worksheets.
05-22-2014, 09:18 AM

#11

Registered User

Re: Compile Error Expected Function or Variable

Originally Posted by romperstomper

This worked. I mistakenly had quotations in this line. Thanks!