How to pass a row to an Excel (Visual Basic for Applications) custom function?
I need to create a custom function for someone else to use in Excel. I can read VB script just fine but I have not done any programming in Excel. To start, I'm hoping to get a shell going. How would I create a function that could be called in a cell as =@MyFunction(ThisRow) (or something like that to hide complexity) to do the following:
Function MyFunction(Row)
firstName = Row.FIRST_NAME // FIRST_NAME is the column label
lastName = Row.LAST_NAME
name = firstName + " " + lastName // just an example of an operation
MyFunction = name // return value
End Function
Search online produced some examples where a range is passed into the function. Since this function would be used by someone else, I'd prefer to limit the argument to the current row. Again, above is only a shell to get the function going. Once I have that working, I can proceed with complex operations within the function.
Example:
A1 | B1 | C1
Mel | Gibson | @MyFunction(thisrow)
C1 would produce 'Mel Gibson'
excel vba excel-vba user-defined-functions
edited Nov 24 '18 at 4:49
K.Dᴀᴠɪs
7,229112439
asked Nov 24 '18 at 3:10
jaceknjacekn
51931338
add a comment |
I need to create a custom function for someone else to use in Excel. I can read VB script just fine but I have not done any programming in Excel. To start, I'm hoping to get a shell going. How would I create a function that could be called in a cell as =@MyFunction(ThisRow) (or something like that to hide complexity) to do the following:
Function MyFunction(Row)
firstName = Row.FIRST_NAME // FIRST_NAME is the column label
lastName = Row.LAST_NAME
name = firstName + " " + lastName // just an example of an operation
MyFunction = name // return value
End Function
Search online produced some examples where a range is passed into the function. Since this function would be used by someone else, I'd prefer to limit the argument to the current row. Again, above is only a shell to get the function going. Once I have that working, I can proceed with complex operations within the function.
Example:
A1 | B1 | C1
Mel | Gibson | @MyFunction(thisrow)
C1 would produce 'Mel Gibson'
excel vba excel-vba user-defined-functions
edited Nov 24 '18 at 4:49
K.Dᴀᴠɪs
7,229112439
asked Nov 24 '18 at 3:10
jaceknjacekn
51931338
MyFunction(ByVal argRow as Range)
– Harun24HR
Nov 24 '18 at 3:13
@Harun24HR This is what my searches produced. Are you saying that Range is the Row? I understand a Range as a rectangle, not a line.
– jacekn
Nov 24 '18 at 3:14
What do you mean byRow? Row means row number or reference cell?
– Harun24HR
Nov 24 '18 at 3:17
Can you put a screenshot of your sample data?
– Harun24HR
Nov 24 '18 at 3:17
Either would work, as long as I could read row cells in the function. Assuming A1 shows 'Harun' and B1 shows '24HR', I'd like to put =@MyFunction(ThisRow) in C1 to produce 'Harun24HR' in C1 where function is called. Again. this is just a shell. Actual logic will be very complex, so I need a custom function.
– jacekn
Nov 24 '18 at 3:20
add a comment |
I need to create a custom function for someone else to use in Excel. I can read VB script just fine but I have not done any programming in Excel. To start, I'm hoping to get a shell going. How would I create a function that could be called in a cell as =@MyFunction(ThisRow) (or something like that to hide complexity) to do the following:
Function MyFunction(Row)
firstName = Row.FIRST_NAME // FIRST_NAME is the column label
lastName = Row.LAST_NAME
name = firstName + " " + lastName // just an example of an operation
MyFunction = name // return value
End Function
Search online produced some examples where a range is passed into the function. Since this function would be used by someone else, I'd prefer to limit the argument to the current row. Again, above is only a shell to get the function going. Once I have that working, I can proceed with complex operations within the function.
Example:
A1 | B1 | C1
Mel | Gibson | @MyFunction(thisrow)
C1 would produce 'Mel Gibson'
excel vba excel-vba user-defined-functions
edited Nov 24 '18 at 4:49
K.Dᴀᴠɪs
7,229112439
asked Nov 24 '18 at 3:10
jaceknjacekn
51931338
I need to create a custom function for someone else to use in Excel. I can read VB script just fine but I have not done any programming in Excel. To start, I'm hoping to get a shell going. How would I create a function that could be called in a cell as =@MyFunction(ThisRow) (or something like that to hide complexity) to do the following:
Function MyFunction(Row)
firstName = Row.FIRST_NAME // FIRST_NAME is the column label
lastName = Row.LAST_NAME
name = firstName + " " + lastName // just an example of an operation
MyFunction = name // return value
End Function
Search online produced some examples where a range is passed into the function. Since this function would be used by someone else, I'd prefer to limit the argument to the current row. Again, above is only a shell to get the function going. Once I have that working, I can proceed with complex operations within the function.
Example:
A1 | B1 | C1
Mel | Gibson | @MyFunction(thisrow)
C1 would produce 'Mel Gibson'
excel vba excel-vba user-defined-functions
excel vba excel-vba user-defined-functions
edited Nov 24 '18 at 4:49
K.Dᴀᴠɪs
7,229112439
asked Nov 24 '18 at 3:10
jaceknjacekn
51931338
edited Nov 24 '18 at 4:49
K.Dᴀᴠɪs
7,229112439
asked Nov 24 '18 at 3:10
jaceknjacekn
51931338
edited Nov 24 '18 at 4:49
K.Dᴀᴠɪs
7,229112439
edited Nov 24 '18 at 4:49
K.Dᴀᴠɪs
7,229112439
edited Nov 24 '18 at 4:49
K.Dᴀᴠɪs
7,229112439
7,229112439
asked Nov 24 '18 at 3:10
jaceknjacekn
51931338
asked Nov 24 '18 at 3:10
jaceknjacekn
51931338
asked Nov 24 '18 at 3:10
jaceknjacekn
51931338
51931338
MyFunction(ByVal argRow as Range)
– Harun24HR
Nov 24 '18 at 3:13
@Harun24HR This is what my searches produced. Are you saying that Range is the Row? I understand a Range as a rectangle, not a line.
– jacekn
Nov 24 '18 at 3:14
What do you mean byRow? Row means row number or reference cell?
– Harun24HR
Nov 24 '18 at 3:17
Can you put a screenshot of your sample data?
– Harun24HR
Nov 24 '18 at 3:17
Either would work, as long as I could read row cells in the function. Assuming A1 shows 'Harun' and B1 shows '24HR', I'd like to put =@MyFunction(ThisRow) in C1 to produce 'Harun24HR' in C1 where function is called. Again. this is just a shell. Actual logic will be very complex, so I need a custom function.
– jacekn
Nov 24 '18 at 3:20
add a comment |
MyFunction(ByVal argRow as Range)
– Harun24HR
Nov 24 '18 at 3:13
@Harun24HR This is what my searches produced. Are you saying that Range is the Row? I understand a Range as a rectangle, not a line.
– jacekn
Nov 24 '18 at 3:14
What do you mean byRow? Row means row number or reference cell?
– Harun24HR
Nov 24 '18 at 3:17
Can you put a screenshot of your sample data?
– Harun24HR
Nov 24 '18 at 3:17
Either would work, as long as I could read row cells in the function. Assuming A1 shows 'Harun' and B1 shows '24HR', I'd like to put =@MyFunction(ThisRow) in C1 to produce 'Harun24HR' in C1 where function is called. Again. this is just a shell. Actual logic will be very complex, so I need a custom function.
– jacekn
Nov 24 '18 at 3:20
MyFunction(ByVal argRow as Range)– Harun24HR
Nov 24 '18 at 3:13
MyFunction(ByVal argRow as Range)– Harun24HR
Nov 24 '18 at 3:13
@Harun24HR This is what my searches produced. Are you saying that Range is the Row? I understand a Range as a rectangle, not a line.
– jacekn
Nov 24 '18 at 3:14
@Harun24HR This is what my searches produced. Are you saying that Range is the Row? I understand a Range as a rectangle, not a line.
– jacekn
Nov 24 '18 at 3:14
What do you mean by
Row? Row means row number or reference cell?– Harun24HR
Nov 24 '18 at 3:17
What do you mean by
Row? Row means row number or reference cell?– Harun24HR
Nov 24 '18 at 3:17
Can you put a screenshot of your sample data?
– Harun24HR
Nov 24 '18 at 3:17
Can you put a screenshot of your sample data?
– Harun24HR
Nov 24 '18 at 3:17
Either would work, as long as I could read row cells in the function. Assuming A1 shows 'Harun' and B1 shows '24HR', I'd like to put =@MyFunction(ThisRow) in C1 to produce 'Harun24HR' in C1 where function is called. Again. this is just a shell. Actual logic will be very complex, so I need a custom function.
– jacekn
Nov 24 '18 at 3:20
Either would work, as long as I could read row cells in the function. Assuming A1 shows 'Harun' and B1 shows '24HR', I'd like to put =@MyFunction(ThisRow) in C1 to produce 'Harun24HR' in C1 where function is called. Again. this is just a shell. Actual logic will be very complex, so I need a custom function.
– jacekn
Nov 24 '18 at 3:20
add a comment |
3 Answers
3
active
oldest
votes
This is a very simple task that can easily be done using just a worksheet formula:
=CONCATENATE(A1," ",B1)
However, if you insist on using a UDF function, this should do
Public Function MyFunction() As String
With Application.Caller
MyFunction = .Parent.Cells(.Row, Range("FIRST_NAME").Column) & _
" " & .Parent.Cells(.Row, Range("LAST_NAME").Column)
End With
End Function
In the above, you won't even need to pass an argument. It uses the Application.Caller.Row method to grab the row from wherever the function is located in.
You would just use the function =MyFunction() and VBA will automatically determine the row the function is located in.
answered Nov 24 '18 at 4:08
K.DᴀᴠɪsK.Dᴀᴠɪs
7,229112439
Thanks, this Application.Caller worked out great. Again, my example was simple just to get the function going. I have placed much more code there. I had to create private functions and delegate some work to them, to keep things readable.
– jacekn
Dec 4 '18 at 2:18
add a comment |
I am not clear about your goal but this may help you.
Public Function MyFunction(ByVal argRow As Long)
Dim fName As String
Dim lName As String
fName = Range("A" & argRow)
lName = Range("B" & argRow)
MyFunction = fName & " " & lName
End Function
answered Nov 24 '18 at 3:30
Harun24HRHarun24HR
3,9502718
add a comment |
Public Function MyFunction(ByVal rowIndex As Long)
Dim firstNameCol As Long
Dim lastNameCol As Long
firstNameCol = Rows(1).Find(What:="FIRST_NAME", LookIn:=xlValues, LookAt:=xlWhole).Column
lastNameCol = Rows(1).Find(What:="LAST_NAME", LookIn:=xlValues, LookAt:=xlWhole).Column
MyFunction = Cells(rowIndex, firstNameCol).Value & " " & Cells(rowIndex, lastNameCol).Value
End Function
answered Nov 24 '18 at 6:06
DisplayNameDisplayName
10.8k2619
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',
autoActivateHeartbeat: false,
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%2f53454847%2fhow-to-pass-a-row-to-an-excel-visual-basic-for-applications-custom-function%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
This is a very simple task that can easily be done using just a worksheet formula:
=CONCATENATE(A1," ",B1)
However, if you insist on using a UDF function, this should do
Public Function MyFunction() As String
With Application.Caller
MyFunction = .Parent.Cells(.Row, Range("FIRST_NAME").Column) & _
" " & .Parent.Cells(.Row, Range("LAST_NAME").Column)
End With
End Function
In the above, you won't even need to pass an argument. It uses the Application.Caller.Row method to grab the row from wherever the function is located in.
You would just use the function =MyFunction() and VBA will automatically determine the row the function is located in.
answered Nov 24 '18 at 4:08
K.DᴀᴠɪsK.Dᴀᴠɪs
7,229112439
Thanks, this Application.Caller worked out great. Again, my example was simple just to get the function going. I have placed much more code there. I had to create private functions and delegate some work to them, to keep things readable.
– jacekn
Dec 4 '18 at 2:18
add a comment |
This is a very simple task that can easily be done using just a worksheet formula:
=CONCATENATE(A1," ",B1)
However, if you insist on using a UDF function, this should do
Public Function MyFunction() As String
With Application.Caller
MyFunction = .Parent.Cells(.Row, Range("FIRST_NAME").Column) & _
" " & .Parent.Cells(.Row, Range("LAST_NAME").Column)
End With
End Function
In the above, you won't even need to pass an argument. It uses the Application.Caller.Row method to grab the row from wherever the function is located in.
You would just use the function =MyFunction() and VBA will automatically determine the row the function is located in.
answered Nov 24 '18 at 4:08
K.DᴀᴠɪsK.Dᴀᴠɪs
7,229112439
Thanks, this Application.Caller worked out great. Again, my example was simple just to get the function going. I have placed much more code there. I had to create private functions and delegate some work to them, to keep things readable.
– jacekn
Dec 4 '18 at 2:18
add a comment |
This is a very simple task that can easily be done using just a worksheet formula:
=CONCATENATE(A1," ",B1)
However, if you insist on using a UDF function, this should do
Public Function MyFunction() As String
With Application.Caller
MyFunction = .Parent.Cells(.Row, Range("FIRST_NAME").Column) & _
" " & .Parent.Cells(.Row, Range("LAST_NAME").Column)
End With
End Function
In the above, you won't even need to pass an argument. It uses the Application.Caller.Row method to grab the row from wherever the function is located in.
You would just use the function =MyFunction() and VBA will automatically determine the row the function is located in.
answered Nov 24 '18 at 4:08
K.DᴀᴠɪsK.Dᴀᴠɪs
7,229112439
This is a very simple task that can easily be done using just a worksheet formula:
=CONCATENATE(A1," ",B1)
However, if you insist on using a UDF function, this should do
Public Function MyFunction() As String
With Application.Caller
MyFunction = .Parent.Cells(.Row, Range("FIRST_NAME").Column) & _
" " & .Parent.Cells(.Row, Range("LAST_NAME").Column)
End With
End Function
In the above, you won't even need to pass an argument. It uses the Application.Caller.Row method to grab the row from wherever the function is located in.
You would just use the function =MyFunction() and VBA will automatically determine the row the function is located in.
answered Nov 24 '18 at 4:08
K.DᴀᴠɪsK.Dᴀᴠɪs
7,229112439
answered Nov 24 '18 at 4:08
K.DᴀᴠɪsK.Dᴀᴠɪs
7,229112439
answered Nov 24 '18 at 4:08
K.DᴀᴠɪsK.Dᴀᴠɪs
7,229112439
answered Nov 24 '18 at 4:08
K.DᴀᴠɪsK.Dᴀᴠɪs
7,229112439
7,229112439
Thanks, this Application.Caller worked out great. Again, my example was simple just to get the function going. I have placed much more code there. I had to create private functions and delegate some work to them, to keep things readable.
– jacekn
Dec 4 '18 at 2:18
add a comment |
Thanks, this Application.Caller worked out great. Again, my example was simple just to get the function going. I have placed much more code there. I had to create private functions and delegate some work to them, to keep things readable.
– jacekn
Dec 4 '18 at 2:18
Thanks, this Application.Caller worked out great. Again, my example was simple just to get the function going. I have placed much more code there. I had to create private functions and delegate some work to them, to keep things readable.
– jacekn
Dec 4 '18 at 2:18
Thanks, this Application.Caller worked out great. Again, my example was simple just to get the function going. I have placed much more code there. I had to create private functions and delegate some work to them, to keep things readable.
– jacekn
Dec 4 '18 at 2:18
add a comment |
I am not clear about your goal but this may help you.
Public Function MyFunction(ByVal argRow As Long)
Dim fName As String
Dim lName As String
fName = Range("A" & argRow)
lName = Range("B" & argRow)
MyFunction = fName & " " & lName
End Function
answered Nov 24 '18 at 3:30
Harun24HRHarun24HR
3,9502718
add a comment |
I am not clear about your goal but this may help you.
Public Function MyFunction(ByVal argRow As Long)
Dim fName As String
Dim lName As String
fName = Range("A" & argRow)
lName = Range("B" & argRow)
MyFunction = fName & " " & lName
End Function
answered Nov 24 '18 at 3:30
Harun24HRHarun24HR
3,9502718
add a comment |
I am not clear about your goal but this may help you.
Public Function MyFunction(ByVal argRow As Long)
Dim fName As String
Dim lName As String
fName = Range("A" & argRow)
lName = Range("B" & argRow)
MyFunction = fName & " " & lName
End Function
answered Nov 24 '18 at 3:30
Harun24HRHarun24HR
3,9502718
I am not clear about your goal but this may help you.
Public Function MyFunction(ByVal argRow As Long)
Dim fName As String
Dim lName As String
fName = Range("A" & argRow)
lName = Range("B" & argRow)
MyFunction = fName & " " & lName
End Function
answered Nov 24 '18 at 3:30
Harun24HRHarun24HR
3,9502718
answered Nov 24 '18 at 3:30
Harun24HRHarun24HR
3,9502718
answered Nov 24 '18 at 3:30
Harun24HRHarun24HR
3,9502718
answered Nov 24 '18 at 3:30
Harun24HRHarun24HR
3,9502718
3,9502718
add a comment |
add a comment |
Public Function MyFunction(ByVal rowIndex As Long)
Dim firstNameCol As Long
Dim lastNameCol As Long
firstNameCol = Rows(1).Find(What:="FIRST_NAME", LookIn:=xlValues, LookAt:=xlWhole).Column
lastNameCol = Rows(1).Find(What:="LAST_NAME", LookIn:=xlValues, LookAt:=xlWhole).Column
MyFunction = Cells(rowIndex, firstNameCol).Value & " " & Cells(rowIndex, lastNameCol).Value
End Function
answered Nov 24 '18 at 6:06
DisplayNameDisplayName
10.8k2619
add a comment |
Public Function MyFunction(ByVal rowIndex As Long)
Dim firstNameCol As Long
Dim lastNameCol As Long
firstNameCol = Rows(1).Find(What:="FIRST_NAME", LookIn:=xlValues, LookAt:=xlWhole).Column
lastNameCol = Rows(1).Find(What:="LAST_NAME", LookIn:=xlValues, LookAt:=xlWhole).Column
MyFunction = Cells(rowIndex, firstNameCol).Value & " " & Cells(rowIndex, lastNameCol).Value
End Function
answered Nov 24 '18 at 6:06
DisplayNameDisplayName
10.8k2619
add a comment |
Public Function MyFunction(ByVal rowIndex As Long)
Dim firstNameCol As Long
Dim lastNameCol As Long
firstNameCol = Rows(1).Find(What:="FIRST_NAME", LookIn:=xlValues, LookAt:=xlWhole).Column
lastNameCol = Rows(1).Find(What:="LAST_NAME", LookIn:=xlValues, LookAt:=xlWhole).Column
MyFunction = Cells(rowIndex, firstNameCol).Value & " " & Cells(rowIndex, lastNameCol).Value
End Function
answered Nov 24 '18 at 6:06
DisplayNameDisplayName
10.8k2619
Public Function MyFunction(ByVal rowIndex As Long)
Dim firstNameCol As Long
Dim lastNameCol As Long
firstNameCol = Rows(1).Find(What:="FIRST_NAME", LookIn:=xlValues, LookAt:=xlWhole).Column
lastNameCol = Rows(1).Find(What:="LAST_NAME", LookIn:=xlValues, LookAt:=xlWhole).Column
MyFunction = Cells(rowIndex, firstNameCol).Value & " " & Cells(rowIndex, lastNameCol).Value
End Function
answered Nov 24 '18 at 6:06
DisplayNameDisplayName
10.8k2619
answered Nov 24 '18 at 6:06
DisplayNameDisplayName
10.8k2619
answered Nov 24 '18 at 6:06
DisplayNameDisplayName
10.8k2619
answered Nov 24 '18 at 6:06
DisplayNameDisplayName
10.8k2619
10.8k2619
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.
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%2f53454847%2fhow-to-pass-a-row-to-an-excel-visual-basic-for-applications-custom-function%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
MyFunction(ByVal argRow as Range)– Harun24HR
Nov 24 '18 at 3:13
@Harun24HR This is what my searches produced. Are you saying that Range is the Row? I understand a Range as a rectangle, not a line.
– jacekn
Nov 24 '18 at 3:14
What do you mean by
Row? Row means row number or reference cell?– Harun24HR
Nov 24 '18 at 3:17
Can you put a screenshot of your sample data?
– Harun24HR
Nov 24 '18 at 3:17
Either would work, as long as I could read row cells in the function. Assuming A1 shows 'Harun' and B1 shows '24HR', I'd like to put =@MyFunction(ThisRow) in C1 to produce 'Harun24HR' in C1 where function is called. Again. this is just a shell. Actual logic will be very complex, so I need a custom function.
– jacekn
Nov 24 '18 at 3:20