Variable not storing value

[Gadgetgav]

Hi All,

I have a workbook that stores staff data and dates for training courses. I have multiple copies of this file for different departments. One of the admin team that uses these files is having problems running the macros to add/remove staff members.

No-one else is having these issues and I can't replicate them on my own system so it appears to be user/system specific.

The issue is that a variable I set using a couple of IF statements/loops seems to lose it's value as soon as it gets to the first line in the code that uses it.

I have attached an image of the code with the highlighted line from the debug message. As you can see from this image the variable "NewEmpRow" is set using the top part of the code (this checks to see where alphabetically the name will appear amongst the existing staff members). When the code gets to the highlighted line, where it should get the data from the text boxes on the userform, it now shows the variable as "empty" meaning it doesn't know which row to put this data on.

I assume that as this code works fine on everyone else's system and on all the other files (which are a direct copy of this file with different staff members) that this must be an issue with the settings on the specific user's system (they have the same issue when using 1 of the copied files too).

Does anyone know what setting I need to check as my searching hasn't found any useful results?

Thanks in advance,
Gav

 

[RoryA]

The only way I can see that happening with your code is if it goes into the Else part and lastRowSL is less than 4. In that situation, the variable will not be assigned a value.

 

[Gadgetgav]

The only way I can see that happening with your code is if it goes into the Else part and lastRowSL is less than 4. In that situation, the variable will not be assigned a value.

Sorry, I should've said that LastRowSL will never be less than 4 as that's where the header row is so the 4 is the 1st line of data. Also the data we have tested this with has all been between the 1st and last rows.

 

[RoryA]

Unless newEmpRow is a public variable, I cannot see any other way that it could be Empty. What debugging have you done? How is LastRowSL actually set?

 

[Gadgetgav]

Unless newEmpRow is a public variable, I cannot see any other way that it could be Empty. What debugging have you done? How is LastRowSL actually set?

NewEmpRow is just defined at the top of the module using:

Dim NewEmpRow as LongLong

It's not easy to do much debugging as the problem only exists on this one user's system and they are part time. The code works absolutely fine everywhere else and on the duplicate files so I don't really know where to start with the debugging. My next step is to step through line by line to see if the error is actually earlier in the code but, as I said it works fine with everyone else.

the LastRowSL variable is set using the below code:

LastRowSL = Application.ActiveWorkbook.Sheets("Staff List").Range("A100000").End(xlUp).Row

 

[RoryA]

Why have you declared it as LongLong? It's just a row number and should be Long. And is that declaration within the routine that has the problem, or outside all routines? I'd suggest you add some code to to default NewEmpRow to 4 (or wherever the minimum output row should be) before the If clause.

 

[Gadgetgav]

Why have you declared it as LongLong? It's just a row number and should be Long. And is that declaration within the routine that has the problem, or outside all routines? I'd suggest you add some code to to default NewEmpRow to 4 (or wherever the minimum output row should be) before the If clause.

I've declared it as LongLong due to some issues we've had with Long after some people have been upgraded to Office 365 but not everyone. This declaration is outside all routines.

I'm 99% certain that the problem isn't the code as it works 100% of the time on every other machine with every other user and on all the files for the different departments with exactly the same code.

My main thoughts about where the problem lies is that something on this user's system is blocking it but not blocking all macros. I need to know if there is an option that needs to be turned on/off to allow this through?

 

[RoryA]

It should not be LongLong. It should be Long. You only need LongLong for 64bit API calls that need to use very large numbers - usually for memory addresses.

There is no option to block certain bits of VBA code. I would be fairly certain the problem is with the code.

In your initial post you said the variable was "empty" - what did you mean by that exactly? Empty is a very specific thing in VBA and only a Variant variable can be Empty. Any numeric typed variable will always have a number - 0 by default.

 

[Gadgetgav]

It should not be LongLong. It should be Long. You only need LongLong for 64bit API calls that need to use very large numbers - usually for memory addresses.

There is no option to block certain bits of VBA code. I would be fairly certain the problem is with the code.

In your initial post you said the variable was "empty" - what did you mean by that exactly? Empty is a very specific thing in VBA and only a Variant variable can be Empty. Any numeric typed variable will always have a number - 0 by default.

I'm aware that Long should be used for this but due to something in the way my company upgraded their Office systems it often won't accept Long and will only accept LongLong for variables. All the files I had created before the update (using Long) stopped working and the only way we found to fix them was to change the variable type to LongLong

What I mean by "empty" is that when we debug the code and hover the mouse over the variable name to see it's current value the pop up says "empty"

If it is the code that is the problem how do you explain it working 100% of the time on everyone else's system?

 

[RoryA]

due to something in the way my company upgraded their Office systems it often won't accept Long and will only accept LongLong for variables

That is not possible. Long is a valid data type in all versions of Office.

As I said, a numeric variable cannot be empty. It will always have some number assigned to it. If your variable actually is Empty, then it would suggest you have another declaration of it somewhere that is either untyped or typed as Variant.