Sub or function not defined vba excel ошибка - Исправление ошибок и поиск оптимальных решений проблем

Return to VBA Code Examples

This article will explain the VBA sub or function not defined error.

When one has finished writing VBA code, it is a good idea to compile the code to check if there are any errors that may occur when running the code. If there are compile errors, a compile error warning will appear. One of these errors may be the Sub or Function not defined error. There can be a few reasons that this error occurs.

Misspelled Sub or Function

The most common reason for this error occurring is a spelling mistake!

Let us take the code below as an example:

Function GetTaxPercent(dblP as Double) As Double
  GetTaxPercent = dblP*0.15
End Function

Sub GetPrice()
  Dim dblPrice As Double
  Dim dblTax As Double
  dblPrice = Range("A1")
  dblTax = GetTaxPerc(dblPrice)
End Sub

In the above example, we have created a function to fetch the tax percentage value (15%).

In the second procedure, I am trying to call that function to get the tax on a certain value in range A1.

However, when I run the code, I get the compile error as I have spelt the function that I am calling incorrectly. This is an easily made mistake, especially in large VBA projects with lots of procedures and modules. The best way to prevent these errors at run time, is to compile the code before releasing it to your users.

In the Menu, click Debug > Compile VBAProject.

Any compile errors will then be highlighted in the code in order for you to fix them.

Missing Sub or Function

It may be that a sub or function just does not exist! Once again, if you have a large VBA project, it can be possible to delete a sub or function by mistake. If this is the case, you would unfortunately need to re-write the function. Always a good idea to have backups for this reason!

Incorrect Scope of Sub of Function

It may be the case that the sub or function does exist, and is spelt correctly, but the scope of the sub or function is incorrect. In the example below, the GetTaxPercent function is in a different module to the GetPrice sub that is calling it, and it has been marked Private. It therefore cannot be seen by the GetPrice sub procedure.

If we remove the word private in front of the Function, then the module will compile.

Источник

What’s worse than getting a runtime error in Excel VBA? A compile error. That’s because the actual error is not always highlighted, rather the opening Sub or Function statement. “Sub or Function not Defined” indicates a compile error. VBA displays this message when it cannot find what is referenced by name. This article gives several examples of this compile error and how to correct them.

VBA is compiled (translated) into machine language before it is executed. Compile errors halt the compile process before procedures are executed.

Best practice: Frequently check for compile errors from VB Editor. From the Debug menu, choose Compile VBAProject. No news is good news when nothing seems to happen.

Issue 1: Typos

Typos are the most common cause of “Sub or Function not Defined.” If Excel highlights (in yellow or gray) the keyword or procedure it can’t find, you have a great head start on your game of Hide and Seek.

Best practice: Follow Microsoft’s naming convention and always include at least one capital letter whenever you create a name (exception: counter variables like n). Always type the name in lower case. When you leave the statement, and the name stays in all lower case, you have found a typo.

Contrary to its message, “Sub or Function not Defined” is not limited to procedures. The statement below causes this message. Can you find the typo?

Worksheet("Summary").Select

Worksheets is the required keyword. The “Summary” worksheet object is a member of the Worksheets collection. The Worksheets collection contains all the worksheet objects of a workbook. Excel VBA has several collections.

Tip: All VBA collections end with “s”: Workbooks, Sheets, Cells, Charts, etc.

Issue 2: Worksheet Functions

VB Editor may be the backstage to the worksheets in front, but not all worksheet props have been brought backstage. These “props” are functions that don’t exist in VBA. Worksheet functions like CountA cause “Sub or Function not Defined”:

intCount = CountA("A:A")

The WorksheetFunction object is the “stage hand” that lets you call worksheet functions from VBA, like this:

intCount = WorksheetFunction.CountA("A:A")

Issue 3: Missing Procedure

Less frequently, the called procedure is truly missing. After you check for typos, and you’re sure you coded the called procedure, perhaps you are missing a library. Tools, References is the next place to look.

From VB Editor Tools menu, choose References. The References dialog box opens. If VBA has identified a missing library, the last library with a checkmark will start with MISSING, followed by its name. Most of the time, you can simply scroll down the alphabetical list of libraries and check the missing library, then choose OK.

Fortunately, a missing library happens infrequently, usually related to a recent change. Perhaps you upgraded to a newer version of Excel. You purchased a new computer. You received a workbook from someone with an older version of Excel. Or you created your first macro that calls Solver Add-In.

The Solver project is not added to VBA when you enable the Solver Add-In, as shown below. At Solver project is near the top of the list of references, so you don’t have to scroll down to find it.

Your own macro workbooks can behave like Solver. Every Excel workbook has a built-in VBAProject. See MyFunctions in the screenshot above? MyFunctions is simply VBAProject renamed in a macro workbook. The workbook is open, so the Subs in MyFunctions run from the Developer tab. Even so, “Sub or Function not Defined” occurs when MyFunctions is not checked, and a procedure is called from a different macro. Simply check its project as an available reference.

Best practice: Assign your VBA projects a meaningful name. From Project Explorer, right click the macro workbook. Choose VBAProperties, then type a Project Name with no spaces.

Issue 4: Unavailable Procedures

“Sub or Function not Defined” also occurs when the procedure is not available to the calling procedure in the same workbook. This error is related to Public vs. Private procedures. By default, Sub and Functions in standard modules of the Modules folder (seen in Project Explorer) are public. Standard procedures can be called by any procedure in the project. You can make a procedure private by adding the Private keyword, like this:

Private Sub Initialize()

Tip: All the macros shown in the Macros dialog box of the Developer tab are Public. The list excludes public functions.

Subs and Functions in worksheet modules are private. They can only be called from the worksheet (like clicking a button) or another procedure in that module. The same is true for user forms.

You can remedy “Sub or Function not Defined” by deleting the Private keyword from a procedure in a standard module. Sorry, you cannot remedy calling a procedure in a worksheet module from another module. Think of standard modules like public parks, and worksheet modules like private property. No trespassing allowed!

Issue 5: Declaring a Routine That Doesn’t Exist in the Specified DLL

The sub you’re trying to use could be a part of a DLL that needs to be referenced. So not declaring a DLL or declaring the wrong one will cause the compiler to not find the sub or function that you are trying to use.

A DLL is a dynamically linked library of a body of code that is compiled and is meant to provide some functionality or data to an executable application (like the VBA project we’re working with). A dynamic library is loaded by the VBA project when it needs it. DLLs are used in order to save developers time by utilizing built and tested bodies of code.

You will need to do some research to determine the library that your sub or function belongs to, then declare it in your code using the Declare keyword in its simplest form:

Declare Sub sub_name Lib “library_name”

To read more about accessing DLL functions and subs from VBA, check out: https://docs.microsoft.com/en-us/office/client-developer/excel/how-to-access-dlls-in-excel

Issue 6: Forgetting to Write It

Finally, it’s possible that it just hasn’t been written yet!

If you realize that the sub that has been highlighted for you by the VBA compiler doesn’t exist, then the solution is to create it. To know whether it exists or not, just search for it on the project level using the Find tool in the VBA IDE.

Selecting the ‘Current Project’ scope will allow you to search for the sub in the entire workbook. You can also do that for the other workbooks where the sub might reside.

Wrap Up

“Sub or Function not Defined” is a compile error that occurs when VBA cannot find a procedure or other reference by name. A typo is the most common cause of this message.

See also: Compile Error: Expected End of Statement and Compile Error: User-defined Type Not Defined

Additional assistance from Mahmoud Mostafa

Источник

Background — 2 excel files, file1 where the macro makes the changes, file2 (volume-log.txt) contains the data which goes into file1 plus the formatting changes. The macro is currently attached to personal.xlsb

Issue — When I run the macro the macro throws the following error «Compile Error Sub or Function not defined», Where else I need to define the sub VolumeInfo() ??

******************************************************

Sub VolumeInfo()
‘
‘
‘ Keyboard Shortcut: Ctrl+z
‘

    Windows(«volume-log.txt»).Activate
    Columns(«A:A»).EntireColumn.AutoFit
    Cells.Replace What:=» bytes free», Replacement:=»», LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range(«B2»).Select
    ActiveCell.FormulaR1C1 = _
        «=IF(ISERROR(FIND(«»Checking»»,R[-1]C[-1])),R[-1]C,RIGHT(R[-1]C[-1],LEN(R[-1]C[-1])-9))»
    Range(«C2»).Select
    ActiveCell.FormulaR1C1 = _
        «=IF(ISERROR(SEARCH(«»Dir(s)»»,RC[-2])),»»»»,IF(ISERROR(FIND(«»:»»,R[-1]C[-2])),»»»»,R[-1]C[-2]))»
    Range(«D2»).Select
    ActiveCell.FormulaR1C1 = _
            «=IF(RC[-1]=»»»»,»»»»,MID(RC[-3],SEARCH(«» «»,RC[-3],20),20)/1024/1024/1024)»
    Range(«E2»).Select
    ActiveCell.FormulaR1C1 = «=IF(RC[-1]<>»»»»,»»Keep»»,»»Delete»»)»
    Range(«B9000:E9000»).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.FillDown
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets(«volume-log»).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(«volume-log»).Sort.SortFields.Add Key _
        :=Range(«C2:C9000»), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(«volume-log»).Sort
        .SetRange Range(«B2:E9000»)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Selection.Copy
    Windows(«file1.xlsx»).Activate
    Sheets(«free space»).Select
    Range(«E2»).Select
    Windows(«volume-log.txt»).Activate

End Sub

************************************************************

Moved by

Friday, August 16, 2013 9:11 AM
•_Programming/Code related

Источник

Sub SM_Z()
‘
‘ Макрос записан 02.03.2005 (Efimov)
‘
FRM = «=MAX(‘[Список студентов.xls]Список групп’!R4C1:R13C1)»
Range(«B5»).Formula = FRM
m = 3 + Range(«B5»).Value
ActiveSheet.Shapes(«Group»).Select
With Selection
.ListFillRange = «‘[Список студентов.xls]Список групп’!$B$4:$B$» & m
.LinkedCell = «$I$3»
.DropDownLines = Range(«B5»).Value
.Display3DShading = True
End With

Range(«E2»).Select
ActiveCell.FormulaR1C1 = _
«=VLOOKUP(R3C9,'[Список студентов.xls]Список групп’!R4C1:R13C2,2)»
ActiveSheet.Shapes(«Bill»).Select
GR = Range(«E2»).Value
FRM = «=MAX(‘[Список студентов.xls]» & GR & «‘!R8C1:R38C1)»
Range(«A5»).Formula = FRM
m = 7 + Range(«A5»).Value
With Selection
.ListFillRange = «‘[Список студентов.xls]» & GR & «‘!$B$8:$B$» & m
.LinkedCell = «$A$4»
.MultiSelect = xlNone
.Display3DShading = True
End With
FRM = «=VLOOKUP(R4C1,'[Список студентов.xls]» & GR & «‘!R4C1:R» & m & «C4,2)»
Range(«B4»).Select
ActiveCell.FormulaR1C1 = FRM

FRM = «=VLOOKUP(R4C1,'[Список студентов.xls]» & GR & «‘!R4C1:R» & m & «C4,3)»
Range(«D4»).Select
ActiveCell.FormulaR1C1 = FRM

FRM = «=VLOOKUP(R4C1,'[Список студентов.xls]» & GR & «‘!R4C1:R» & m & «C4,4)»
Range(«F4»).Select
ActiveCell.FormulaR1C1 = FRM

If ActiveSheet.Name = «Задание» Then
Prep = «='[Список студентов.xls]» & GR & «‘!R5C4»
Range(«I23»).Formula = Prep
End If
End Sub

Sub Print_zsm()
‘
N = Range(«A5»)
For i = 1 To N
Range(«A4»).Value = i
Application.Run «‘Комплектование машин по объектам строительства__мг_mod.xls’!GSN»
pa = «A1:» & Cells(1, 1)
ActiveSheet.PageSetup.PrintArea = pa
ActiveSheet.PageSetup.PrintArea = pa
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.78740157480315)
.RightMargin = Application.InchesToPoints(0.78740157480315)
.TopMargin = Application.InchesToPoints(0.78740157480315)
.BottomMargin = Application.InchesToPoints(0.78740157480315)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next i
End Sub
Sub GSN()
‘
‘ Макрос1 Макрос
‘ Макрос записан 18.06.2009 (Владимир Ефимов)
‘

‘
Dim c(8) ‘ массив для значений ядра
Dim s(200) ‘ массив для случайных чисел
c(1) = 37584381
c(2) = 190999663
c(3) = 196446317
c(4) = 123567149
c(5) = 1480745561
c(6) = 442596621
c(7) = 340029183
c(8) = 203022663

Range(«F3»).Select
ActiveCell.FormulaR1C1 = _
«=RIGHT(RC[-2],LEN(RC[-2])-FIND(«»-«»,RC[-2],LEN(RC[-2])-3))»
nc = Cells(3, 6)
If nc > 8 Then nc = 8
mn = Len(Cells(4, 2)) & Len(Cells(4, 4)) & Len(Cells(4, 6)) + Cells(4, 1)
mnm = mn Mod 2
If mnm = 0 Then mn = mn + 1
bb = c(nc) * mn
mn = Right(bb, 4)
For i = 1 To 200
bb = c(nc) * mn
mn = Right(bb, 4)
s(i) = Right(bb, / 100000000
‘Cells(i + 5, 9) = s(i)
Next i
‘
‘——— конец генератора случайных чисел ———
‘
ni = 10
nj = 10
For i = 1 To ni
For j = 1 To nj
k = j + nj * (i — 1)
Cells(i + 9, j + 1) = Cells(10, 12) — Cells(10, 13) + 2 * Cells(10, 13) * s(k)
Next j
Next i
‘
‘ ——- заполнили матрицу затрат
‘
If ActiveSheet.Name = «Решение» Then
[B56:K65] = 0
SolverOk SetCell:=»$D$54″, MaxMinVal:=2, ValueOf:=»0″, ByChange:=»$B$56:$K$65″
SolverSolve UserFinish:=True
End If
End Sub

Sub Main_m()
Application.Run «‘Комплектование машин по объектам строительства__мг_mod.xls’!SM_Z»
Application.Run «‘Комплектование машин по объектам строительства__мг_mod.xls’!GSN»
End Sub

Первый раз имею дело с макросами.
Надеюсь то что надо скопировал

Источник

Sub DWP()
Dim vVar As String
Dim kvar As String
Dim changev As Range
Dim off As Integer
Dim con As Integer
Dim kchange As Double
Dim vchange As Double
Dim mini As Integer
Dim donum As Integer
Dim SelNum As Integer


'Application.ScreenUpdating = False
donum = -15
mini = 10
off = 57
con = 0
SelNum = -57
Sheets("Solver").Select
Range("B71").Select
Call ValSet
Call Opter
Range("B18").Select
Call valOf

Range("B71").Select
Call Opter



Range("B71").Select
con = 1
kchange = -0.5
vchange = 0.5
Call Opter
Range("B18").Select
'Call valOf
Range("B71").Select

kchange = 1
vchange = 1
Call Opter
Range("B18").Select
'Call valOf
Range("B71").Select


kchange = 0.5
vchange = -0.5
Call Opter
Range("B18").Select
'Call valOf
Range("B71").Select

kchange = -0.5
vchange = -0.5
Call Opter
Range("B18").Select
'Call valOf
Range("B71").Select

kchange = 0.5
vchange = 0.5
Call OpterUnconst
Range("B18").Select
'Call valOf
Range("B71").Select

kchange = -0.5
vchange = 0.5
Call OpterUnconst
Range("B18").Select
'Call valOf
Range("B71").Select

kchange = 0.5
vchange = -0.5
Call OpterUnconst
Range("B18").Select
'Call valOf
Range("B71").Select

kchange = 10
vchange = 1
Call OpterUnconst
Range("B18").Select
'Call valOf
Range("B71").Select

kchange = 5
vchange = 1
Call OpterUnconst
Range("B18").Select
'Call valOf
Range("B71").Select

kchange = 1
vchange = 5
Call OpterUnconst
Range("B18").Select
'Call valOf
Range("B71").Select

kchange = 2
vchange = 50
Call OpterUnconst
Range("B18").Select
'Call valOf
Range("B71").Select

kchange = -5
vchange = 0.5
Call OpterUnconst
Range("B18").Select
'Call valOf
Range("B71").Select

kchange = -10
vchange = 10
Call OpterUnconst
Range("B18").Select
'Call valOf
Range("B71").Select

kchange = -10
vchange = 1
Call OpterUnconst
Range("B18").Select
'Call valOf
Range("B71").Select

kchange = 1
vchange = 10
Call OpterUnconst
Range("B18").Select
'Call valOf
Range("B71").Select


kchange = -0.5
vchange = -0.5
Call OpterUnconst
Range("B18").Select
'Call valOf



con = 0
Range("N73").Select
Call Opter
Range("N73").Select

con = 1
kchange = 1
vchange = 1
Call Opter
Range("N73").Select

'con = 1
kchange = 0.123
vchange = 0.321
Call Opter
Range("N73").Select

kchange = -0.5
vchange = 0.5
Call Opter
Range("N73").Select

kchange = 0.5
vchange = -0.5
Call Opter
Range("N73").Select

kchange = -0.5
vchange = -0.5
Call Opter
Range("N73").Select

kchange = 0.5
vchange = 0.5
Call OpterUnconst
Range("N73").Select

kchange = -0.5
vchange = 0.5
Call OpterUnconst
Range("N73").Select

kchange = 0.5
vchange = -0.5
Call OpterUnconst
Range("N73").Select

kchange = 5
vchange = 1
Call OpterUnconst
Range("N73").Select

kchange = 1
vchange = 5
Call OpterUnconst
Range("N73").Select

kchange = -0.5
vchange = -0.5
Call OpterUnconst


Range("AB71").Select
con = 0
Call Opter3
Range("AB71").Select
con = 1
kchange = -3
Call Opter3


Range("AK71").Select
Call Opter4

con = 0
mini = 1
donum = 2
off = 3

Range("BB16").Select
Call valOf2

Range("BP16").Select
Call valOf2

Range("AV25").Select
Call OpterUnconstr

Range("AV12").Select
Call OpterUnconstr

Range("BJ25").Select
Call OpterUnconstr

Range("BJ12").Select
Call OpterUnconstr

con = 0
mini = 0
donum = -2
off = 49
SelNum = -49
Range("BP59").Select
Call OpterUnconstr

Range("BB59").Select
Call OpterUnconstr
SolverReset
End Sub
Sub ValSet()
'Application.ScreenUpdating = False
    Range("B9:J10, AB9:AH9").Value = 0.5
    Range("N9:O10, Q9:U10, W9:W10, X10").Value = -0.5
    Range("P9").Value = 2
    Range("P10").Value = -2.5
    Range("AK9:AP9").Value = 0.75
    Range("V9").Value = -0.005
    Range("v10").Value = 0.05
    Range("x9").Value = -0.05
End Sub
Sub Opter()
'Application.ScreenUpdating = False
'Call Module1.Iterations
Do While IsEmpty(ActiveCell.Offset(donum, 0)) = False
If mini = 1 Then
Call selecterMini
Else
Call selecter
End If
Set changev = Selection
ActiveCell.Offset(off, 0).Select


On Error GoTo erhand
If ActiveCell.Value > 950 Then
    If con = 1 Then
    Range(kvar).Value = kchange
    Range(vVar).Value = vchange
    Else
End If

    'Application.Run "SolverReset"
    'Application.Run "SolverOk", "ActiveCell.Address", 3, "0", "changev.Address"
    'Application.Run "SolverAdd", "kvar", 1, "Range(kvar).Offset(-1, 0).value"
    'Application.Run "SolverAdd", "kvar", 3, "Range(kvar).Offset(-2, 0).value"
    'Application.Run "SolverAdd", "vvar", 1, "Range(vVar).Offset(-4, 0).value"
    'Application.Run "SolverAdd", "vvar", 3, "Range(vVar).Offset(-5, 0).value"
    'Application.Run "SolverSolve", True
SolverReset
SolverOptions AssumeNonNeg:=False, Derivatives:=2
SolverOk SetCell:=ActiveCell.Address, _
                     MaxMinVal:=3, _
                     ValueOf:=0, _
                     ByChange:=changev.Address
                     SolverAdd CellRef:=kvar, Relation:=1, FormulaText:=Range(kvar).Offset(-1, 0).Value
                     SolverAdd CellRef:=kvar, Relation:=3, FormulaText:=Range(kvar).Offset(-2, 0).Value
                     SolverAdd CellRef:=vVar, Relation:=1, FormulaText:=Range(vVar).Offset(-4, 0).Value
                     SolverAdd CellRef:=vVar, Relation:=3, FormulaText:=Range(vVar).Offset(-5, 0).Value
            SolverSolve UserFinish:=True
ActiveCell.Offset(0, 1).Select
Else
erhand:
Resume Next
ActiveCell.Offset(0, 1).Select
End If
Loop
'Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Sub valOf()
'Application.ScreenUpdating = False
Do While IsEmpty(ActiveCell.Offset(-2, 0)) = False
If ActiveCell.Value = "Run Opt.1" Then
SolverReset
SolverOk SetCell:=ActiveCell.Offset(1, 0).Address, _
                     MaxMinVal:=3, _
                     ValueOf:=0, _
                     ByChange:=Selection.Offset(2, 0).Address
            SolverSolve UserFinish:=True
ActiveCell.Offset(0, 1).Select
Else
ActiveCell.Offset(0, 1).Select
End If
Loop
End Sub
Sub valOf2()
'Application.ScreenUpdating = False
Do While IsEmpty(ActiveCell.Offset(-3, 0)) = False
If ActiveCell.Value = "Run Opt.1" Then
SolverReset
SolverOk SetCell:=ActiveCell.Offset(1, 0).Address, _
                     MaxMinVal:=3, _
                     ValueOf:=0, _
                     ByChange:=Selection.Offset(2, 0).Address
            SolverSolve UserFinish:=True
ActiveCell.Offset(0, 1).Select
Else
ActiveCell.Offset(0, 1).Select
End If
Loop
End Sub
Sub OpterUnconst()
'Application.ScreenUpdating = False
'Call Module1.Iterations
Do While IsEmpty(ActiveCell.Offset(donum, 0)) = False
If mini = 1 Then
Call selecterMini
Else
Call selecter
End If
Set changev = Selection
ActiveCell.Offset(off, 0).Select
On Error GoTo erhand
If ActiveCell.Value > 950 Or ActiveCell < -500 Then

If con = 1 Then
Range(kvar).Value = kchange
Range(vVar).Value = vchange
Else
End If

SolverReset
SolverOptions AssumeNonNeg:=False, Derivatives:=2
SolverOk SetCell:=ActiveCell.Address, _
                     MaxMinVal:=3, _
                     ValueOf:=0, _
                     ByChange:=changev.Address
                     SolverAdd CellRef:=kvar, Relation:=1, FormulaText:=Range(kvar).Offset(-1, 0).Value + 2
                     SolverAdd CellRef:=kvar, Relation:=3, FormulaText:=Range(kvar).Offset(-2, 0).Value - 2
                     SolverAdd CellRef:=vVar, Relation:=1, FormulaText:=Range(vVar).Offset(-4, 0).Value + 15
                     SolverAdd CellRef:=vVar, Relation:=3, FormulaText:=Range(vVar).Offset(-5, 0).Value - 15
                     'SolverAdd CellRef:=kvar, Relation:=1, FormulaText:=Range(kvar).Offset(-1, 0).value
                    'SolverAdd CellRef:=kvar, Relation:=3, FormulaText:="0.0001"
                     'SolverAdd CellRef:=vVar, Relation:=1, FormulaText:=Range(vVar).Offset(-4, 0).value
                    'SolverAdd CellRef:=vVar, Relation:=3, FormulaText:="0.0001"
            SolverSolve UserFinish:=True
ActiveCell.Offset(0, 1).Select
Else
erhand:
Resume Next
ActiveCell.Offset(0, 1).Select
End If
Loop
'Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Sub OpterUnconstr()
'Application.ScreenUpdating = False
'Call Module1.Iterations
Do While IsEmpty(ActiveCell.Offset(donum, 0)) = False
If mini = 1 Then
Call selecterMini
Else
Call selecter
End If
Set changev = Selection
ActiveCell.Offset(off, 0).Select
On Error GoTo erhand
If ActiveCell.Value > 0.95 Or ActiveCell < -0.5 Then

If con = 1 Then
Range(kvar).Value = kchange
Range(vVar).Value = vchange
Else
End If

SolverReset
SolverOptions AssumeNonNeg:=False, Derivatives:=2
SolverOk SetCell:=ActiveCell.Address, _
                     MaxMinVal:=3, _
                     ValueOf:=0, _
                     ByChange:=changev.Address
                     'SolverAdd CellRef:=kvar, Relation:=1, FormulaText:=Range(kvar).Offset(-1, 0).value + 2
                     'SolverAdd CellRef:=kvar, Relation:=3, FormulaText:=Range(kvar).Offset(-2, 0).value - 2
                     'SolverAdd CellRef:=vVar, Relation:=1, FormulaText:=Range(vVar).Offset(-4, 0).value + 15
                     'SolverAdd CellRef:=vVar, Relation:=3, FormulaText:=Range(vVar).Offset(-5, 0).value - 15
                     'SolverAdd CellRef:=kvar, Relation:=1, FormulaText:=Range(kvar).Offset(-1, 0).value
                    'SolverAdd CellRef:=kvar, Relation:=3, FormulaText:="0.0001"
                     'SolverAdd CellRef:=vVar, Relation:=1, FormulaText:=Range(vVar).Offset(-4, 0).value
                    'SolverAdd CellRef:=vVar, Relation:=3, FormulaText:="0.0001"
            SolverSolve UserFinish:=True
ActiveCell.Offset(0, 1).Select
Else
erhand:
Resume Next
ActiveCell.Offset(0, 1).Select
End If
Loop
'Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Sub Opter3()
'Application.ScreenUpdating = False
'Call Module1.Iterations
Do While IsEmpty(ActiveCell.Offset(-10, 0)) = False
Call selecter3
Set changev = Selection
ActiveCell.Offset(57, 0).Select
On Error GoTo erhand
If ActiveCell.Value > 950 Or ActiveCell.Value < -0.1 Then
'MsgBox ActiveCell.value
If con = 1 Then
Range(kvar).Value = kchange
Else
End If

SolverReset
SolverOptions AssumeNonNeg:=True, Derivatives:=2
SolverOk SetCell:=ActiveCell.Address, _
                     MaxMinVal:=3, _
                     ValueOf:=0, _
                     ByChange:=kvar
                     SolverAdd CellRef:=kvar, Relation:=1, FormulaText:=Range(kvar).Offset(-1, 0).Value
                     SolverAdd CellRef:=kvar, Relation:=3, FormulaText:=Range(kvar).Offset(-2, 0).Value
            SolverSolve UserFinish:=True
ActiveCell.Offset(0, 1).Select
Else
erhand:
Resume Next
ActiveCell.Offset(0, 1).Select
End If
Loop
'Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Sub Opter4()
'Application.ScreenUpdating = False
'Call Module1.Iterations
Do While IsEmpty(ActiveCell.Offset(-13, 0)) = False
Call selecter3
Set changev = Selection
ActiveCell.Offset(57, 0).Select
SolverReset
SolverOptions AssumeNonNeg:=True, Derivatives:=2
SolverOk SetCell:=ActiveCell.Address, _
                     MaxMinVal:=2, _
                     ByChange:=kvar
                     SolverAdd CellRef:=kvar, Relation:=1, FormulaText:=Range(kvar).Offset(-1, 0).Value
                     SolverAdd CellRef:=kvar, Relation:=3, FormulaText:=Range(kvar).Offset(-2, 0).Value
            SolverSolve UserFinish:=True
ActiveCell.Offset(0, 1).Select
'erhand:
'Resume Next
'ActiveCell.Offset(0, 1).Select
Loop
'Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Sub selecter()
'Application.ScreenUpdating = False
ActiveCell.Offset(SelNum, 0).Select
kvar = ActiveCell.Address
ActiveCell.Offset(1, 0).Select
vVar = ActiveCell.Address
ActiveCell.Offset(-1, 0).Select
ActiveCell.Resize(2).Select
End Sub
Sub selecter3()
'Application.ScreenUpdating = False
ActiveCell.Offset(-57, 0).Select
kvar = ActiveCell.Address
End Sub
Sub selecterMini()
ActiveCell.Offset(-3, 0).Select
kvar = ActiveCell.Address
ActiveCell.Offset(1, 0).Select
vVar = ActiveCell.Address
ActiveCell.Offset(-1, 0).Select
ActiveCell.Resize(2).Select
End Sub

Sub it2()
'Range("B9:J10").value = 0.5
'MsgBox kvar & " " & vVar
'MsgBox Range(kvar).Offset(-2, 0).value

    Application.Run "SolverReset"
    Application.Run "SolverOk", "ActiveCell.Address", 3, "0", "changev.Address"
    Application.Run "SolverAdd", "kvar", 1, "Range(kvar).Offset(-1, 0).value"
    Application.Run "SolverAdd", "kvar", 3, "Range(kvar).Offset(-2, 0).value"
    Application.Run "SolverAdd", "vvar", 1, "Range(vVar).Offset(-4, 0).value"
    Application.Run "SolverAdd", "vvar", 3, "Range(vVar).Offset(-5, 0).value"
    Application.Run "SolverSolve", True
End Sub

Источник

sub or function not defined функция call

alex3111

Дата: Четверг, 08.05.2014, 00:09 |
Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 14

Репутация:
3
±

Замечаний:
0% ±

Excel 2007

уважаемые форумчане, прошу помощи.
Суть вопроса: В архиве (не смог приложить-весит 2Мб) таблица и заполняемый шаблон данными из таблицы. При запуске в отладчике процедур по отдельности — каждая из них выполняет свою задачу нормально, но при использовании функции Call (пробовал использовать и другие) появляется ошибка sub or function not defined на строке Call zapolnenie.
Искал на форуме http://www.excelworld.ru/search?q=sub+or+function+not+defined&x=8&y=9 , но ответа не увидел.

1
[vba]

Код

Sub CommandButton1_Click()
Call sozdpapki
End Sub

[/vba]

К сообщению приложен файл:

4400129.txt
(10.6 Kb)

Сообщение отредактировал alex3111 — Четверг, 08.05.2014, 00:13

Ответить

alex3111

Дата: Четверг, 08.05.2014, 00:10 |
Сообщение № 2

Группа: Пользователи

Ранг: Новичок

Сообщений: 14

Репутация:
3
±

Замечаний:
0% ±

Excel 2007

2
[vba]

Код

Sub sozdpapki()

HomeDir = ThisWorkbook.Path
Dim aaaa As Integer
aaaa = ActiveSheet.Range(«B4»)
sPath$ = HomeDir & «Готовозапрос_№_» & aaaa
     On Error Resume Next
     GetAttr (sPath)   ‘ если папка не существует, то будет ошибка
     If Err Then MkDir sPath ‘ если была ошибка, то создать папку
Call zapolnenie
End Sub

[/vba]

Ответить

alex3111

Дата: Четверг, 08.05.2014, 00:10 |
Сообщение № 3

Группа: Пользователи

Ранг: Новичок

Сообщений: 14

Репутация:
3
±

Замечаний:
0% ±

Excel 2007

3
[vba]

Код

Sub zapolnenie()

Dim sOM As String, sDocNum As String
Dim WordApp As Object

                      HomeDir = ThisWorkbook.Path
  Dim bbbb As String
  bbbb = ActiveSheet.Range(«B4»)
  a = HomeDir + «ШАБЛОНЫ6_АКТ_ПРОЦЕДУРЫ_ВСКРЫТИЯ_КОНВЕРТОВ» + «.doc»
  b = HomeDir + «Готово» + «запрос_№_» + bbbb + «6_АКТ_ПРОЦЕДУРЫ_ВСКРЫТИЯ_КОНВЕРТОВ» + «.doc»
  FileCopy Source:=a, Destination:=b
  sOM = b
   …
         WordApp.ActiveDocument.Save
         WordApp.ActiveDocument.Close
         WordApp.Quit

End Sub

[/vba]

Ответить

_Boroda_

Дата: Четверг, 08.05.2014, 00:19 |
Сообщение № 4

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16561

Репутация:
6446
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

А не пробовали просто
[vba]

Код

Sub CommandButton1_Click()
sozdpapki
‘Или Module555.sozdpapki, где Module555 — название модуля, в котором лежит макрос sozdpapki
End Sub

[/vba]
С zapolnenie аналогично

Скажи мне, кудесник, любимец ба’гов…
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

alex3111

Дата: Четверг, 08.05.2014, 00:28 |
Сообщение № 5

Группа: Пользователи

Ранг: Новичок

Сообщений: 14

Репутация:
3
±

Замечаний:
0% ±

Excel 2007

спасибо за мысль, ошибка была на Call zapolnenie, перенес Sub zapolnenie() из ЭтаКнига в модуль4, прописал Module4.zapolnenie и всё вроде заработало))))

пока я писал сообщение, Вы и про Sub zapolnenie() упомянули, еще раз спасибо.

Сообщение отредактировал alex3111 — Четверг, 08.05.2014, 00:31

Ответить

Источник