Prevent Private sub from calling function - Excel VBA
up vote
0
down vote
favorite
I have now come to the understanding that my Private sub calls my UDF because my UDF has Application.Volatile = True
. I can therefore prevent it from happening, by setting it to Application.Volatile = False
instead.
Problem
Without having Volatile set to True
in my function, it won't update, which is key in my sheet. And as earlier mentioned, I would like my private sub to stop calling my function, since it pretty much stops my loop from happening.
Goal
I would therefore like to know, whether it is possible to prevent my Private sub from calling my function, since it is placed in very different areas.
My private sub inserts a new value in B19, whereas my function is placed in A2.
Thank you in advance
As requested here is the code:
Sub UpdateSheets()
Dim WS_count As Integer
Dim I As Integer
Dim sht As Worksheet
Today = Date
WS_count = ActiveWorkbook.Worksheets.Count
For I = 1 To WS_count
If I = 1 Then
Else
Set sht = Sheets(I)
LnLAddress = sht.Range("A:A").Find("Lease end lessee:", , LookIn:=xlValues).Address(False, False, xlA1)
LnLOff = sht.Range(LnLAddress).Offset(0, 1).Address(False, False, xlA1)
LnLVal = sht.Range(LnLOff).Value
NtceAddress = sht.Range("A:A").Find("Notice:", , LookIn:=xlValues).Address(False, False, xlA1)
NtceOff = sht.Range(NtceAddress).Offset(0, 1).Address(False, False, xlA1)
NtceVal = sht.Range(NtceOff).Value
On Error GoTo Ending:
NtceVal = Left(NtceVal, Application.WorksheetFunction.Find(" ", NtceVal) - 1)
LnLVal = DateSerial(Year(LnLVal), Month(LnLVal) - NtceVal, Day(LnLVal))
LnLYear = Year(LnLVal)
On Error GoTo 0
If LnLVal <= Today Then
AutoExtAddress = sht.Range("A:A").Find("Automatical extension of contract", , LookIn:=xlValues).Address(False, False, xlA1)
AutoExtOff = sht.Range(AutoExtAddress).Offset(0, 1).Address(False, False, xlA1)
AutoExtVal = sht.Range(AutoExtOff).Value
AutoExt = Left(AutoExtVal, Application.WorksheetFunction.Find(" ", AutoExtVal) - 1)
LnLNewVal = DateSerial(Year(LnLVal) + AutoExt, Month(LnLVal) + NtceVal, Day(LnLVal))
Application.Calculation = xlCalculationManual
sht.Range(LnLOff).Value = LnLNewVal
Application.Calculation = xlCalculationAutomatic 'loop through functions starts here...
End If
End If
Ending:
On Error GoTo 0
Next I
End Sub
And here the functions:
Function SHEETNAME(number As Long) As String
Application.Volatile True
SHEETNAME = Sheets(number).Name
End Function
Function NxtShtNm(number As Long) As String
Application.Volatile True
NxtShtNm = ActiveWorkbook.Sheets(ActiveSheet.Index + number - 1).Name
End Function
excel vba excel-vba loops volatile
|
show 11 more comments
up vote
0
down vote
favorite
I have now come to the understanding that my Private sub calls my UDF because my UDF has Application.Volatile = True
. I can therefore prevent it from happening, by setting it to Application.Volatile = False
instead.
Problem
Without having Volatile set to True
in my function, it won't update, which is key in my sheet. And as earlier mentioned, I would like my private sub to stop calling my function, since it pretty much stops my loop from happening.
Goal
I would therefore like to know, whether it is possible to prevent my Private sub from calling my function, since it is placed in very different areas.
My private sub inserts a new value in B19, whereas my function is placed in A2.
Thank you in advance
As requested here is the code:
Sub UpdateSheets()
Dim WS_count As Integer
Dim I As Integer
Dim sht As Worksheet
Today = Date
WS_count = ActiveWorkbook.Worksheets.Count
For I = 1 To WS_count
If I = 1 Then
Else
Set sht = Sheets(I)
LnLAddress = sht.Range("A:A").Find("Lease end lessee:", , LookIn:=xlValues).Address(False, False, xlA1)
LnLOff = sht.Range(LnLAddress).Offset(0, 1).Address(False, False, xlA1)
LnLVal = sht.Range(LnLOff).Value
NtceAddress = sht.Range("A:A").Find("Notice:", , LookIn:=xlValues).Address(False, False, xlA1)
NtceOff = sht.Range(NtceAddress).Offset(0, 1).Address(False, False, xlA1)
NtceVal = sht.Range(NtceOff).Value
On Error GoTo Ending:
NtceVal = Left(NtceVal, Application.WorksheetFunction.Find(" ", NtceVal) - 1)
LnLVal = DateSerial(Year(LnLVal), Month(LnLVal) - NtceVal, Day(LnLVal))
LnLYear = Year(LnLVal)
On Error GoTo 0
If LnLVal <= Today Then
AutoExtAddress = sht.Range("A:A").Find("Automatical extension of contract", , LookIn:=xlValues).Address(False, False, xlA1)
AutoExtOff = sht.Range(AutoExtAddress).Offset(0, 1).Address(False, False, xlA1)
AutoExtVal = sht.Range(AutoExtOff).Value
AutoExt = Left(AutoExtVal, Application.WorksheetFunction.Find(" ", AutoExtVal) - 1)
LnLNewVal = DateSerial(Year(LnLVal) + AutoExt, Month(LnLVal) + NtceVal, Day(LnLVal))
Application.Calculation = xlCalculationManual
sht.Range(LnLOff).Value = LnLNewVal
Application.Calculation = xlCalculationAutomatic 'loop through functions starts here...
End If
End If
Ending:
On Error GoTo 0
Next I
End Sub
And here the functions:
Function SHEETNAME(number As Long) As String
Application.Volatile True
SHEETNAME = Sheets(number).Name
End Function
Function NxtShtNm(number As Long) As String
Application.Volatile True
NxtShtNm = ActiveWorkbook.Sheets(ActiveSheet.Index + number - 1).Name
End Function
excel vba excel-vba loops volatile
Please edit your question and add the code or a Minimal, Complete, and Verifiable example. Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself.
– Pᴇʜ
Nov 12 at 8:24
Can you edit your question to include the code that you already have. Without that, it's difficult to answer your question, and will therefore likely be closed as being too broad.
– Ambie
Nov 12 at 8:25
I have now inserted the code and functions.
– Patrick S
Nov 12 at 8:27
3
...so all this code is to add hyperlinks dynamically? Are you aware of theHYPERLINK
worksheet function? In my experience when someone's had to useApplication.Volatile
to make a UDF "work properly", there's almost always a better/more organized/more efficient way to accomplish the same task.
– ashleedawg
Nov 12 at 8:36
1
This worksheet function returns the current worksheet name:=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
(based off of theCELL("filename")
function which returns filename + sheet name.)
– ashleedawg
Nov 12 at 8:42
|
show 11 more comments
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have now come to the understanding that my Private sub calls my UDF because my UDF has Application.Volatile = True
. I can therefore prevent it from happening, by setting it to Application.Volatile = False
instead.
Problem
Without having Volatile set to True
in my function, it won't update, which is key in my sheet. And as earlier mentioned, I would like my private sub to stop calling my function, since it pretty much stops my loop from happening.
Goal
I would therefore like to know, whether it is possible to prevent my Private sub from calling my function, since it is placed in very different areas.
My private sub inserts a new value in B19, whereas my function is placed in A2.
Thank you in advance
As requested here is the code:
Sub UpdateSheets()
Dim WS_count As Integer
Dim I As Integer
Dim sht As Worksheet
Today = Date
WS_count = ActiveWorkbook.Worksheets.Count
For I = 1 To WS_count
If I = 1 Then
Else
Set sht = Sheets(I)
LnLAddress = sht.Range("A:A").Find("Lease end lessee:", , LookIn:=xlValues).Address(False, False, xlA1)
LnLOff = sht.Range(LnLAddress).Offset(0, 1).Address(False, False, xlA1)
LnLVal = sht.Range(LnLOff).Value
NtceAddress = sht.Range("A:A").Find("Notice:", , LookIn:=xlValues).Address(False, False, xlA1)
NtceOff = sht.Range(NtceAddress).Offset(0, 1).Address(False, False, xlA1)
NtceVal = sht.Range(NtceOff).Value
On Error GoTo Ending:
NtceVal = Left(NtceVal, Application.WorksheetFunction.Find(" ", NtceVal) - 1)
LnLVal = DateSerial(Year(LnLVal), Month(LnLVal) - NtceVal, Day(LnLVal))
LnLYear = Year(LnLVal)
On Error GoTo 0
If LnLVal <= Today Then
AutoExtAddress = sht.Range("A:A").Find("Automatical extension of contract", , LookIn:=xlValues).Address(False, False, xlA1)
AutoExtOff = sht.Range(AutoExtAddress).Offset(0, 1).Address(False, False, xlA1)
AutoExtVal = sht.Range(AutoExtOff).Value
AutoExt = Left(AutoExtVal, Application.WorksheetFunction.Find(" ", AutoExtVal) - 1)
LnLNewVal = DateSerial(Year(LnLVal) + AutoExt, Month(LnLVal) + NtceVal, Day(LnLVal))
Application.Calculation = xlCalculationManual
sht.Range(LnLOff).Value = LnLNewVal
Application.Calculation = xlCalculationAutomatic 'loop through functions starts here...
End If
End If
Ending:
On Error GoTo 0
Next I
End Sub
And here the functions:
Function SHEETNAME(number As Long) As String
Application.Volatile True
SHEETNAME = Sheets(number).Name
End Function
Function NxtShtNm(number As Long) As String
Application.Volatile True
NxtShtNm = ActiveWorkbook.Sheets(ActiveSheet.Index + number - 1).Name
End Function
excel vba excel-vba loops volatile
I have now come to the understanding that my Private sub calls my UDF because my UDF has Application.Volatile = True
. I can therefore prevent it from happening, by setting it to Application.Volatile = False
instead.
Problem
Without having Volatile set to True
in my function, it won't update, which is key in my sheet. And as earlier mentioned, I would like my private sub to stop calling my function, since it pretty much stops my loop from happening.
Goal
I would therefore like to know, whether it is possible to prevent my Private sub from calling my function, since it is placed in very different areas.
My private sub inserts a new value in B19, whereas my function is placed in A2.
Thank you in advance
As requested here is the code:
Sub UpdateSheets()
Dim WS_count As Integer
Dim I As Integer
Dim sht As Worksheet
Today = Date
WS_count = ActiveWorkbook.Worksheets.Count
For I = 1 To WS_count
If I = 1 Then
Else
Set sht = Sheets(I)
LnLAddress = sht.Range("A:A").Find("Lease end lessee:", , LookIn:=xlValues).Address(False, False, xlA1)
LnLOff = sht.Range(LnLAddress).Offset(0, 1).Address(False, False, xlA1)
LnLVal = sht.Range(LnLOff).Value
NtceAddress = sht.Range("A:A").Find("Notice:", , LookIn:=xlValues).Address(False, False, xlA1)
NtceOff = sht.Range(NtceAddress).Offset(0, 1).Address(False, False, xlA1)
NtceVal = sht.Range(NtceOff).Value
On Error GoTo Ending:
NtceVal = Left(NtceVal, Application.WorksheetFunction.Find(" ", NtceVal) - 1)
LnLVal = DateSerial(Year(LnLVal), Month(LnLVal) - NtceVal, Day(LnLVal))
LnLYear = Year(LnLVal)
On Error GoTo 0
If LnLVal <= Today Then
AutoExtAddress = sht.Range("A:A").Find("Automatical extension of contract", , LookIn:=xlValues).Address(False, False, xlA1)
AutoExtOff = sht.Range(AutoExtAddress).Offset(0, 1).Address(False, False, xlA1)
AutoExtVal = sht.Range(AutoExtOff).Value
AutoExt = Left(AutoExtVal, Application.WorksheetFunction.Find(" ", AutoExtVal) - 1)
LnLNewVal = DateSerial(Year(LnLVal) + AutoExt, Month(LnLVal) + NtceVal, Day(LnLVal))
Application.Calculation = xlCalculationManual
sht.Range(LnLOff).Value = LnLNewVal
Application.Calculation = xlCalculationAutomatic 'loop through functions starts here...
End If
End If
Ending:
On Error GoTo 0
Next I
End Sub
And here the functions:
Function SHEETNAME(number As Long) As String
Application.Volatile True
SHEETNAME = Sheets(number).Name
End Function
Function NxtShtNm(number As Long) As String
Application.Volatile True
NxtShtNm = ActiveWorkbook.Sheets(ActiveSheet.Index + number - 1).Name
End Function
excel vba excel-vba loops volatile
excel vba excel-vba loops volatile
edited Nov 12 at 8:33
asked Nov 12 at 8:13
Patrick S
526
526
Please edit your question and add the code or a Minimal, Complete, and Verifiable example. Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself.
– Pᴇʜ
Nov 12 at 8:24
Can you edit your question to include the code that you already have. Without that, it's difficult to answer your question, and will therefore likely be closed as being too broad.
– Ambie
Nov 12 at 8:25
I have now inserted the code and functions.
– Patrick S
Nov 12 at 8:27
3
...so all this code is to add hyperlinks dynamically? Are you aware of theHYPERLINK
worksheet function? In my experience when someone's had to useApplication.Volatile
to make a UDF "work properly", there's almost always a better/more organized/more efficient way to accomplish the same task.
– ashleedawg
Nov 12 at 8:36
1
This worksheet function returns the current worksheet name:=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
(based off of theCELL("filename")
function which returns filename + sheet name.)
– ashleedawg
Nov 12 at 8:42
|
show 11 more comments
Please edit your question and add the code or a Minimal, Complete, and Verifiable example. Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself.
– Pᴇʜ
Nov 12 at 8:24
Can you edit your question to include the code that you already have. Without that, it's difficult to answer your question, and will therefore likely be closed as being too broad.
– Ambie
Nov 12 at 8:25
I have now inserted the code and functions.
– Patrick S
Nov 12 at 8:27
3
...so all this code is to add hyperlinks dynamically? Are you aware of theHYPERLINK
worksheet function? In my experience when someone's had to useApplication.Volatile
to make a UDF "work properly", there's almost always a better/more organized/more efficient way to accomplish the same task.
– ashleedawg
Nov 12 at 8:36
1
This worksheet function returns the current worksheet name:=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
(based off of theCELL("filename")
function which returns filename + sheet name.)
– ashleedawg
Nov 12 at 8:42
Please edit your question and add the code or a Minimal, Complete, and Verifiable example. Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself.
– Pᴇʜ
Nov 12 at 8:24
Please edit your question and add the code or a Minimal, Complete, and Verifiable example. Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself.
– Pᴇʜ
Nov 12 at 8:24
Can you edit your question to include the code that you already have. Without that, it's difficult to answer your question, and will therefore likely be closed as being too broad.
– Ambie
Nov 12 at 8:25
Can you edit your question to include the code that you already have. Without that, it's difficult to answer your question, and will therefore likely be closed as being too broad.
– Ambie
Nov 12 at 8:25
I have now inserted the code and functions.
– Patrick S
Nov 12 at 8:27
I have now inserted the code and functions.
– Patrick S
Nov 12 at 8:27
3
3
...so all this code is to add hyperlinks dynamically? Are you aware of the
HYPERLINK
worksheet function? In my experience when someone's had to use Application.Volatile
to make a UDF "work properly", there's almost always a better/more organized/more efficient way to accomplish the same task.– ashleedawg
Nov 12 at 8:36
...so all this code is to add hyperlinks dynamically? Are you aware of the
HYPERLINK
worksheet function? In my experience when someone's had to use Application.Volatile
to make a UDF "work properly", there's almost always a better/more organized/more efficient way to accomplish the same task.– ashleedawg
Nov 12 at 8:36
1
1
This worksheet function returns the current worksheet name:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
(based off of the CELL("filename")
function which returns filename + sheet name.)– ashleedawg
Nov 12 at 8:42
This worksheet function returns the current worksheet name:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
(based off of the CELL("filename")
function which returns filename + sheet name.)– ashleedawg
Nov 12 at 8:42
|
show 11 more comments
1 Answer
1
active
oldest
votes
up vote
0
down vote
later on came to know a better solution, without making UDF's...
I deleted my functions, since they only made problems within my sheet.
Then I made a named range, from the name manager, calling it "sheetlist"
After this I assigned this formula to what it refers to: =REPLACE(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")&T(NOW())
Making a formula in the cells, where I need to reference the workbooks: =IFERROR(HYPERLINK("#'" & INDEX(sheetlist;ROW()) & "'!A1";INDEX(sheetlist;ROW()));"")
Now it references the sheets in order, and assigning:Private Sub WorkSheet_Activate()
ActiveWorksheet.Calculate
End sub
to the overview sheet, has made the trick of updating itself :)
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53258104%2fprevent-private-sub-from-calling-function-excel-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
later on came to know a better solution, without making UDF's...
I deleted my functions, since they only made problems within my sheet.
Then I made a named range, from the name manager, calling it "sheetlist"
After this I assigned this formula to what it refers to: =REPLACE(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")&T(NOW())
Making a formula in the cells, where I need to reference the workbooks: =IFERROR(HYPERLINK("#'" & INDEX(sheetlist;ROW()) & "'!A1";INDEX(sheetlist;ROW()));"")
Now it references the sheets in order, and assigning:Private Sub WorkSheet_Activate()
ActiveWorksheet.Calculate
End sub
to the overview sheet, has made the trick of updating itself :)
add a comment |
up vote
0
down vote
later on came to know a better solution, without making UDF's...
I deleted my functions, since they only made problems within my sheet.
Then I made a named range, from the name manager, calling it "sheetlist"
After this I assigned this formula to what it refers to: =REPLACE(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")&T(NOW())
Making a formula in the cells, where I need to reference the workbooks: =IFERROR(HYPERLINK("#'" & INDEX(sheetlist;ROW()) & "'!A1";INDEX(sheetlist;ROW()));"")
Now it references the sheets in order, and assigning:Private Sub WorkSheet_Activate()
ActiveWorksheet.Calculate
End sub
to the overview sheet, has made the trick of updating itself :)
add a comment |
up vote
0
down vote
up vote
0
down vote
later on came to know a better solution, without making UDF's...
I deleted my functions, since they only made problems within my sheet.
Then I made a named range, from the name manager, calling it "sheetlist"
After this I assigned this formula to what it refers to: =REPLACE(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")&T(NOW())
Making a formula in the cells, where I need to reference the workbooks: =IFERROR(HYPERLINK("#'" & INDEX(sheetlist;ROW()) & "'!A1";INDEX(sheetlist;ROW()));"")
Now it references the sheets in order, and assigning:Private Sub WorkSheet_Activate()
ActiveWorksheet.Calculate
End sub
to the overview sheet, has made the trick of updating itself :)
later on came to know a better solution, without making UDF's...
I deleted my functions, since they only made problems within my sheet.
Then I made a named range, from the name manager, calling it "sheetlist"
After this I assigned this formula to what it refers to: =REPLACE(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")&T(NOW())
Making a formula in the cells, where I need to reference the workbooks: =IFERROR(HYPERLINK("#'" & INDEX(sheetlist;ROW()) & "'!A1";INDEX(sheetlist;ROW()));"")
Now it references the sheets in order, and assigning:Private Sub WorkSheet_Activate()
ActiveWorksheet.Calculate
End sub
to the overview sheet, has made the trick of updating itself :)
answered Nov 12 at 23:51
Patrick S
526
526
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53258104%2fprevent-private-sub-from-calling-function-excel-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Please edit your question and add the code or a Minimal, Complete, and Verifiable example. Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself.
– Pᴇʜ
Nov 12 at 8:24
Can you edit your question to include the code that you already have. Without that, it's difficult to answer your question, and will therefore likely be closed as being too broad.
– Ambie
Nov 12 at 8:25
I have now inserted the code and functions.
– Patrick S
Nov 12 at 8:27
3
...so all this code is to add hyperlinks dynamically? Are you aware of the
HYPERLINK
worksheet function? In my experience when someone's had to useApplication.Volatile
to make a UDF "work properly", there's almost always a better/more organized/more efficient way to accomplish the same task.– ashleedawg
Nov 12 at 8:36
1
This worksheet function returns the current worksheet name:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
(based off of theCELL("filename")
function which returns filename + sheet name.)– ashleedawg
Nov 12 at 8:42