VBA- sub or function not defined after re-importing a module

K

Kawule

New Member
Joined
Nov 21, 2016
Messages
23
I was testing something last week and I exported my coding module and then re-imported it later on.

Problem is when I re-imported the module itself Excel can no longer "see" the code that is stored in the module. After I double checked all my macro security settings I could see some of the macros when I hit alt+f8 some of them are still missing but right now I'm trying to troubleshoot a few of my Caller subs.

So for example I have this in my module
Code: 
Sub CreateMonthlyCharts_Caller()
Call CreateMonthlyCharts
End Sub

The names for both subs are correct but for some reason it just doesn't want to work. The Sub CreateMonthlyCharts is located on the actual worksheet itself and when I run that by itself it works with no issues.

I'm not 100% sure why all of the sudden Excel 2016 has issues when I exported my module.

I tried one more thing and that was to copy and paste the code in my imported module into a brand new one but this did not work.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Kawule said:
The Sub CreateMonthlyCharts is located on the actual worksheet itself
Click to expand...

Unless the caller is also located in the same worksheet code module, that is the problem. The CreateMonthlyCharts routine either needs to be in a normal module rather than an object module, or you need to include the code name of the worksheet when you call it:

Code: 
Call Sheet1.CreateMonthlyCharts

for example.
 
Upvote 0
hiker95 said:
Kawule,

Can we see all the macro code for CreateMonthlyCharts?
Click to expand...

As requested, as far as I know there should be no syntax errors, this code is still being worked on so there was a lot of copy & pasting from similar subs.
Code: 
Option Explicit
Sub CreateMonthlyCharts()


Dim rng As Range
Dim cvrRng As Range
Dim cht As ChartObject
Dim ws As Worksheet, ws2 As Worksheet, ws3 As Worksheet


Set ws = Worksheets("Field Report")
Set ws2 = Worksheets("Monthly Report")
Set ws3 = Worksheets("Monthly Data Transfer")


'Copies everything from the Field Report sheet to the Monthly report sheet
ws.Range("A:BO").Copy Destination:=ws2.Range("J:BX")
'this piece of code deletes the shape buttons copied from the field report sheet
' it will ignore the PE Logo on the sheet
Dim Shp As Shape


For Each Shp In ws.Shapes
    If Not (Shp.Type = msoPicture) Then Shp.Delete
Next Shp


'Start point for building up the charts
Set rng = ws3.Range("f1:f31,AU1:AU31") 'Selects Flow data range
Set cht = ws2.ChartObjects.Add(Left:=50, Width:=283.5, Top:=50, Height:=170) 'Create chart on Monthly Report
'Sets chart dimensions to specific range on chart
Set cvrRng = ws2.Range("A40:j60")
cht.Height = cvrRng.Height ' resize
cht.Width = cvrRng.Width   ' resize
cht.Top = cvrRng.Top       ' reposition
cht.Left = cvrRng.Left     ' reposition
'Building the chart for Flows
With cht.Chart
        .HasTitle = True
        .ChartTitle.Text = "Flows, m³"
        .ChartTitle.Font.Size = 10
        .SetSourceData Source:=rng
        .ChartType = xlLine
        '.SetElement (msoElementPrimaryValueAxisShow)
        '.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
        .SetElement (msoElementLegendTop)
        'legend
        .Legend.Font.Size = 8
        'y-axis name
        '.Axes(xlValue, xlPrimary).HasTitle = True
        '.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Volume m³"
        'Color the lines
        'note that series 1 = Raw Water, series 2 = treated water
        .SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(0, 112, 192) 'Blue
        .SeriesCollection(1).Format.Line.Weight = 1.5 'line width
        .SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(0, 176, 80) 'Green
        .SeriesCollection(2).Format.Line.Weight = 1.5
        'Select last point of data and add a marker+data label
        With .SeriesCollection(1).Points(30)
            .HasDataLabel = True
            .MarkerStyle = 2
            .Format.Fill.ForeColor.RGB = RGB(0, 112, 192) 'marker's fill color
            .DataLabel.Position = xlLabelPositionBelow
        End With
        With .SeriesCollection(2).Points(30)
            .HasDataLabel = True
            .MarkerStyle = 2
            .Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
            .DataLabel.Position = xlLabelPositionAbove
        End With
End With
'Start point for building up the charts
Set rng = ws3.Range("C1:D31,AR1:AR31,AT1:AT31") 'Selects Raw/Treated pH/Temp data range
Set cht = ws2.ChartObjects.Add(Left:=50, Width:=283.5, Top:=50, Height:=170)
'Sets chart dimensions to specific range on chart
Set cvrRng = ws2.Range("K40:T60")
cht.Height = cvrRng.Height ' resize
cht.Width = cvrRng.Width   ' resize
cht.Top = cvrRng.Top       ' reposition
cht.Left = cvrRng.Left     ' reposition
'Building the chart for Flows
With cht.Chart
        .HasTitle = True
        .ChartTitle.Text = "Raw/Treated pH/Temp°C"
        .ChartTitle.Font.Size = 10
        .SetSourceData Source:=rng
        .ChartType = xlLine
        '.SetElement (msoElementPrimaryValueAxisShow)
        '.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
        .SetElement (msoElementLegendTop)
        'legend
        .Legend.Font.Size = 8
        'y-axis name
        '.Axes(xlValue, xlPrimary).HasTitle = True
        '.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Volume m³"
        'Color the lines
        .SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(0, 112, 192) 'Blue
        .SeriesCollection(1).Format.Line.Weight = 1.5 'line width
        .SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(0, 176, 240) 'light blue
        .SeriesCollection(2).Format.Line.Weight = 1.5
        .SeriesCollection(3).Format.Line.ForeColor.RGB = RGB(0, 176, 80) 'Green
        .SeriesCollection(3).Format.Line.Weight = 1.5
        .SeriesCollection(4).Format.Line.ForeColor.RGB = RGB(146, 208, 80) 'light Green
        .SeriesCollection(4).Format.Line.Weight = 1.5
        'Sets ph/temp series into secondary axis
        .SeriesCollection(1).AxisGroup = 2
        .SeriesCollection(3).AxisGroup = 2
        'Select last point of data and add a marker+data label
        With .SeriesCollection(1).Points(30)
            .HasDataLabel = True
            .MarkerStyle = 2
            .Format.Fill.ForeColor.RGB = RGB(0, 112, 192) 'marker's fill color
            .DataLabel.Position = xlLabelPositionBelow
        End With
        With .SeriesCollection(2).Points(30)
            .HasDataLabel = True
            .MarkerStyle = 2
            .Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
            .DataLabel.Position = xlLabelPositionAbove
        End With
        With .SeriesCollection(3).Points(30)
            .HasDataLabel = True
            .MarkerStyle = 2
            .Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
            .DataLabel.Position = xlLabelPositionAbove
        End With
        With .SeriesCollection(4).Points(30)
            .HasDataLabel = True
            .MarkerStyle = 2
            .Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
            .DataLabel.Position = xlLabelPositionAbove
        End With
End With
'Start point for building up the charts
Set rng = ws3.Range("E1:E31,Q1:R31,BJ1:BL31") 'Selects all the turbidity data
Set cht = ws2.ChartObjects.Add(Left:=50, Width:=283.5, Top:=50, Height:=170)
'Sets chart dimensions to specific range on chart
Set cvrRng = ws2.Range("U40:AD60")
cht.Height = cvrRng.Height ' resize
cht.Width = cvrRng.Width   ' resize
cht.Top = cvrRng.Top       ' reposition
cht.Left = cvrRng.Left     ' reposition
'Building the chart for Flows
With cht.Chart
        .HasTitle = True
        .ChartTitle.Text = "Turbidity, nTU"
        .ChartTitle.Font.Size = 10
        .SetSourceData Source:=rng
        .ChartType = xlLine
        '.SetElement (msoElementPrimaryValueAxisShow)
        '.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
        .SetElement (msoElementLegendTop)
        'legend
        .Legend.Font.Size = 8
        'y-axis name
        '.Axes(xlValue, xlPrimary).HasTitle = True
        '.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Volume m³"
        'Color the lines
        .SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(0, 112, 192) 'Blue
        .SeriesCollection(1).Format.Line.Weight = 1.5 'line width
        .SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(255, 192, 80) 'Orange
        .SeriesCollection(2).Format.Line.Weight = 1.5
        .SeriesCollection(3).Format.Line.ForeColor.RGB = RGB(0, 176, 80) 'Green
        .SeriesCollection(3).Format.Line.Weight = 1.5
        .SeriesCollection(4).Format.Line.ForeColor.RGB = RGB(112, 48, 160) 'purple
        .SeriesCollection(4).Format.Line.Weight = 1.5
        'Select last point of data and add a marker+data label
        With .SeriesCollection(1).Points(30)
            .HasDataLabel = True
            .MarkerStyle = 2
            .Format.Fill.ForeColor.RGB = RGB(0, 112, 192) 'marker's fill color
            .DataLabel.Position = xlLabelPositionBelow
        End With
        With .SeriesCollection(2).Points(30)
            .HasDataLabel = True
            .MarkerStyle = 2
            .Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
            .DataLabel.Position = xlLabelPositionAbove
        End With
        With .SeriesCollection(3).Points(30)
            .HasDataLabel = True
            .MarkerStyle = 2
            .Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
            .DataLabel.Position = xlLabelPositionAbove
        End With
        With .SeriesCollection(4).Points(30)
            .HasDataLabel = True
            .MarkerStyle = 2
            .Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
            .DataLabel.Position = xlLabelPositionAbove
        End With
End With
'Start point for building up the charts
Set rng = ws3.Range("AJ1:AJ31,AM1:AN31,AQ1:AQ31,BJ1:BJ31") 'Selects chlorine data ranges
Set cht = ws2.ChartObjects.Add(Left:=50, Width:=283.5, Top:=50, Height:=170)
'Sets chart dimensions to specific range on chart
Set cvrRng = ws2.Range("AE40:AN60")
cht.Height = cvrRng.Height ' resize
cht.Width = cvrRng.Width   ' resize
cht.Top = cvrRng.Top       ' reposition
cht.Left = cvrRng.Left     ' reposition
'Building the chart for Flows
With cht.Chart
        .HasTitle = True
        .ChartTitle.Text = "Chlorine, mg/L"
        .ChartTitle.Font.Size = 10
        .SetSourceData Source:=rng
        .ChartType = xlLine
        '.SetElement (msoElementPrimaryValueAxisShow)
        '.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
        .SetElement (msoElementLegendTop)
        'legend
        .Legend.Font.Size = 8
        'y-axis name
        '.Axes(xlValue, xlPrimary).HasTitle = True
        '.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Volume m³"
        'Color the lines
        .SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(0, 112, 192) 'Blue
        .SeriesCollection(1).Format.Line.Weight = 1.5 'line width
        .SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(255, 0, 0) 'Red
        .SeriesCollection(2).Format.Line.Weight = 1.5
        .SeriesCollection(3).Format.Line.ForeColor.RGB = RGB(255, 192, 80) 'Orange
        .SeriesCollection(3).Format.Line.Weight = 1.5
        .SeriesCollection(4).Format.Line.ForeColor.RGB = RGB(255, 255, 0) 'Yellow
        .SeriesCollection(4).Format.Line.Weight = 1.5
        .SeriesCollection(5).Format.Line.ForeColor.RGB = RGB(112, 48, 160) 'Purple
        .SeriesCollection(5).Format.Line.Weight = 1.5
        'Select last point of data and add a marker+data label
        With .SeriesCollection(1).Points(30)
            .HasDataLabel = True
            .MarkerStyle = 2
            .Format.Fill.ForeColor.RGB = RGB(0, 112, 192) 'marker's fill color
            .DataLabel.Position = xlLabelPositionBelow
        End With
        With .SeriesCollection(2).Points(30)
            .HasDataLabel = True
            .MarkerStyle = 2
            .Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
            .DataLabel.Position = xlLabelPositionAbove
        End With
        With .SeriesCollection(3).Points(30)
            .HasDataLabel = True
            .MarkerStyle = 2
            .Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
            .DataLabel.Position = xlLabelPositionAbove
        End With
        With .SeriesCollection(4).Points(30)
            .HasDataLabel = True
            .MarkerStyle = 2
            .Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
            .DataLabel.Position = xlLabelPositionAbove
        End With
        With .SeriesCollection(5).Points(30)
            .HasDataLabel = True
            .MarkerStyle = 2
            .Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
            .DataLabel.Position = xlLabelPositionAbove
        End With
End With
'Start point for building up the charts
Set rng = ws3.Range("BF1:BG31") 'Selects CT Actual & Perf. Ratio data range
Set cht = ws2.ChartObjects.Add(Left:=50, Width:=283.5, Top:=50, Height:=170)
'Sets chart dimensions to specific range on chart
Set cvrRng = ws2.Range("AO40:AX60")
cht.Height = cvrRng.Height ' resize
cht.Width = cvrRng.Width   ' resize
cht.Top = cvrRng.Top       ' reposition
cht.Left = cvrRng.Left     ' reposition
'Building the chart for Flows
With cht.Chart
        .HasTitle = True
        .ChartTitle.Text = "CT Actual/CT Perf. Ratio"
        .ChartTitle.Font.Size = 10
        .SetSourceData Source:=rng
        .ChartType = xlLine
        '.SetElement (msoElementPrimaryValueAxisShow)
        '.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
        .SetElement (msoElementLegendTop)
        'legend
        .Legend.Font.Size = 8
        'y-axis name
        '.Axes(xlValue, xlPrimary).HasTitle = True
        '.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Volume m³"
        'Color the lines
        'note that series 1 = Raw Water, series 2 = treated water
        .SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(0, 112, 192) 'Blue
        .SeriesCollection(1).Format.Line.Weight = 1.5 'line width
        .SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(0, 176, 80) 'Green
        .SeriesCollection(2).Format.Line.Weight = 1.5
        'Select last point of data and add a marker+data label
        With .SeriesCollection(1).Points(30)
            .HasDataLabel = True
            .MarkerStyle = 2
            .Format.Fill.ForeColor.RGB = RGB(0, 112, 192) 'marker's fill color
            .DataLabel.Position = xlLabelPositionBelow
        End With
        With .SeriesCollection(2).Points(30)
            .HasDataLabel = True
            .MarkerStyle = 2
            .Format.Fill.ForeColor.RGB = RGB(0, 176, 80)
            .DataLabel.Position = xlLabelPositionAbove
        End With
End With


End Sub
 
Upvote 0
Hi RoryA,

I didn't think of that when I tried to run it guess I'm still trying to wrap my head around the different types in Excel. I'll give your suggestion a shot and yes the Caller subs are in the Module but the actual sub for the code is on the worksheet. Before I exported the module everything worked but after I did an export/import everything broke haha

EDIT: Yep referencing the sheet did the trick but I guess next time I'm not gonna export my module!
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Build in Cushion between Modules being Called
2
Replies
12
Views
377
reberryjr
R
K
  • Solved
VBA that runs Sub after Data Refresh All completes
2
Replies
11
Views
1K
John_w
J
R
  • Question
VB Mac/Windows advise
Replies
0
Views
87
Russk68
R
S
  • Solved
Programmatically adding a Private Sub to a worksheet
2
Replies
18
Views
393
sctlippert2
S
K0st4din
  • Solved
Macro help, after pasting with Call option...... stopped working and gives error
Replies
2
Views
230
K0st4din
K0st4din

Forum statistics

Threads
1,214,636
Messages
6,120,666
Members
448,977
Latest member
moonlight6

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top