Works fine in 365, !VALUE Error in 2016

K

kaia2001

New Member
Joined
Sep 7, 2018
Messages
17
Office Version
  1. 2013
Platform
  1. Windows
Greetings,

I have a macro workbook that was created in Excel 2016, however VBA was added to it in Excel 365. It works fine when opened using MS 365 however the users that will need the from/wb have 2016. When opened in 2016 I am receiving a !VALUE error in the cells that should be calculating from the VBA. Would anyone know why this error persists in 2016?

This is the formula: =get_hours($B$16,B18,Database!$B$1,Database!$C$1)*VLOOKUP(B18,Lists!J:K,2,FALSE)/60
And the VBA references: Set pv = Sheet4.PivotTables("Activities by Team")

I don't see anything different or wrong here?
 

Attachments

  • !VALUE error.JPG
    !VALUE error.JPG
    88.3 KB · Views: 24

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
The screen shot is not helpful in diagnosing a VBA error. We need to see the entire VBA function get_hours. A #VALUE! error on a UDF indicates that the UDF raised an error, and it won't be possible to even start to diagnose this without seeing all the code.

It is possible, for example, that the VBA is calling a worksheet function that was added for 365 but did not exist in 2016. Just one possibility.
 
Upvote 0
This is the entire code referencing the cells/pivot:

VBA Code: 
Function get_count(date_val As Variant, criteria1 As String, date_header As Range, leader_header As Range)
    
    'Dim criteria1 As String
    'Dim date_val As Date
    'Dim task_duration_range As Range
    'Dim leader_header As Range
    'Dim date_header As Range
    
    
    'date_val = Sheet3.Range("B16")
    'criteria1 = "Walk-throughs"
    'Set task_duration_range = Sheet8.Range("J2")
    'Set date_header = Sheet2.Range("B1")
    'Set leader_header = Sheet2.Range("C1")
    

    'Set task_duration_range = task_duration_range.CurrentRegion
    
    'Debug.Print date_val
    

    Dim wr As Range
    Dim cell As Range
    Dim row_range As Range
    
    'Dim pv As PivotTable
    'Set pv = Sheet4.PivotTables("Activities by Team")
    'Set wr = pv.RowRange
    'Set wr = range_skip_down(wr)
    
    Set wr = expand_down(Sheet4.Range("D3"))
    Set wr = range_skip_down(wr)
    
    Dim sum_val As Double
    
    
    Dim person_coll As New Collection
    
    For Each person In wr
        
        person_coll.Add CStr(person), CStr(person)
    
    Next person
    
    Set wr = expand_down(date_header, True, True)
    
    
    For Each cell In wr
    
        If IsDate(cell.Value) = True Then
            
            If DateSerial(Year(cell.Value), Month(cell.Value), Day(cell.Value)) = DateSerial(Year(date_val), Month(date_val), Day(date_val)) Then
                
                ' check if the leader is the same
                If Coll_Exist(person_coll, cell.Offset(0, 1).Value) = True Then
                    
                    
                    
                    ' consider the row
                    Set row_range = expand_right(cell.Offset(0, 2), True, True)
                    
                    For Each i In row_range
                        
                        If LCase(CStr(i.Value)) = LCase(criteria1) Then
                            

                            sum_val = sum_val + 1

                        End If
                    
                    Next i
                    
                
                End If
                
            
            End If
        
        End If
        
    
    Next cell
    
    get_count = sum_val



End Function




Function get_hours(date_val As Variant, criteria1 As String, date_header As Range, leader_header As Range)
    
    'Dim criteria1 As String
    'Dim date_val As Date
    'Dim task_duration_range As Range
    'Dim leader_header As Range
    'Dim date_header As Range
    
    
    'date_val = Sheet3.Range("B16")
    'criteria1 = "Walk-throughs"
    'Set task_duration_range = Sheet8.Range("J2")
    'Set date_header = Sheet2.Range("B1")
    'Set leader_header = Sheet2.Range("C1")
    

    'Set task_duration_range = task_duration_range.CurrentRegion
    
    
    
    'Debug.Print date_val
    
    
    
    Dim wr As Range
    Dim cell As Range
    Dim row_range As Range
    
'    Dim pv As PivotTable
'    Set pv = Sheet4.PivotTables("Activities by Team")
'    Set wr = pv.RowRange
'    Set wr = range_skip_down(wr)
    
    Set wr = expand_down(Sheet4.Range("D3"))
    Set wr = range_skip_down(wr)
    
    Dim sum_val As Double
    
    
    Dim person_coll As New Collection
    
    For Each person In wr
        
        person_coll.Add CStr(person), CStr(person)
    
    Next person
    
    Set wr = expand_down(date_header, True, True)
    
    
    For Each cell In wr
    
        If IsDate(cell.Value) = True Then
            
            If DateSerial(Year(cell.Value), Month(cell.Value), Day(cell.Value)) = DateSerial(Year(date_val), Month(date_val), Day(date_val)) Then
                
                ' check if the leader is the same
                If Coll_Exist(person_coll, cell.Offset(0, 1).Value) = True Then
                    
                    
                    
                    ' consider the row
                    Set row_range = expand_right(cell.Offset(0, 2), True, True)
                    
                    For Each i In row_range
                        
                        If LCase(CStr(i.Value)) = LCase(criteria1) Then
                            
                            If IsNumeric(i.Offset(0, 1).Value) = True Then
                                
                                sum_val = sum_val + i.Offset(0, 1).Value
                            
                            End If
                            
                        
                        End If
                    
                    Next i
                    
                
                End If
                
            
            End If
        
        End If
        
    
    Next cell
    
    get_hours = sum_val



End Function



Sub test()

    Dim criteria1 As String
    Dim date_val As Date
    Dim task_duration_range As Range
    Dim leader_header As Range
    Dim date_header As Range
    
    
    date_val = Sheet3.Range("B16")
    criteria1 = "Walk-throughs"
    Set task_duration_range = Sheet8.Range("J2")
    Set date_header = Sheet2.Range("B1")
    Set leader_header = Sheet2.Range("C1")
    

    Set task_duration_range = task_duration_range.CurrentRegion
    Dim pv As PivotTable
    
    Set pv = Sheet4.PivotTables("Activities by Team")
    
    Dim wr As Range
    Dim cell As Range
    Dim row_range As Range
    
    Set wr = pv.RowRange
    Set wr = range_skip_down(wr)
    
    Dim sum_val As Double
    
    
    Dim person_coll As New Collection
    
    For Each person In wr
        
        person_coll.Add CStr(person), CStr(person)
    
    Next person
    
    Set wr = expand_down(date_header, True, True)
    
    
    For Each cell In wr
    
        If IsDate(cell.Value) = True Then
            
            If DateSerial(Year(cell.Value), Month(cell.Value), Day(cell.Value)) = DateSerial(Year(date_val), Month(date_val), Day(date_val)) Then
                
                ' check if the leader is the same
                If Coll_Exist(person_coll, cell.Offset(0, 1).Value) = True Then
                    
                    ' consider the row
                    Set row_range = expand_right(cell.Offset(0, 2), True, True)
                    
                    For Each i In row_range
                        
                        If i = criteria1 Then
                            
                            If IsNumeric(i.Offset(0, 1).Value) = True Then
                                
                                sum_val = sum_val + i.Offset(0, 1).Value
                            
                            End If
                            
                        
                        End If
                    
                    Next i
                    
                
                End If
                
            
            End If
        
        End If
        
    
    Next cell
    
    
End Sub
 
Upvote 0
Go into the VB Editor, and from the "Debug" menu, select "Compile VBAProject".
That could help identify the problem parts of your code.
Does it return any errors/highlight any code when you do that?
 
Upvote 0
There's no error handling in the functions so it could be caused by errors in the data. Was it only the code that was altered in 365, or were any formulas added?
 
Upvote 0
Joe4 said:
Go into the VB Editor, and from the "Debug" menu, select "Compile VBAProject".
That could help identify the problem parts of your code.
Does it return any errors/highlight any code when you do that?
Click to expand...
No errors come up when I Debug.
 
Upvote 0
RoryA said:
There's no error handling in the functions so it could be caused by errors in the data. Was it only the code that was altered in 365, or were any formulas added?
Click to expand...
The only formula is this one: =get_hours($B$16,B18,Database!$B$1,Database!$C$1)*VLOOKUP(B18,Lists!J:K,2,FALSE)/60

It
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Excel/365 VBA crash issues recently
Replies
0
Views
528
picklefactory
P
Mahantesh_Torgal
  • Question Question
Please help: VBA
Replies
2
Views
89
Mahantesh_Torgal
Mahantesh_Torgal
N
  • Question Question
Slow vba with opening 60 files
Replies
7
Views
287
NickvdB
N
J
  • Question Question
New Laptop - VBA Script will not run using VBS file manually or via Task Scheduler
Replies
0
Views
465
jaihawk8
J
R
  • Question Question
Compose Email VBA Help
Replies
1
Views
126
Logit
Logit

Forum statistics

Threads
1,224,735
Messages
6,180,636
Members
452,992
Latest member
TokugawaIesuma

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