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

 

[Gadgetgav]

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.

Long may be theoretically valid in all versions of Office but all I know is that the files didn't work after the update when the variables were Long but as soon as they were changed to LongLong they worked so that is the workaround we have been using.

The variable is not declared/used anywhere else other than this module so it is definitely a numeric variable and the prompt said "empty" I can't explain how but that's what the situation is. This is why I'm on here trying to find a solution.

 

[RoryA]

I suspect this will be next to impossible to resolve without all of the code and/or ideally the workbook (no data necessary)

 

[Gadgetgav]

I suspect this will be next to impossible to resolve without all of the code and/or ideally the workbook (no data necessary)

I suspect that's true also. I was hoping it would be a simple setting on the user's system.
I'm not sure I'd be able to send the file without data as some of the headers etc would class as sensitive data. I'll have a look and see if it's possible

 

[RoryA]

At this stage I don't think we need any data at all (including headers); I just want to see all the code and how it interacts with the structure of the workbook.

 

[Gadgetgav]

Just realised I'd not checked/responded to this in a while. The problem resolved itself in the end. Thanks for the suggestions everyone